May 28, 2023, 04:28:30 AM

News:

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.

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:

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.

Bill
When all else fails, get a bigger hammer.