Delete one of two duplicates

[robbiedg]

Hi,

I have a database that lists every entry on a customers account, I have used the query wizard to list all duplicate entries (using the account number as a denominator). The table contains Account number, work order, date entered and time entered.

How can I write a query to list the 'Trouble call' that was entered last.

The table can contain about 300+ records..

 

[GumbyD]

Trouble Calls

If you change your query to a grouping a totalling query and then use the last selection in the grouping it will give the last date for each of the grouped values. It sounds to me like you want to delete the duplicate records, which is much tricker and you will probably need some code to loop through a sorted version of your recordset and compare field values looking for duplicate records and then deleting one of them.

 

[st3ve]

Assuming your data is stored in the order entered still then could try this...

Add an new field called, say, fred as an autonumber field type. Then run a query using the Group By /Zigma/Totals function and just have your account number as Count with Max function on autonumber field (fred). This gives the 'latest' use of each account number.

If your data has been 're-ordered' then need to use the date and the time entries to find the most recent. You can still use the same Max function, but as we need to consider date and time simultaneously you cant just write a max function on each, you ned to write the max function on a combined/concatenated field. eg 'date & time' or similar.

HTH.