By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,743 Members | 1,028 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,743 IT Pros & Developers. It's quick & easy.

Find missing numbers in list of receipts by batch

P: 78
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!
Feb 21 '08 #1
Share this Question
Share on Google+
20 Replies


Expert Mod 2.5K+
P: 2,545
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:
qryAllUsedReceipts
Expand|Select|Wrap|Line Numbers
  1. SELECT [Receipts Batches].BatchID, Receipts.ReceiptNumber, [Receipts Batches].BegRng, [Receipts Batches].EndRng
  2. FROM Receipts, [Receipts Batches]
  3. WHERE (((Receipts.ReceiptNumber) Between [BegRng] And [EndRng]));
  4.  
qryReceiptNotUsed
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Receipts.ReceiptNumber
  3. FROM Receipts LEFT JOIN qryAllUsedReceipts ON Receipts.ReceiptNumber = qryAllUsedReceipts.ReceiptNumber
  4. WHERE (((qryAllUsedReceipts.ReceiptNumber) Is Null));
  5.  
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:

Expand|Select|Wrap|Line Numbers
  1. BatchID BegRng EndRng
  2. 1 1 10
  3. 2 12 16
  4. 3 17 29
Note that the numbers 11 and 30 are not in the batch ranges.

qryReceiptNotUsed returns
Expand|Select|Wrap|Line Numbers
  1. ReceiptNumber
  2. 11
  3. 30
as expected. Try these out and see how it goes for you.

Regards

Stewart
Feb 21 '08 #2

NeoPa
Expert Mod 15k+
P: 31,416
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.
Feb 22 '08 #3

P: 78
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:
qryAllUsedReceipts
Expand|Select|Wrap|Line Numbers
  1. SELECT [Receipts Batches].BatchID, Receipts.ReceiptNumber, [Receipts Batches].BegRng, [Receipts Batches].EndRng
  2. FROM Receipts, [Receipts Batches]
  3. WHERE (((Receipts.ReceiptNumber) Between [BegRng] And [EndRng]));
  4.  
qryReceiptNotUsed
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Receipts.ReceiptNumber
  3. FROM Receipts LEFT JOIN qryAllUsedReceipts ON Receipts.ReceiptNumber = qryAllUsedReceipts.ReceiptNumber
  4. WHERE (((qryAllUsedReceipts.ReceiptNumber) Is Null));
  5.  
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:

Expand|Select|Wrap|Line Numbers
  1. BatchID BegRng EndRng
  2. 1 1 10
  3. 2 12 16
  4. 3 17 29
Note that the numbers 11 and 30 are not in the batch ranges.

qryReceiptNotUsed returns
Expand|Select|Wrap|Line Numbers
  1. ReceiptNumber
  2. 11
  3. 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.
Feb 25 '08 #4

NeoPa
Expert Mod 15k+
P: 31,416
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.
Feb 25 '08 #5

P: 78
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.
Feb 26 '08 #6

NeoPa
Expert Mod 15k+
P: 31,416
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.
Feb 26 '08 #7

P: 78
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!
Feb 26 '08 #8

NeoPa
Expert Mod 15k+
P: 31,416
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.
Feb 26 '08 #9

P: 78
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.
Feb 27 '08 #10

NeoPa
Expert Mod 15k+
P: 31,416
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.
Feb 27 '08 #11

P: 78
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).
Feb 27 '08 #12

NeoPa
Expert Mod 15k+
P: 31,416
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.
Feb 27 '08 #13

P: 78
*Bump* .
Feb 29 '08 #14

NeoPa
Expert Mod 15k+
P: 31,416
Ooooops :(
I'll email myself to have a look at home this evening. I'm glad you bumped. It fell off my radar.
Feb 29 '08 #15

NeoPa
Expert Mod 15k+
P: 31,416
Sorry, had a very busy weekend and wasn't able to do much on the computer.
I will try to look at it tomorrow for you.
Mar 2 '08 #16

NeoPa
Expert Mod 15k+
P: 31,416
I've been very busy at work recently so I haven't had a chance to look at this properly until now. Apologies for that.

What I propose to do is show you the SQL for a query that returns, for each [BatchID], the [BegRng] & [EndRng] as well as the [MinUsed] & [MaxUsed]. These last two coming from the [Receipts] table.
At that point we can stop and consider where we want to go from there in more detail. I've reread the original post a couple of times now and I'm still a little fuzzy on what you need from there (exactly).

Anyway, on the the SQL that will probably form the basis of whatever does proceed from here, and the record layouts to make the whole question easier to follow :
Expand|Select|Wrap|Line Numbers
  1. [Receipts]
  2. ReceiptNumber, Numeric, PK
Expand|Select|Wrap|Line Numbers
  1. [Receipts Batches]
  2. BatchID, Numeric, PK
  3. BegRng, Numeric
  4. EndRng, Numeric
Expand|Select|Wrap|Line Numbers
  1. SELECT tRB.BatchID,
  2.        tRB.BegRng,
  3.        tRB.EndRng,
  4.        Min(tR.ReceiptNumber) AS [MinUsed],
  5.        Max(tR.ReceiptNumber) AS [MaxUsed]
  6. FROM [Receipts Batches] AS tRB INNER JOIN [Receipts] AS tR
  7.   ON tR.ReceiptNumber >= tRB.BegRng
  8.  AND tR.ReceiptNumber <= tRB.EndRng
  9. GROUP BY tRB.BatchID
Mar 4 '08 #17

P: 78
I'm assuming the first two parts of code are just the table setups, correct?

After copy/pasting the 3rd section and trying to run, the following comes up:

Expand|Select|Wrap|Line Numbers
  1. You tried to execute a query that does not include the specified expression 'BegRng' as part of an aggregate function. (Error 3122)
In trying to think of a simpler explanation of the scenario, I thought this: you're giving out bunchs of receipts. You want people to use them in order. You need to check to see if (in each group of receipts) any were skipped, but of course you don't count the ones that haven't been used yet (which you figure by looking at the last one used). I hope that makes sense.
Mar 4 '08 #18

NeoPa
Expert Mod 15k+
P: 31,416
It's not a good idea to change direction half way through. You may want to start again from scratch...

The SQL should have read :
Expand|Select|Wrap|Line Numbers
  1. SELECT tRB.BatchID,
  2.        tRB.BegRng,
  3.        tRB.EndRng,
  4.        Min(tR.ReceiptNumber) AS [MinUsed],
  5.        Max(tR.ReceiptNumber) AS [MaxUsed]
  6. FROM [Receipts Batches] AS tRB INNER JOIN [Receipts] AS tR
  7.   ON tR.ReceiptNumber >= tRB.BegRng
  8.  AND tR.ReceiptNumber <= tRB.EndRng
  9. GROUP BY tRB.BatchID,
  10.          tRB.BegRng,
  11.          tRB.EndRng
Mar 4 '08 #19

P: 78
Just tested quickly, looks wonderful! I don't have time to work on it today (will tomorrow), but I think this is a huge step. From here I should be able to check where null with the range being min and max. Thanks so much for the help. I'll tinker around, and if in trouble will post. Thanks again!
Mar 4 '08 #20

NeoPa
Expert Mod 15k+
P: 31,416
Pleased that's helped :)
You let us know if there's anything more you need help with. Remember : MinUsed & MaxUsed do not necessarily represent a continuous list. There may be gaps in there, but at least they describe the 'outer' range.
Mar 4 '08 #21

Post your reply

Sign in to post your reply or Sign up for a free account.