Mark Duplicate Records

[philipscodes]

Greetings Experts,

I have a Access DB where I store sales data from our CRM server.
I base this DB for all my Excel calculations/reports via MS Query.
We sell cars.

The sales table in Access has a field 'CREDITED_INVOICE' that maintains a manual entry as of now, to differentiate between the actual invoice and the credited invoice.
From the report that the CRM provides, the only way to know if the invoice was credited and re-invoiced is via the latest system generated Invoice number 'INV_No'.
So, while the Access table stores both the credited and new invoice, it will obviously have a duplicate entry in the Chassis (VIN number) field.

My requirement:
The 'CREDITED_INVOICE' field should always store the value 'INVOICED' when a fresh CRM report is uploaded to the Access table.
However, when Access notices a duplicate CHASSIS, it should mark the value 'CREDITED' instead on all the previous records - (by observing the Older Invoice Number).

Warm Regards,


Philip

[aytee111]

I assume a file is imported into the database and the table is then updated? If so, this will be two queries, one to append all the records, the second to join the two tables on VIN number and update the one to Credited. Altho I am not sure how you would identify which record to update, is it the one with the highest invoice number?

[philipscodes]

Yes, That's right - highest Invoice number.
Will an Aggregate query work with Max on the Invoice number? I saw this solution from another similar thread.
Then a query for updating to 'CREDITED' can be used.

Regards,
Philip

[aytee111]

Totals queries cannot be used in updating. There are other ways of doing it, such as using DMax, doing it in VBA, making a temp table, etc.

[philipscodes]

Hi Aytee111,

Could you kindly advise me using the DMax method on this particular case?
I am good at VBA solutions but would like to avoid it as much as possible.

Thanks,
Philip

[ArviLaanemets]

Create a saved query
qLastInvoice = SELECT inv.Chassis, MAX(inv.INV_NO) AS LastInvoice FROM AL_Invoice_AN6 inv GROUP BY inv.Chassis;

and another one
uqInvoices =
UPDATE (AL_INVOICE_AN6 inv LEFT JOIN qLastInvoice li ON li.Chassis = inv.Chassis AND li.LastInvoice = inv.INV_NO)
SET Credited_Deal = "CREDITED"
WHERE li.Chassis = Null

Now run the second query.