Find missing numbers in list of receipts by batch

[kpfunf]

Hey all,
I have a table of receipts used (Table Name: receipts, Field Name: receiptNumber) and a table with receipts issued (Table Name: Receipts Batches, Field Names: BatchID, BegRng, EndRng). Example row in Receipts Batches: 3, 72551, 72600. I need to check the list of used receipts against the issued list and find any "skipped" receipts; that is, find the max used receipt for each batch, and check if any below that number are missing. I've seen some loops and select statements out there, but couldn't come close to getting them to work! Thanks in advance for the help; this forums has so much good info!

[Stewart Ross]

Hi. What an interesting problem! Here's a two-query solution. The first query finds all the receipt numbers that have been used in the batches, and the second uses this left-joined back to the receipts and selected for a null receipt ID to return all remaining receipt numbers not in the batch ranges:
qryAllUsedRecei pts

SELECT [Receipts Batches].BatchID, Receipts.ReceiptNumber, [Receipts Batches].BegRng, [Receipts Batches].EndRng
FROM Receipts, [Receipts Batches]
WHERE (((Receipts.ReceiptNumber) Between [BegRng] And [EndRng]));

qryReceiptNotUs ed

 
SELECT Receipts.ReceiptNumber
FROM Receipts LEFT JOIN qryAllUsedReceipts ON Receipts.ReceiptNumber = qryAllUsedReceipts.ReceiptNumber
WHERE (((qryAllUsedReceipts.ReceiptNumber) Is Null));

I've tested these on a simple small dataset, where receipts just has the numbers 1 to 30 in sequence, and receipt batches is as follows:

BatchID BegRng EndRng
1 1 10
2 12 16
3 17 29

Note that the numbers 11 and 30 are not in the batch ranges.

qryReceiptNotUs ed returns

ReceiptNumber
11
30

as expected. Try these out and see how it goes for you.

Regards

Stewart

[NeoPa]

Without going into too much detail, I'd work on the basis that Count(*) == (Max() - Min() + 1).
From this point you can select all matching records or use some code to list the missing numbers. Clearly SQL would struggle to list those per se.

[kpfunf]

Hi. What an interesting problem! Here's a two-query solution. The first query finds all the receipt numbers that have been used in the batches, and the second uses this left-joined back to the receipts and selected for a null receipt ID to return all remaining receipt numbers not in the batch ranges:
qryAllUsedRecei pts

SELECT [Receipts Batches].BatchID, Receipts.ReceiptNumber, [Receipts Batches].BegRng, [Receipts Batches].EndRng
FROM Receipts, [Receipts Batches]
WHERE (((Receipts.ReceiptNumber) Between [BegRng] And [EndRng]));

qryReceiptNotUs ed

 
SELECT Receipts.ReceiptNumber
FROM Receipts LEFT JOIN qryAllUsedReceipts ON Receipts.ReceiptNumber = qryAllUsedReceipts.ReceiptNumber
WHERE (((qryAllUsedReceipts.ReceiptNumber) Is Null));

I've tested these on a simple small dataset, where receipts just has the numbers 1 to 30 in sequence, and receipt batches is as follows:

BatchID BegRng EndRng
1 1 10
2 12 16
3 17 29

Note that the numbers 11 and 30 are not in the batch ranges.

qryReceiptNotUs ed returns

ReceiptNumber
11
30

as expected. Try these out and see how it goes for you.

Regards

Stewart

Thanks Stewart. This worked, but it is not what I exactly what I was looking for. BUT it did reveal an issue that I didn't even see! It shows me if Receipts Used were not recorded as being Issued in a batch. So this query will be great.

For the original issue, I'll use your sample for an example. Let's say we keep the batches you have. Now, for receipts used, I have 1,2,3,4,6,7,12, 13,14,15,17,18, 19,20,23,24,25. What I want is to find the max used in the batch (1st batch = 7, 2nd batch = 15, 3rd batch = 25), and from there, check back to the beginning to see if any were not use (in this case, 5 for the 1st, none for the 2nd, and 21,22 for the 3rd). So the end result from the query would be Receipts missing = 5, 21, 22.

[NeoPa]

As you'll see from my last post (#3), this cannot be done in SQL.
You will need to code this up in VBA.
A RecordSet loop is what you'll need. Start on that basis and let us know if/where you get stuck. Basic DAO recordset loop using two recordsets may help starting you going.

[kpfunf]

Thanks NeoPa. I feel fairly stupid to admit being stuck at the beginning. I think the example linked makes sense in what it is, but I don't see how to apply that to my situation. I apologize for the "newbieness "; nearly all my VBA has been in Excel, not Access.

[NeoPa]

That's not a big problem, but as what you require is in the "very involved" range of coding solutions, I'm not about to do it all for you.

What I will try to do is a general pseudo-code layout of what is probably going to be required. From there you will be expected to build onwards (with help where required and appropriate). This is certainly no trivial question, so be prepared for some graft.

[kpfunf]

That's great. I don't want you to write it, for sure, that wouldn't be fair. I usually pick things up quickly, so some solid direction hopefully will be enough. Thanks!

[NeoPa]

As a basic concept then, you would process through your dataset (The earlier link shows how to reference a dataset in VBA code).
You would have a variable that remembers the value from the previous record.
You would then have a loop of code that processes through for every record in the record set.
Compare the value in the current record with that of the previous (remebered) one.
If new = old + 1 then no extra action.
Otherwise you log the old remembered value as the last of a group.
Any other processing you want here.
Save the new "old" value in the variable.
Loop around until done.

[kpfunf]

I've been trying to figure this out for a while now. I don't understand how this will work though. I need to check each individual batch. How would I do that? And if the formula is just +1, what if the next one is 2 higher? I'm pretty stuck.

[NeoPa]

I've been trying to figure this out for a while now. I don't understand how this will work though. I need to check each individual batch. How would I do that?

That's what the previous link is supposed to explain. Have you been through that?

And if the formula is just +1, what if the next one is 2 higher? I'm pretty stuck.

Your post #4 explains that you want to find the top one for each grouping. There was no mention of groups that might be non-contiguous.

If we're to proceed, we need to communicate carefully and clearly. What I'm reading seems to be contradictory. I can help - but only if I know what you're working with and where (precisely) you are.

[kpfunf]

Specifically in # 4, you'll see the example:

For the original issue, I'll use your sample for an example. Let's say we keep the batches you have. Now, for receipts used, I have 1,2,3,4,6,7,12, 13,14,15,17,18, 19,20,23,24,25. What I want is to find the max used in the batch (1st batch = 7, 2nd batch = 15, 3rd batch = 25), and from there, check back to the beginning to see if any were not use (in this case, 5 for the 1st, none for the 2nd, and 21,22 for the 3rd). So the end result from the query would be Receipts missing = 5, 21, 22.

With the batches being:

Code: ( text )
BatchID BegRng EndRng
1 1 10
2 12 16
3 17 29

Should I use another example? I'm sorry for any confusion. I'm not understanding how to adapt the info from the link to the current situation.

In sum, the process is look at the batches, for each batch check the highest used receipt number, and from there check to see if any (from beg of batch to highest used in batch) were skipped (could be just one, could be multiple).

[NeoPa]

Let me have another look through this and come back to you. I think I got hold of the wrong end of the stick somewhere, but it's really quite hard to work with a problem where the requirements and the data to work with are not clearly visible.
I will be away this evening so there will also be a delay on my next post. This is just to let you know I haven't forgotten and will continue tomorrow.

PS. Feel free to *bump* the thread if I've not posted by this time tomorrow.

[kpfunf]

*Bump* .

[NeoPa]

Ooooops :(
I'll email myself to have a look at home this evening. I'm glad you bumped. It fell off my radar.