Check sequence gap in table 3

[micang]

Access 2003,

Hi All,

I have a table named VehCodes with the following data:

MakeCode, ModelCode
MA , 6
MA , 7
MA , 9
PR , 1
PR , 2
PR , 6
etc

I would like to find the missing seqeunce ModelCode numbers for each MakeCode - from the example above the results would be:
For MA - ModelCode 8
for PR = ModelCodes 3,4,5
I only need a list of MakeCodes with the relavent missing ModelCodes, the format is not relevant.

I have sort of done it in a long winded way by using queries, but this would take quiet some time as there are 81 MakeCodes, with the ModelCodes spanning anywhere from 1 to 999 (never greater than 999 though).

I was thinking if there was maybe a soultion to do this in VBA (combined with SQL)?

Any help in the correct direction will be appreciated.

Thank you.
Michael

 

[Remou]

Perhaps (typed)

sSQL="SELECT DISTINCT MakeCode FROM VehCodes"

Set rs1=CurrentDB.OpenRecordset(sSQL)

Do While Not rs1.EOF
   sSQL="SELECT * FROM VehCodes WHERE MakeCode ='" & rs1!MakeCode & "'"
   Set rs2=CurrentDB.OpenRecordset(sSQL)
   Do while Not rs2.EOF
      i=ModelCode
      rs2.MoveNext
      If rs2.EOF Then 
         Exit Sub
       Else
         If rs2.ModelCode<>i+1 Then
            Debug.Print rs2.ModelCode
         End If
       End If
    Loop
    rs.MoveNext
Loop

I

 

[micang]

Hi Remou,

Thank you for your prompt reply.

I am a bit scethcy with my VBA, so not fully understanding the code, I have put this together:

Sub test()

Dim rs1 As Recordset
Dim ssql As String
Dim rs2 As Recordset
Dim i As Integer

ssql = "SELECT DISTINCT MakeCode FROM VehCodes"

Set rs1 = CurrentDb.OpenRecordset(ssql)

Do While Not rs1.EOF
   ssql = "SELECT * FROM VehCodes WHERE MakeCode ='" & rs1!MakeCode & "'"
   Set rs2 = CurrentDb.OpenRecordset(ssql)
   Do While Not rs2.EOF
      i = ModelCode
      rs2.MoveNext
      If rs2.EOF Then
         Exit Sub
       Else
         If rs2.ModelCode <> i + 1 Then
            Debug.Print rs2.ModelCode
         End If
       End If
    Loop
    rs.MoveNext
Loop
End Sub

It's giving a variable not defined error for ModelCode ( this part of the code: "i = ModelCode")

I am not sure what else I have to add?

Many thanks for your efforts, it's really appreciated.

Michael

 

[Remou]

Oops.

i = rs2!ModelCode

 

[micang]

Thanks Remou,

I have this now:

I amended rs.MoveNext (3 rd last line to rs2.MoveNext - I may be wrong, also the rs2.ModelCode I changed to rs2!ModelCode

Sub test()

Dim rs1 As Recordset
Dim ssql As String
Dim rs2 As Recordset
Dim i As Integer

ssql = "SELECT DISTINCT MakeCode FROM VehCodes"

Set rs1 = CurrentDb.OpenRecordset(ssql)

Do While Not rs1.EOF
   ssql = "SELECT * FROM VehCodes WHERE MakeCode ='" & rs1!MakeCode & "'"
   Set rs2 = CurrentDb.OpenRecordset(ssql)
   Do While Not rs2.EOF
      i = rs2!ModelCode
      rs2.MoveNext
      If rs2.EOF Then
         Exit Sub
       Else
         If rs2!ModelCode <> i + 1 Then
            Debug.Print rs2!ModelCode
         End If
       End If
    Loop
    rs2.MoveNext
Loop
End Sub

This code parses with no errors, in the module, but now I am not sure how I apply this?

Michael

 

[Remou]

Excellent, thanks for fixing the typos.

The code snippet only prints ModelCode to the immediate window, as I was not sure what you wished to do. By modifying the code at the Debug.Print line, you can cause it to insert records or output more information and to different places. You could build a 'missing' table, if that is what you wish.

 

[micang]

Hi Remou,

Thank you so much, I am almost there, the only thing it won't do is loop through the makecodes, not sure why, trying to figure it out.

I am using the code as in my last post.

Michael

 

[PHV]

In the original code provided by Remou, replace rs.MoveNext with rs1.MoveNext

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[micang]

Hi PHV,

This is what I have:

Dim rs1 As Recordset
Dim ssql As String
Dim rs2 As Recordset
Dim i As Integer

ssql = "SELECT DISTINCT MakeCode FROM VehCodes"

Set rs1 = CurrentDb.OpenRecordset(ssql)

Do While Not rs1.EOF
   ssql = "SELECT * FROM VehCodes WHERE MakeCode ='" & rs1!MakeCode & "'"
   Set rs2 = CurrentDb.OpenRecordset(ssql)
   Do While Not rs2.EOF
      i = rs2!ModelCode
      rs2.MoveNext
      If rs2.EOF Then
         Exit Sub
       Else
         If rs2!ModelCode <> i + 1 Then
            Debug.Print rs2!ModelCode
         End If
       End If
    Loop
    rs1.MoveNext
Loop

It's pulling out only one makecodes modelcode that is missing. Can't figure out why it is not looping through, so close.

Thank you both.

Michael

 

[PHV]

Anyway, I'd replace this:
Debug.Print rs2!ModelCode
with this:
Debug.Print rs1!MakeCode, rs2!ModelCode

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[micang]

Thanks guys, finally worked why it was not looping:

Changed

 rs2.MoveNext
      If rs2.EOF Then
         Exit Sub
       Else

to

 rs2.MoveNext
      If rs2.EOF Then
         Exit Do
       Else

Thank you all.

Michael

 

[Remou]

Glad you sorted it out! I will make a better effort or stop roughing out ideas late at night

 

[micang]

Thanks Remou for your efforts, this is really a cool way of checking sequence numbers for my purposes and performance is fantastic too.

It's impressive how you wrote that off the top of your head!

Michael