Need Help

R

Ron

I want to know if there is a way to search a table for sequential records. In
other words I want to know if all records in the table are in order. If I
have 100 records in the table, I want to know if all 100 records are there
without having to search each record. I would like Access to search for me. I
would like to have a pop-up signal if there is a missing record. Can this be
done?

Thanks
Ron
 
V

Van T. Dinh

Since Records are stored unordered in a Table (like tings in a bucket, you
may need to clarify what you are trying to do.

Do you meant you have Records numbered 1 to 100 and you want to check if
there are any missing numbers between 1 to 100?

HTH
Van T. Dinh
MVP (Access)
 
R

Ron

Van,
Yes, I have records that are numbered 1 to 100 and I want to check if any
are missing.
 
J

John Vinson

I want to know if there is a way to search a table for sequential records. In
other words I want to know if all records in the table are in order. If I
have 100 records in the table, I want to know if all 100 records are there
without having to search each record. I would like Access to search for me. I
would like to have a pop-up signal if there is a missing record. Can this be
done?
Click to expand...

Note that Autonumber fields will essentially ALWAYS have gaps. If you
delete a record its autonumber will be retired and never reused; if
you even hit <Esc> while adding a record, the autonumber tentatively
assigned to that record will be discarded and not reused.

If you have a numeric field, you can create a Query by adding your
table to the query grid TWICE. Access will alias the second instance
of the table by adding _1 to the name. Join the field that you want to
gap-check in one table (I'll call it ID) to the corresponding field in
the second instance of the table.

Select the Join line and choose Option 2: "Show all records in Table
and matching records in Table_1". Select the ID field from both
Tables.

Now switch to SQL view and edit the JOIN clause from

FROM Table LEFT OUTER JOIN Table_1 ON Table.ID = Table_1.ID

to

FROM Table LEFT OUTER JOIN Table_1 ON Table.ID + 1 = Table_1.ID

Now put a criterion on Table_1.ID of

IS NULL

If you now open the query, you will see the ID of each record
immediately preceding a gap. Ideally there will just be one record -
the largest.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
V

Van T. Dinh

John Vinson's solution should work fines in this case.

Van T. Dinh
MVP (Access)
 
Top