Missing Numbers

About 8 years ago an office worker decided to put all the admission information for our non profit on access. I'm not sure how or why she set it up the way she did. I'm trying to finish up 27 years of records but have found that our admissions numbers are 19,839 and the actual data put on the tables is only 19801 so I'm missing 38 records. Is there anyway to find the missing numbers without going back and looking at all the admissions on the table. (Does this even make sense?) If I can do something to make it clearer let me know.

Thanks for your assistance.

Lisa

bacnvol said:

About 8 years ago an office worker decided to put all the admission information for our non profit on access. I'm not sure how or why she set it up the way she did. I'm trying to finish up 27 years of records but have found that our admissions numbers are 19,839 and the actual data put on the tables is only 19801 so I'm missing 38 records. Is there anyway to find the missing numbers without going back and looking at all the admissions on the table. (Does this even make sense?) If I can do something to make it clearer let me know.

Thanks for your assistance.

Lisa

Click to expand...

Where is the "admissions numbers" information stored? If it is on an Access or Access Friendly database, then it is most likely possible that the information you are looking for could be attained. If not (EX: Stored on paper), then the odds drop considerably.

It's on our access 2007 database on a table she set up. The origional was the version before 2003 and then was 2003 and is now 2007.

Lisa

If your admission numbers are stored as Autonumber fields then there may not be any missing records. Autonumbers can have gaps in them.

Nope...it was saved as text. The origional table was auto numbered but in the conversion from 2003 to 2007 it saved it as text and when I tried to go back to auto number it told me I couldn't.

Here is a crude demo that will do what you want it to do.

Since your numbers are in a text field, be sure to sort them prior to running the qryCheckOrder or they will all end up out of sequence.

The way this works is there is a global variable which stores the previous number. The next number in the query is sent to a function which compares that to the previous number. If it is one number prior it will put a "-" in the field. If a number has been skipped, it will put a "Some is missing" in the field.

You can do "Some is missing" critieria to limit.

Basically all this will do is tell you 'hey, there are a/some numbers missing between this record and the one before it."

Again, it is crude - but it was meant for a one time use and dispose.

-dK