Finding gaps in sequential field number

[impulse24]

Hi,

I received a database that has a field named Certificate Number, and the field is supposed to have sequential certificate numbers. However I have noticed there are gaps. There is cert # 1,2,3,4,5,9,10

Is there are query that I can do to show me what the gaps are: Example from above would show that 6,7,8 are missing. Any help would be appreciated.

 

[THoey]

Are there a finite number of certs? If so, you could use the NOT IN clause:

Select MyDocuments.DocumentID, MyCerts.CertNo
FROM MyDocuments, MyCerts
WHERE MyDocuments.DocumentID = MyCerts.DocumentID AND
MyTable.CertNo NOT IN (1,2,3,4,5,6,7,8,9,10)

Does that help? After looking back at your request, these might be all in one field. Not sure... Terry M. Hoey

 

[impulse24]

Thanks for the help, but I don't have a finite number of certs, and each certno is in a separate field. any more help would be appreciated.

 

[THoey]

I am not sure, but a description of your table might help me or someone else help you better... Terry M. Hoey

 

[impulse24]

Database:

certs.mdb

Table Name:

certs

Field in table:
certNo

Values in certNo field:
1
2
3
4
5
6
...

The above increments all the way up to 68,000

So I want to query the cert's table looking for when the next sequential certno does not exist.

Please help.

 

[THoey]

I am not sure if you MUST have a query or not. I was 99% done with a function that would do this for you when I sent Access into an endless loop. I had to kill it and of course, I hadn't saved it yet. (@#$%^&*!!!)

If you can use a function, the basic logic is this:

1. Declare variables (DB, rst1, rst2, Counter)
2. Open the Certs table (OpenRecordset - Select * - Order by Certs)
3. Open a MissingCerts table (OpenRecordset - Select *)
4. Initialize Counter = 1
5. Do While Not rst1.EOF
if rst1.Certs = Counter then
rst1.MoveNext
else
rst2.AddNew
rst2!Certs = Counter
rst2.Update
end if
Counter = Counter + 1
Loop

That should do it, but realize that I did not get to test it. After that, you can SELECT * from the MissingCerts table and have a list of all missing certs.

Hope that helps. Terry M. Hoey

 

[Craig0201]

Impulse,

I think what you are saying is that you have a range of numbers from 1-68000+ with some missing and you want to see the missing ones?

If so then there is little that you can do about it using a simple query. SQL can only return things it knows are there or things that are in one place but not in others. So how about doing something like the following.

Create a table with one field: tblPossibleCertIds
Give the table only one fieldossibleCertIds

Now try something like the following to populate up the new temporary table. I've created it in DAO so you might need to adjust to ADO if you're using Access2000

Dim db as database
Dim rst as Recordset
Dim MaxCertID as Integer
Dim indx as Integer

Set db = CurrentDB
Set rst= sb.OpenRecordset("SELECT MAX CertID AS Max_Cert_ID FROM YourTable&quot

rst.MoveFirst
MaxCertID=rst!MaxCertID

rst.Close
Set rst=nothing

Set rst=db.OpenRecordset("SELECT * FROM tblPossibleCertIds&quot

With rst
For indx= 1 TO MaxCertID
.AddNew
!PossibleCertID = indx
.Update
Next
End With
rst.close
set rst=nothing
set db=nothing

You will now e able to use the Unmatched query wizard to find which CertIDs have not been used.

HTH

Craig

 

[Craig0201]

And a similar approach form Terry at the same time.......nice work Terry.....

Craig

 

[THoey]

Thanks Craig. Would have had it sooner with exact code if I wouldn't sent the loop into the ether... >:-< Terry M. Hoey

 

[MichaelRed]

Well, what is done is done.


Select MyDocuments.DocumentID, MyCerts.CertNo
FROM MyDocuments, MyCerts
WHERE MyTable.CertNo < (Select Max(MyTable.CertNo) From MyTable) MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tomschafer]

Check this out Impulse24....

SELECT
a.certNo Gap
FROM
certs a
WHERE
certNo not in (SELECT certNo FROM certs b WHERE b.certNo-a.certNo=1) and
(a.certNo<(SELECT max(certNo) FROM certs a))

 

[tomschafer]

Sorry I missed a couple of aliases in there...
SELECT
a.certNo Gap
FROM
certs a
WHERE
a.certNo not in (SELECT b.certNo FROM certs b WHERE b.certNo-a.certNo=1) and
(a.certNo<(SELECT max(a.certNo) FROM certs a))