Hi there
I am trying to make an update query that will update information in all occurances of duplicate records based on the First record for that duplicate set.
There are about 30,000 records in the table, and of them without the duplicates there is only about 4000 records.The duplicates are essential to work we do, so we cannot delete them.
I have a make table query that selects only the first record for a duplicate set based on the account number for that record, and exports the number into a new table. I then have that inner joined into the main table on a form, and the form only shows 1 record and updates the first record on the main table (matches by auto id number)
However in the mornings when new data comes in, I need a query that will run through the table and update all the records with the information entered on the first occurance of that account number, and then move onto the next account number and do the same, etc Does anyone know how I might go about doing this?
If it helps the table looks like this..
ID | AccNumber | Advisor | Action | DateWorked
--------------
1 | 1234567890 | Brendan | Cleared | 30/09/07
2 | 1234567890 |
3 | 6655443322 | Dan | Unworkable | 30/09/07
4 | 6655443322 |
and the information that needs updating is the Advisor, Action, and DateWorked cells.
Many thanks,
Brendan