Skipped Numbers

[jocat]

I maintain an Access 97 database for parking violations for the parking lots of a Public Transportation Authority. I have a table of all parking violations with the ticket number as the primary key. The tickets come in books of 25 tickets and are submitted to the office for data entry.

There are various ticket books that have been mistakenly been filed as entered, yet they have not been entered into the system. Also, there are times the data entry person has skipped a ticket or two by accident.

I want to write a query that will give me the ticket numbers that have been skipped so that we can retrieve them from the filing cabinet and enter them.

I only want to look at the last few months. There are probably about 15,000 total tickets for that period, and maybe 100 or so that have not been entered. The series i am concerned with starts at # 200000.

I suspect this is relatively simple, but I am going through a mental block (probably the 60's catching up to me).

Can anyone help? It would be greatly appreciated

 

[philcon]

i) create a table with a "ticketno" and "flag" field (tbl_MissTickets)
ii)create a for next loop to populate it with a sequence of numbers 200k to 215k

ie

*************

For x = 200000 to 215000

MySQL ="INSERT INTO tbl_MissTickets ( ticketno ) " & _
"SELECT " & x & " AS ticketNO;"

Domd.RunSQL MySQL

Next x

*********
Then create an update query as follows:

*******
UPDATE tbl_MissTickets INNER JOIN tbl_tickets ON tbl_tickets.ticketno = tbl_MissTickets.ticketno SET tbl_MissTickets.flag = "Not MISSING";
******

You will then have the sequence of ticket numbers where a null flag field indicates a missing ticket.

regards


Phil

 

[PeasNCarrots]

So the ticket #'s start with 200000 all the way to 215000?

 

[jocat]

Tickets are constantly being entered. It is better to say that the ticket numbers are greater than 200000.

Joe

 

[jocat]

Actually, now that I look closer there are currently 6000 tickets in the series. The range is 255501 to 561501.

Joe