Useful Access Queries

[billhsln]

If you have created a 'RecID' field as Integer and would prefer it to be used like a COUNTER(1,1) and the RecID is only internally useful, in other words, if you renumber it it will not screw up other tables that would reference it.  Then you can do:

Code Select Expand

SELECT recid, DCount("recid","customers","recid <= " & [recid]) AS [Counter]
FROM customers;

This will show you the comparison between RecID and Counter.  Then do:

Code Select Expand

update customers set recid=DCount("recid","customers","recid <= " & [recid]);

This will update the table.  This will work as long as the recid is sequential and the replacement numbers don't overlap.  If they do, you might have to do the update more than once to correct the entire table.

Bill