Missing record numbers.

[thomsond]

I am looking to create a table in Access which displays all of the numbers missing from a field in another table.

For example, if a field name the_number had the following records: 1 2 4 5, i would like to return the value 3 in the new table.

I am dealing with approx 90,000 records and therefore cannot use Excel.

Any help would be greatly appreciated.

 

[jsteph]

Well, the simplest to execute if your example has numeric values like that, would be to create a dummy table with a single field of Long type. Run a quick loop to fill it incrementally with numbers from your lowest to the highest in the current table.

Then create a query, and in the query grid, link the 'the_number' to the dummy field's sole field, and make it an outer join. (right click on the little black 'join' line, and check the "Show all records from dummy table and only those from the other table that match"

Then in criteria, under the original table's 'the_number', put Is Null.
--Jim

 

[thomsond]

Thanks jsteph, i created another table and then set up a variable to compare against the record number in the first table, if it existed i moved to the next record, or else i inserted the record into table 2, easy peasy.

Thanks for the help.