Challenging Query

[MuSuLPhReAk]

Stuck on this one.

This database has duplicate customers. What I'm trying to do is to fin
the last invoice on each customer.

Fields that can possibly be used to find duplicates are customer name
address or phone number.

What I've done is taken all the data I needed from a query and put i
into a table. The problem of course is the duplicates.

Now what I need to know is how to retrieve the most recent order base
on order date.

Fields I have:
InvoiceID
Customer Name
Customer Address
Customer Phone Number
Invoice Date

Any help would be greatly appreciated. Can be done in SQL. Thanks :

 

[JohnFol]

If each customer can have multiple invoices, then the structure is wrong. I
would expect each customer to have a specific ID, and in the invoice table
that ID to be mentioned. For example the Invoice table could be

InvoiceID
Invoice Date
CustomerID

This makes your query easy.

For an idea of what I mean, look at Northwind and the relationship between
Orders and Customers

 

[MuSuLPhReAk]

Ideas are welcome to.

 

[___]

Use the Max() function to retrieve the most recent date.
Something like...

SELECT YourTable.InvoiceID, YourTable.[Customer Name]
YourTable.[Customer Address], YourTable.[Customer Phone Number]
Max(YourTable.[Invoice Date]) AS [MaxOfInvoice Date]
FROM YourTable
GROUP BY YourTable.InvoiceID, YourTable.[Customer Name]
YourTable.[Customer Address], YourTable.[Customer Phone Number];

Change "YourTable" to the name of your table.

HT