If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Identify missing numbers
I have a table that uses a text field to store receipt numbers. Currently I
use VAL to sort the numbers numerically. Is there a way to identify which numbers are missing out of the sequence? For instance if my receipts start at 1 and go through 10,000, but receipts 900 through 987 are missing, can I get a query to show me that? Thanks! |
#2
|
|||
|
|||
Identify missing numbers
Tara
By "missing" I asssume you mean "should be in a list but is not"... One way to do this is to have a list of what numbers SHOULD be listed, then use the query wizard to construct an "unmatched" query between your list and the SHOULD BE list to see which of the SHOULD BE values are not in your list. Or you could create a procedure that steps through the sequence numbers one-by-one, looking for situations in which the next (expected) value is not present, saving all those up, then displaying those after running through your list. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Tara" wrote in message ... I have a table that uses a text field to store receipt numbers. Currently I use VAL to sort the numbers numerically. Is there a way to identify which numbers are missing out of the sequence? For instance if my receipts start at 1 and go through 10,000, but receipts 900 through 987 are missing, can I get a query to show me that? Thanks! |
#3
|
|||
|
|||
Identify missing numbers
Try this --
WO8SEP09 is the table and Work is the number field. The first query finds the first missing number in a sequence and the second find the end of that sequence. first missing number of each sequence -- SELECT [Work]+1 AS Missing FROM WO8SEP09 AS T WHERE (((Exists (SELECT * FROM WO8SEP09 T1 WHERE T1.[Work] = T.[Work] + 1))=False)); SELECT [first missing number of each sequence].Missing AS Missing_Start, (SELECT TOP 1 [XX].Work FROM WO8SEP09 AS [XX] WHERE [XX].Work [first missing number of each sequence].Missing ORDER BY [XX].Work)-1 AS Missing_End FROM [first missing number of each sequence]; -- Build a little, test a little. "Tara" wrote: I have a table that uses a text field to store receipt numbers. Currently I use VAL to sort the numbers numerically. Is there a way to identify which numbers are missing out of the sequence? For instance if my receipts start at 1 and go through 10,000, but receipts 900 through 987 are missing, can I get a query to show me that? Thanks! |
#4
|
|||
|
|||
Identify missing numbers
Construct a table with one number field and 10 records with the numbers 0 to 9.
Construct a query using that table multiple times to generate numbers from zero to 10,000 SELECT CStr(T1.NumField + T10.NumField*10 + T100.NumField * 100 + T1000.Numfield*1000) as NumString FROM NumTable as T1, NumTable As t10, NumTable as T100, NumTable as T1000 Now you can use that and your store reciept table to do an unmatched query. SELECT NumString as MissingValues FROM QueryNumString LEFT JOIN [ReceiptsTable] as R ON QueryNumString.NumString = R.[ReceiptNumber] WHERE R.[ReceiptNumber] is Null John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Tara wrote: I have a table that uses a text field to store receipt numbers. Currently I use VAL to sort the numbers numerically. Is there a way to identify which numbers are missing out of the sequence? For instance if my receipts start at 1 and go through 10,000, but receipts 900 through 987 are missing, can I get a query to show me that? Thanks! |
#5
|
|||
|
|||
Identify missing numbers
Thank you both for the suggestions!
"Tara" wrote: I have a table that uses a text field to store receipt numbers. Currently I use VAL to sort the numbers numerically. Is there a way to identify which numbers are missing out of the sequence? For instance if my receipts start at 1 and go through 10,000, but receipts 900 through 987 are missing, can I get a query to show me that? Thanks! |
Thread Tools | |
Display Modes | |
|
|