June 13, 2024, 01:09:54 AM


Own IWBasic 2.x ? -----> Get your free upgrade to 3.x now.........

Useful Access Queries

Started by billhsln, June 19, 2018, 05:51:16 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.


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:

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

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

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.

When all else fails, get a bigger hammer.