find missing sequence numbers access 2003 query

I need to see if there are any gaps in a table column which stores sequential numbers. Thank you.
LVL 1
BobbyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you will need VBA codes to do that, is this an option?
BobbyAuthor Commented:
yes
Rey Obrero (Capricorn1)Commented:
first create a table "tblDummy" with one field "RecordNumber"  DataType Number

Sub FindMissingSequence()
dim rs as dao.recordset, minNum as long, maxNum as long, j as long
minNum=Dmin("SequenceNumber","TableName")
maxNum=Dmax("SequenceNumber","TableName")

for j=minNum to maxNum

currentdb.execute "insert into tblDummy(RecordNumber) values(" & j &")"

next

end sub

now create a query like this

select   RecordNumber
from  tblDummy D
left join TableName  TN On D.RecordNumber =TN.SequenceNumber
where TN.SequenceNumber is null
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

BobbyAuthor Commented:
It all runs, I see the results, but what am I looking for? I see some that have gaps and some that are in sequence (no gap).
BobbyAuthor Commented:
also, how would I alter that to make it a make table query instead of just select?
Rey Obrero (Capricorn1)Commented:
what the query meant is

select all the records from tblDummy where recordnumber is not in Tablename

so what you are seeing are all the SequenceNumber that are missing from your table.

to create a make table query

Select A.* Into NewTable
From
(select   RecordNumber
 from  tblDummy D
 left join TableName  TN On D.RecordNumber =TN.SequenceNumber
 where TN.SequenceNumber is null) As A

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BobbyAuthor Commented:
VERY nice, thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.