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
-
-
Function GetMissingNumbers()
-
On Error Resume Next
-
Dim strSQL As String
-
Dim strCustomerCodes As String
-
Dim MyInsert As String
-
Dim ThisDB As DAO.Database, rstCustomer As DAO.Recordset
-
strSQL = "SELECT DISTINCT the_table.[cst code] FROM the_table;"
-
Set ThisDB = CurrentDb()
-
'comment out he following line if you do not want tblsequence to be empted of existing data
-
DoCmd.RunSQL "DELETE * FROM tblSequence"
-
Set rstCustomer = ThisDB.OpenRecordset(strSQL, dbOpenSnapshot)
-
rstCustomer.MoveFirst
-
Do While Not rstCustomer.EOF
-
'build a sub select statement based on the current cst code in the rowset
-
strCustomerCodes = "SELECT the_table.[Customer name], the_table.[cst code], the_table.sequence "
-
strCustomerCodes = strCustomerCodes & " FROM the_table "
-
strCustomerCodes = strCustomerCodes & " WHERE (((the_table.[cst code])='" & rstCustomer![cst code] & "'))"
-
'build an SQL Select as a wrapper for the sub select
-
strNumbers = "SELECT DISTINCT '" & rstCustomer![cst code] & "' AS [cst code], tblNumbers.Numbers AS [Missing Numbers] FROM tblNumbers "
-
strNumbers = strNumbers & "WHERE (((tblNumbers.Numbers) Not In (SELECT sequence FROM (" & strCustomerCodes & "))"
-
strNumbers = strNumbers & "And (tblNumbers.Numbers)<(SELECT MAX(sequence) FROM (" & strCustomerCodes & ")))) "
-
strNumbers = strNumbers & "ORDER BY tblNumbers.Numbers;"
-
'build an insert SQL based on the SQL Select and insert the values into tblsequence
-
MyInsert = "INSERT INTO tblSequence ( [cst code], [Missing Numbers] )"
-
MyInsert = MyInsert & strNumbers
-
'fire off the insert action sql
-
DoCmd.RunSQL MyInsert
-
'Debug.Print (MyInsert)
-
'move to the next customer in the loop
-
rstCustomer.MoveNext
-
Loop
-
'release object references and memory
-
rstCustomer.Close
-
strSQL = ""
-
strCustomerCodes = ""
-
strNumbers = ""
-
Set rstCustomer = Nothing
-
Set rstCustomer = Nothing
-
mycnt = DCount("*", "tblSequence")
-
DoCmd.Beep
-
MsgBox "Process complete tblSequence contains " & mycnt & " records!", vbNewLine, vbInformation, "System Message"
-
End Function
-
To execute this function you can either create a command button and type into its event procedure (properties dialog box) the following line
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 :)
6
