Remove duplicates

[jpaokx]

Hi again,

I need help on something else. I have a table with orders (100,000 records) and a table with customers (30,000 records). Consequently, a customer may have made multiple orders. Both tables have a unique id that link them together.
My problem is that if I join them with a simple query, I will get 100,000 records. How I can keep the 30,000 records and maintain some records from the order table (for example, I need those with the most recent order)?


Thanks

 

[jdraw]

You have 2 tables.
tblCustomer should have unique CustomerId (PK) and fields related only to Customer.
tblOrder should have unique OrderId (PK), OrderDate (so you can find the latest), OrderAmt (so you can find most expensive), other Order specific info and CustomerID (fk).

 

[jpaokx]

You have 2 tables.
tblCustomer should have unique CustomerId (PK) and fields related only to Customer.
tblOrder should have unique OrderId (PK), OrderDate (so you can find the latest), OrderAmt (so you can find most expensive), other Order specific info and CustomerID (fk).

Yes. Based on your example, suppose the OrderID and OrderDate have 100,000 records from the tblOrder table and the Customer ID from tblCustomer has 30,000 records (these are unique customers). How can I create in this case a table with the unique customers plus new columns with the most recent date?

Should I create a new table with the original table that I had already to do that?