Curtis Blaine
asked on
Access 2007 - Create Query That Returns One Record Per Customer
I want to create a query that examines an order table and creates a recordset containing only the most recent order for each customer. Each record in the order table contains 3 fields: an order ID, a dollar amount, and a code to indicate if it is taxable. Here is a sample record:
Order ID Amount Taxable?
======== ======= ========
ABC002 $300.00 Yes
The order ID contains a 3 letter code for the customer and a sequence number. The order ID of ABC002
is the second order placed by customer ABC. Now, here is all the data contained in the table:
ABC001 $25.00 Yes
ABC002 $300.00 Yes
DEF001 $175.00 No
DEF002 $234.00 No
DEF003 $75.00 No
XYZ001 $179.00 Yes
I would want the output recordset to contain these records:
ABC002 $300.00 Yes
DEF003 $75.00 No
XYZ001 $179.00 Yes
Each record is the most recent order for each customer. I could accomplish this using VBA, but it runs quite slowly when there are many records in the table. Can anyone suggest how to create a query that would accomplish this?
Order ID Amount Taxable?
======== ======= ========
ABC002 $300.00 Yes
The order ID contains a 3 letter code for the customer and a sequence number. The order ID of ABC002
is the second order placed by customer ABC. Now, here is all the data contained in the table:
ABC001 $25.00 Yes
ABC002 $300.00 Yes
DEF001 $175.00 No
DEF002 $234.00 No
DEF003 $75.00 No
XYZ001 $179.00 Yes
I would want the output recordset to contain these records:
ABC002 $300.00 Yes
DEF003 $75.00 No
XYZ001 $179.00 Yes
Each record is the most recent order for each customer. I could accomplish this using VBA, but it runs quite slowly when there are many records in the table. Can anyone suggest how to create a query that would accomplish this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
best method would be to write a query which splits your OrderID, something like:
SELECT yourTable.*
FROM yourTable
INNER JOIN (
SELECT Left(OrderID, 4) as Company, MAX(Val(Mid(OrderID, 4)) as MaxSeq
FORM yourTable
Group by Left(OrderID, 4)
) as T ON yourTable.OrderID = T.Company & Format(T.MaxSeq, "0000")
As jim indicated, if your orderID changes (more or less than 4 characters in either part company or sequence), this will fail.
SELECT yourTable.*
FROM yourTable
INNER JOIN (
SELECT Left(OrderID, 4) as Company, MAX(Val(Mid(OrderID, 4)) as MaxSeq
FORM yourTable
Group by Left(OrderID, 4)
) as T ON yourTable.OrderID = T.Company & Format(T.MaxSeq, "0000")
As jim indicated, if your orderID changes (more or less than 4 characters in either part company or sequence), this will fail.
ASKER
Thanks for your comments. I'm working with a database designed by someone else, so I can't easily change the Order ID. Also, I simplified the example. In reality it's a 4 digit customer code plus a 4 digit sequence number, such as "ABCD0001". There will never be more than 9999 orders per customer, so 4 digits is sufficient.
Do you have any suggestions on how to create a query that returns the last order for each customer?
Sincerely, Curtis