Link to home
Start Free TrialLog in
Avatar of Curtis Blaine
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?
ASKER CERTIFIED SOLUTION
Avatar of Jeff Tennessen
Jeff Tennessen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Curtis Blaine
Curtis Blaine

ASKER

Hi Jim,
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
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.