472,133 Members | 1,405 Online

# Missing Numbers in sequence

In Access, I have a table of customers, each cust has sequence of numbers that are supposed to be in sequential order and some are missing, i want list of all missing sequence number of each customer, is it possible with query.
Oct 22 '07 #1
8 7394
debasisdas
8,127 Expert 4TB
Question moved to Access Forum.
Oct 22 '07 #2
Jim Doherty
897 Expert 512MB
In Access, I have a table of customers, each cust has sequence of numbers that are supposed to be in sequential order and some are missing, i want list of all missing sequence number of each customer, is it possible with query.
Take a look at this thread the logic of which is 'similar' to that which you require and addressed by one of our resident experts

Regards

Jim :)
Oct 22 '07 #3
Take a look at this thread the logic of which is 'similar' to that which you require and addressed by one of our resident experts

Regards

Jim :)
But in my case there are few groups and every group has own sequence numbers , need to bring the customers name and each of the missing sequence belongs to that customer.
Oct 23 '07 #4
Jim Doherty
897 Expert 512MB
But in my case there are few groups and every group has own sequence numbers , need to bring the customers name and each of the missing sequence belongs to that customer.

I didnt perceive it as answering your post merely a pointer in the respect of its logic which is sound. :)

In your particular case given there are groups and names names within each group and each group has its own sequential referencing numerically whereby some numbers are missing.

In order to answer the posting it would be helpful to understand what you have there in terms of table structure ie field names etc otherwise any coding response will not reflect your tables and field names

Regards

Jim :)
Oct 23 '07 #5
I didnt perceive it as answering your post merely a pointer in the respect of its logic which is sound. :)

In your particular case given there are groups and names names within each group and each group has its own sequential referencing numerically whereby some numbers are missing.

In order to answer the posting it would be helpful to understand what you have there in terms of table structure ie field names etc otherwise any coding response will not reflect your tables and field names

Regards

Jim :)
the table has customer name, cst code, and each cst has selling sequence. This selling sequence column has some times number missing. for example cst A has sequence 1,2,3,4,,,,,, n and could be possible that 3 is missing, i want to know the new table or query which brings CstA and 3. same as for cst B, and also for other customers if the number are missing. Here more than one customer can have same sequence number.
Oct 23 '07 #6
Jim Doherty
897 Expert 512MB
the table has customer name, cst code, and each cst has selling sequence. This selling sequence column has some times number missing. for example cst A has sequence 1,2,3,4,,,,,, n and could be possible that 3 is missing, i want to know the new table or query which brings CstA and 3. same as for cst B, and also for other customers if the number are missing. Here more than one customer can have same sequence number.

The following is strictly in accordance with the information you provided

My Assumptions

You have a table called...........the_table
the table has the following fields
Customer name datatype TEXT
cust code datatype TEXT
sequence datatype NUMBER (format Long Integer)
You will create your own error handling routine I have simply put the on error resume next as the first line which means it will advance though the code till the end ignoring any potential errors it may or may not encounter.

What you need to do
1)
Create a table called tblSequence
the table must have the following field names
cst code datatype TEXT
Missing Numbers datatype NUMBER (format Long Integer)

2)
Create a table called tblNumbers
the table must have the following field names
Number datatype NUMBER (format( Long Integer)

3)
Populate the tblNumbers table with an arbitary set of numbers anything from 1 to however many you wish you can do this ie by using excel and 'drag down' facility where it increments the number for instance instance. Insert 1 to 10000 say whatever you wish. This table will be used to compare and determine the 'missing numbers' logic. I could have coded this but its just as simple to do it manually yourself.

4)
Paste the following function into a module and save it

Expand|Select|Wrap|Line Numbers
1.
2. Function GetMissingNumbers()
3. On Error Resume Next
4. Dim strSQL As String
5. Dim strCustomerCodes As String
6. Dim MyInsert As String
7. Dim ThisDB As DAO.Database, rstCustomer As DAO.Recordset
8. strSQL = "SELECT DISTINCT the_table.[cst code] FROM the_table;"
9. Set ThisDB = CurrentDb()
10. 'comment out he following line if you do not want tblsequence to be empted of existing data
11. DoCmd.RunSQL "DELETE * FROM tblSequence"
12. Set rstCustomer = ThisDB.OpenRecordset(strSQL, dbOpenSnapshot)
13. rstCustomer.MoveFirst
14. Do While Not rstCustomer.EOF
15.     'build a sub select statement based on the current cst code in the rowset
16.     strCustomerCodes = "SELECT the_table.[Customer name], the_table.[cst code], the_table.sequence "
17.     strCustomerCodes = strCustomerCodes & " FROM the_table "
18.     strCustomerCodes = strCustomerCodes & " WHERE (((the_table.[cst code])='" & rstCustomer![cst code] & "'))"
19.     'build an SQL Select as a wrapper for the sub select
20.     strNumbers = "SELECT DISTINCT '" & rstCustomer![cst code] & "' AS [cst code], tblNumbers.Numbers AS [Missing Numbers] FROM tblNumbers "
21.     strNumbers = strNumbers & "WHERE (((tblNumbers.Numbers) Not In (SELECT sequence FROM (" & strCustomerCodes & "))"
22.     strNumbers = strNumbers & "And (tblNumbers.Numbers)<(SELECT MAX(sequence) FROM (" & strCustomerCodes & ")))) "
23.     strNumbers = strNumbers & "ORDER BY tblNumbers.Numbers;"
24.     'build an insert SQL based on the SQL Select and insert the values into tblsequence
25.     MyInsert = "INSERT INTO tblSequence ( [cst code], [Missing Numbers] )"
26.     MyInsert = MyInsert & strNumbers
27.     'fire off the insert action sql
28.     DoCmd.RunSQL MyInsert
29.     'Debug.Print (MyInsert)
30. 'move to the next customer in the loop
31. rstCustomer.MoveNext
32. Loop
33. 'release object references and memory
34. rstCustomer.Close
35. strSQL = ""
36. strCustomerCodes = ""
37. strNumbers = ""
38. Set rstCustomer = Nothing
39. Set rstCustomer = Nothing
40. mycnt = DCount("*", "tblSequence")
41. DoCmd.Beep
42. MsgBox "Process complete tblSequence contains " & mycnt & " records!", vbNewLine, vbInformation, "System Message"
43. End Function
44.
To execute this function you can either create a command button and type into its event procedure (properties dialog box) the following line

Expand|Select|Wrap|Line Numbers
1.  =GetMissingNumbers()
or go into the immediate window of the vba module (view...immediate window) and simply type

GetMissingNumbers

The function firstly opens a recordset of distinct customer values based on the table of your data held in ...the_table
It then loops through that list and recreates an sql statement on the fly each time it loops which pertains to each individual customer. (the cust code is provided by the position of the row in the loop at a particular time in the loop. The dynamic SQL is then used to insert the necessary values you require into the table tblSequence, which by the time you have reached the end of the loop will have inserted all customers who have missing numbers.

How does it know what is missing? simply put it compares what numbers it does have against a table of numbers that it doesnt have. You have to ensure when using this method that the table of numbers has sufficient numbers in it on which to base the comparison

I hope this helps you! It always helps to provide specific field names and specific tables names and datatypes when posting because as you can see by that I provide for you here this may or may well not instantly match your table and field names and you have some editing to do. (so in the absence of specifics maybe not a simple cut and paste as you may have hoped)

As an aside it is wise to have the habit of no spaces in field or table names when having a naming convention. I have tried to compromise on the basis on your posted details my advice however to you is to look at Leszynski/Roddick naming convention (google it on the web)

Regards

Jim :)
Oct 23 '07 #7
The following is strictly in accordance with the information you provided

My Assumptions

You have a table called...........the_table
the table has the following fields
Customer name datatype TEXT
cust code datatype TEXT
sequence datatype NUMBER (format Long Integer)
You will create your own error handling routine I have simply put the on error resume next as the first line which means it will advance though the code till the end ignoring any potential errors it may or may not encounter.

What you need to do
1)
Create a table called tblSequence
the table must have the following field names
cst code datatype TEXT
Missing Numbers datatype NUMBER (format Long Integer)

2)
Create a table called tblNumbers
the table must have the following field names
Number datatype NUMBER (format( Long Integer)

3)
Populate the tblNumbers table with an arbitary set of numbers anything from 1 to however many you wish you can do this ie by using excel and 'drag down' facility where it increments the number for instance instance. Insert 1 to 10000 say whatever you wish. This table will be used to compare and determine the 'missing numbers' logic. I could have coded this but its just as simple to do it manually yourself.

4)
Paste the following function into a module and save it

Expand|Select|Wrap|Line Numbers
1.
2. Function GetMissingNumbers()
3. On Error Resume Next
4. Dim strSQL As String
5. Dim strCustomerCodes As String
6. Dim MyInsert As String
7. Dim ThisDB As DAO.Database, rstCustomer As DAO.Recordset
8. strSQL = "SELECT DISTINCT the_table.[cst code] FROM the_table;"
9. Set ThisDB = CurrentDb()
10. 'comment out he following line if you do not want tblsequence to be empted of existing data
11. DoCmd.RunSQL "DELETE * FROM tblSequence"
12. Set rstCustomer = ThisDB.OpenRecordset(strSQL, dbOpenSnapshot)
13. rstCustomer.MoveFirst
14. Do While Not rstCustomer.EOF
15.     'build a sub select statement based on the current cst code in the rowset
16.     strCustomerCodes = "SELECT the_table.[Customer name], the_table.[cst code], the_table.sequence "
17.     strCustomerCodes = strCustomerCodes & " FROM the_table "
18.     strCustomerCodes = strCustomerCodes & " WHERE (((the_table.[cst code])='" & rstCustomer![cst code] & "'))"
19.     'build an SQL Select as a wrapper for the sub select
20.     strNumbers = "SELECT DISTINCT '" & rstCustomer![cst code] & "' AS [cst code], tblNumbers.Numbers AS [Missing Numbers] FROM tblNumbers "
21.     strNumbers = strNumbers & "WHERE (((tblNumbers.Numbers) Not In (SELECT sequence FROM (" & strCustomerCodes & "))"
22.     strNumbers = strNumbers & "And (tblNumbers.Numbers)<(SELECT MAX(sequence) FROM (" & strCustomerCodes & ")))) "
23.     strNumbers = strNumbers & "ORDER BY tblNumbers.Numbers;"
24.     'build an insert SQL based on the SQL Select and insert the values into tblsequence
25.     MyInsert = "INSERT INTO tblSequence ( [cst code], [Missing Numbers] )"
26.     MyInsert = MyInsert & strNumbers
27.     'fire off the insert action sql
28.     DoCmd.RunSQL MyInsert
29.     'Debug.Print (MyInsert)
30. 'move to the next customer in the loop
31. rstCustomer.MoveNext
32. Loop
33. 'release object references and memory
34. rstCustomer.Close
35. strSQL = ""
36. strCustomerCodes = ""
37. strNumbers = ""
38. Set rstCustomer = Nothing
39. Set rstCustomer = Nothing
40. mycnt = DCount("*", "tblSequence")
41. DoCmd.Beep
42. MsgBox "Process complete tblSequence contains " & mycnt & " records!", vbNewLine, vbInformation, "System Message"
43. End Function
44.
To execute this function you can either create a command button and type into its event procedure (properties dialog box) the following line

Expand|Select|Wrap|Line Numbers
1.  =GetMissingNumbers()
or go into the immediate window of the vba module (view...immediate window) and simply type

GetMissingNumbers

The function firstly opens a recordset of distinct customer values based on the table of your data held in ...the_table
It then loops through that list and recreates an sql statement on the fly each time it loops which pertains to each individual customer. (the cust code is provided by the position of the row in the loop at a particular time in the loop. The dynamic SQL is then used to insert the necessary values you require into the table tblSequence, which by the time you have reached the end of the loop will have inserted all customers who have missing numbers.

How does it know what is missing? simply put it compares what numbers it does have against a table of numbers that it doesnt have. You have to ensure when using this method that the table of numbers has sufficient numbers in it on which to base the comparison

I hope this helps you! It always helps to provide specific field names and specific tables names and datatypes when posting because as you can see by that I provide for you here this may or may well not instantly match your table and field names and you have some editing to do. (so in the absence of specifics maybe not a simple cut and paste as you may have hoped)

As an aside it is wise to have the habit of no spaces in field or table names when having a naming convention. I have tried to compromise on the basis on your posted details my advice however to you is to look at Leszynski/Roddick naming convention (google it on the web)

Regards

Jim :)
Thanks- I did run after changing some columns names, this gives me error on the line
--DoCmd.RunSQL MyInsert-- Error# 3131

Oct 23 '07 #8
Jim Doherty
897 Expert 512MB
Thanks- I did run after changing some columns names, this gives me error on the line
--DoCmd.RunSQL MyInsert-- Error# 3131

1) Make sure the DAO library is available. In the VB editor window go to Tools - References and make sure that there is a 3.6 Microsoft DAO library ticked.

2) Using the method of displaying the built SQL using the txtSQL control on screen (or comment out the DoCmd.RunSql command and allow the Debug.Print line to reproduce the sql in the immediate window if you are running it from there then copy it to the clipboard) I mentioned if your error persists then post back the built SQL so I can look at it

3) I created a small db to write this code with tables to suit, tested it through a number of times it does work. It may be helpful 'if' the issue persists to email you this small database so that you can spot any differences yourself. If you PM (private message) me with your email address I will send it to you

Jim :)
Oct 24 '07 #9