I am trying to create a query to find missing sequential numbers in a text field. I am using this specific field as a case number which is designated as two letters, the # sign, two digits indicating the year, a dash, and then a four digit number; For Example: AB#13-1234.
The reason for this query is to tell the user of this database that a specific case number has yet to be entered and needs to be. The case numbers are unique and will never be referenced more than once.
My table name is "MainDataTbl" and the field i'm trying to find the missing case numbers is titled "CaseNumber".
Assuming that last 4 digits are unique in your Casenumber, you need to create a table which contains all sequential numbers. There is probably the way to do it with VBA, but quick way would be to do it in Excel and than import.
If you call new table 'tSeqNums' and the column 'SeqNumList' this is (untested) sql which should work:
Assuming that last 4 digits are unique in your Casenumber, you need to create a table which contains all sequential numbers. There is probably the way to do it with VBA, but quick way would be to do it in Excel and than import.
If you call new table 'tSeqNums' and the column 'SeqNumList' this is (untested) sql which should work:
...
select SeqNumList from tSeqNums
left join
select right(CaseNumber,4) as CaseNum from MainDataTbl
I forgot to mention something. Our facility has three different sites therefore the last four digits could potentially be used three separate times. For example: AB#13-1234, CD#13-1234, EF#13-1234, the two letters at the beginning of each case numbers are indcators of the site the case number was assigned for. Its all being tracked in one table.
I forgot to mention something. Our facility has three different sites therefore the last four digits could potentially be used three separate times. For example: AB#13-1234, CD#13-1234, EF#13-1234, the two letters at the beginning of each case numbers are indcators of the site the case number was assigned for. Its all being tracked in one table.
Is this correct? If it is, then the following should do the trick:
Code:
SELECT "AB#13-" & Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'AB*') as AB
ON Right("000" & tSeqNums.SeqNumList,4) = right( AB.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null
UNION ALL
SELECT "CD#13-" & Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'CD*') as CD
ON Right("000" & tSeqNums.SeqNumList,4) = right( CD.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null
UNION ALL
SELECT "EF#13-" & Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'EF*') as EF
ON Right("000" & tSeqNums.SeqNumList,4) = right( EF.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null;
This code assumes that the the Excel generated sequence list may be numeric (i.e. 1,2,3 and not 0001, 0002, 0003) , so it will work in either case.
Here is the small modification which will remove numbers higher than the last CaseNumbers
Code:
SELECT "AB#13-"&Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'AB*') as AB
ON Right("000" & tSeqNums.SeqNumList,4) = right( AB.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null and SeqNumList <= (SELECT Max(CLng(Right([CaseNumber],4))) FROM MainDataTbl)
UNION ALL
SELECT "CD#13-"&Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'CD*') as CD
ON Right("000" & tSeqNums.SeqNumList,4) = right( CD.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null and SeqNumList <= (SELECT Max(CLng(Right([CaseNumber],4))) FROM MainDataTbl)
UNION ALL
SELECT "EF#13-"&Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'EF*') as EF
ON Right("000" & tSeqNums.SeqNumList,4) = right( EF.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null and SeqNumList <= (SELECT Max(CLng(Right([CaseNumber],4))) FROM MainDataTbl);
Here is the small modification which will remove numbers higher than the last CaseNumbers
Code:
SELECT "AB#13-"&Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'AB*') as AB
ON Right("000" & tSeqNums.SeqNumList,4) = right( AB.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null and SeqNumList <= (SELECT Max(CLng(Right([CaseNumber],4))) FROM MainDataTbl)
UNION ALL
SELECT "CD#13-"&Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'CD*') as CD
ON Right("000" & tSeqNums.SeqNumList,4) = right( CD.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null and SeqNumList <= (SELECT Max(CLng(Right([CaseNumber],4))) FROM MainDataTbl)
UNION ALL
SELECT "EF#13-"&Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'EF*') as EF
ON Right("000" & tSeqNums.SeqNumList,4) = right( EF.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null and SeqNumList <= (SELECT Max(CLng(Right([CaseNumber],4))) FROM MainDataTbl);
This is the one i'd like to use so i'm not getting a long list of cases that havent been issued yet. I'm having issues understanding as to how I implement this code into my dataase. Is this the sql for a query or is it three separate steps that I need to take to set it up?
Thanks in advance for your help, its much appreciated.
Step1: Create table with sequential numbers. Either do it with Excel and import to Access, or run the following code:
Create module, paste the code and press F5
Code:
Sub CreateSeqTable()
DoCmd.SetWarnings False
DoCmd.RunSQL ("SELECT 1 AS SeqNumList INTO tSeqNums")
Dim SQLstr As String
Dim Counter As Integer
Counter = 2
While Counter <= 1000 '<---Change as needed for maximum numbers
SQLstr = "INSERT INTO tSeqNums ( SeqNumList ) VALUES (" & Counter & ");"
DoCmd.RunSQL (SQLstr)
Counter = Counter + 1
Wend
DoCmd.SetWarnings True
End Sub
Verify that the 'tSeqNums' was created
Step2: Run the query
Create new query, and change to SQL view. Paste this SQL code:
Code:
SELECT "AB#13-"&Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'AB*') as AB
ON Right("000" & tSeqNums.SeqNumList,4) = right( AB.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null and SeqNumList <= (SELECT Max(CLng(Right([CaseNumber],4))) FROM MainDataTbl)
UNION ALL
SELECT "CD#13-"&Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'CD*') as CD
ON Right("000" & tSeqNums.SeqNumList,4) = right( CD.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null and SeqNumList <= (SELECT Max(CLng(Right([CaseNumber],4))) FROM MainDataTbl)
UNION ALL
SELECT "EF#13-"&Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'EF*') as EF
ON Right("000" & tSeqNums.SeqNumList,4) = right( EF.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null and SeqNumList <= (SELECT Max(CLng(Right([CaseNumber],4))) FROM MainDataTbl);
This is one complete code, which will do everything at once (no steps involved). You probably will need to adapt it depending on the actual (real) shape of your Casenumber (I am assuming AB, CD and EF were "fake", no?). If you provide with actual format, I can help you.
Step1: Create table with sequential numbers.
Either do it with Excel and import to Access, or run the following code:
Create module, paste the code and press F5
Code:
Sub CreateSeqTable()
DoCmd.SetWarnings False
DoCmd.RunSQL ("SELECT 1 AS SeqNumList INTO tSeqNums")
Dim SQLstr As String
Dim Counter As Integer
Counter = 2
While Counter <= 1000 '<---Change as needed for maximum numbers
SQLstr = "INSERT INTO tSeqNums ( SeqNumList ) VALUES (" & Counter & ");"
DoCmd.RunSQL (SQLstr)
Counter = Counter + 1
Wend
DoCmd.SetWarnings True
End Sub
Verify that the 'tSeqNums' was created
Step2: Run the query
Create new query, and change to SQL view. Paste this SQL code:
Code:
SELECT "AB#13-"&Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'AB*') as AB
ON Right("000" & tSeqNums.SeqNumList,4) = right( AB.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null and SeqNumList <= (SELECT Max(CLng(Right([CaseNumber],4))) FROM MainDataTbl)
UNION ALL
SELECT "CD#13-"&Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'CD*') as CD
ON Right("000" & tSeqNums.SeqNumList,4) = right( CD.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null and SeqNumList <= (SELECT Max(CLng(Right([CaseNumber],4))) FROM MainDataTbl)
UNION ALL
SELECT "EF#13-"&Right("000" & [SeqNumList],4) AS MissingNums
FROM tSeqNums LEFT JOIN (SELECT CaseNumber from MainDataTbl where CaseNumber like 'EF*') as EF
ON Right("000" & tSeqNums.SeqNumList,4) = right( EF.CaseNumber,4)
WHERE MainDataTbl.CaseNumber is null and SeqNumList <= (SELECT Max(CLng(Right([CaseNumber],4))) FROM MainDataTbl);
This is one complete code, which will do everything at once (no steps involved). You probably will need to adapt it depending on the actual (real) shape of your Casenumber (I am assuming AB, CD and EF were "fake", no?). If you provide with actual format, I can help you.
In reference to format, everything is correct with an exception of the letters; those true letters will be BS, LS, and BP.
I created an excel sheet and built a list for each designator starting with ??#13-0001 up to ??#13-1500 and imported it to a table.
I then created a unmatched query to find what is in the table vs. the other using this SQL:
Code:
SELECT seqnums.*
FROM seqnums LEFT JOIN MainDataTbl ON seqnums.[Field1] = MainDataTbl.[CaseNumber]
WHERE (((MainDataTbl.CaseNumber) Is Null));
I then ran the query; however it came up with the entire list even showing the case number that have yet to be entered. can you help me with applying that filter on my query sql?
That is correct, I ran each letter designator up to 1500 so I didnt have to keep creating and replacing the table over time as the numbers got higher, hoping to do an automated thing on this; however if i have to create a new table every time I want to check for missing case numbers, I can do that as well.
Ultimately the table with the intended case numbers is in one fiend, A1 and runs for each "site" designater BP#13-0001 all the way up to BP#13-1500 and then onto the other two designators.
Now thinking about this further, I guess the query would not know where to stop as it wouldnt know if a specific number was the last number issued vs. a missing number in the opposite table. ?????????
Hopefully this makes since.
Thanks again for the help, i'm sure i'm on track to what you are explaining; however I am still in learning phase with certain things in access and I much appreciate the help/confirmation for this process.
