How do I update a field in Microsoft Access with the RowID of reco

[Guest]

How do I update a field in Microsoft Access with the RowID of each record? I
don't want use auto number in the table as I would only like to populate the
rows in the table which have been extracted by the query.

 

[John W. Vinson]

How do I update a field in Microsoft Access with the RowID of each record? I
don't want use auto number in the table as I would only like to populate the
rows in the table which have been extracted by the query.

What value do you want to update it WITH? A query doesn't *have* a RowID or
record number.

Note that Queries are dynamic objects - changing the criteria, or the sort
order, or the underlying data can and will change the sequence and number of
records displayed in the Query, so a sequential number that is accurate today
may well be completely wrong tomorrow!

John W. Vinson [MVP]

 

[Guest]

Hi John

Well my real problem is that I have a table with duplicates in and I would
like to maintain one record of each duplicate. I thought if I could run a
query to find the duplicate and then update each a field with the rowid I
could then use this field to select the records I didn't want and then delete
them in one go.

Have you maybe got a better way of doing this?

Thanks
Charlene

 

[John W. Vinson]

Hi John

Well my real problem is that I have a table with duplicates in and I would
like to maintain one record of each duplicate. I thought if I could run a
query to find the duplicate and then update each a field with the rowid I
could then use this field to select the records I didn't want and then delete
them in one go.

Have you maybe got a better way of doing this?

Yes. Create a new table with the same definition as yours; put a unique Index
on the combination of fields that define a duplicate; and run an Append query
to append data from the table with duplicates into this one. The first example
of each pair (or larger set) of duplicates will be added; subsequent members
will be rejected with a warning message.

John W. Vinson [MVP]