Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010

    Unanswered: Modules to find missing records in db

    I hope there is someone who can help me with a little problem I have been trying to solve for about a two weeks now.

    Basically I have a list of customer ID's which are all the same format for example 1 letter followed by 6 numbers.

    I get a long list of these numbers every day which are not in order.

    I set the query to put the customer id's into ascending order.

    Basically what I am trying to find are missing customer id’s. the criteria is that each customer id must be either the same as the previous customer id or a maximum of 1 number larger i.e. A000000 A000001 A000001 A000002 are all correct in ascending order but if A000009 was to follow I need to find a way to flag this and display what numbers are missing, as every number should be in sequence.

    I managed to get this working in a module in excel

    Sub one()
    Max = InputBox(“Enter size of Spreadsheet”)
    Base = Right (Sheet1.cells(2, 1), 6)
    For lp = 3 To Val (Max)
    Chk = Right(Sheet1.Cells(lp, 1), 6)
    If ((chk – base) > 1) Then
    S = “”
    For q = base + 1 To chk – 1
    S = s & Str(q) & vbCrLf
    Next q
    MsgBox (“Missing numbers” & vbCrLf & s)
    End If
    Base = chk
    MsgBox “Done”
    End Sub.

    As I am trying to get this working in Access 2003 I am unsure how to get the same response in a query in Access 2003.

    I need this to check every record in access to make sure there are none missing. The above code I set to request the number of rows to check before it ran. The query and field details are as follows:

    Query = TestQuery
    Field = Customer_ID

    You can see that the excel module also reads the 6 numbers from right to left and ignores the initial letter which is correct.

    It also once finding a missing number or missing numbers searches for the next set.

    Is there anyone that can help me with this as I am not that good with access and every attempt I have made to get this working in access has failed miserably?

  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    I would write a query to:
    Remove the Alpha character, leaving just the number
    Check this against a table of numbers (numbers\ tally tables are SO useful yet woefully underused)
    Return all numbers in the tally table not in your imported list of IDs

    This would provide what you require - correct?
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2010

    Thank you for the quick reply

    I have removed the initial character in previous attempts also I created a list of all customer id's is sequence, the problem this caused was that as customer A000102 could come through 3 time in a row each on a seperate record and not all customer id's come through each day they come through bit a time each day, so the records I am trying to compare is from the first record in the db to the last one received on each daily update.
    I could get ten records one day and 200 the next. so the table to compare the records too flags up loads of errors.
    As an Example the records in the db run from say A000503 and ever day new records are added and I was trying to find a way to highlight records that are missing from the sequence as each new recorded added should be either the same as the previous or 1 number higher than the previous.
    I dont know enough about access to get my excel module to work in access. I dont even know if it is possible.
    I hope im not too confusing, I started to loose myself then.
    Thank you again for your quick reply

  4. #4
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    Look at the "DISTINCT" keyword in Access/Jet sql. Then look in to the "numbers table" that pootle was mentioning.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jul 2010
    I think I didnt read what was written correctly I am going to have a play and see if I can get this to work.
    I dont really know that much about access im a cobol sort of person.

    thank you both for you help.

  6. #6
    Join Date
    Jul 2010
    After some playing I eneded up with this and it is working perfectly.

    Sub one()
    Dim rs As ADODB.Recordset
    DoCmd.OpenQuery "TestQuery"
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open "Select * From TestQuery", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    base = Mid(rs("Customer_ID"), 2)
    While Not rs.EOF
    chk = Mid(rs("Customer_ID"), 2)
    If ((chk - base) > 1) Then
    s = ""
    For q = base + 1 To chk - 1
    s = s & Right("000000" & Trim(Str(q)), 6) & vbCrLf
    Next q
    MsgBox ("Missing numbers" & vbCrLf & s)
    End If
    base = chk
    MsgBox "Done"
    End Sub

    Thank you all for your help.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts