0

I have 2 tables in MS Access with the following values

Customer
id | name 
1  | jon  
2  | bob
3  | jack

Order
id | amount | date        | customer
5  | 50     | 3/10/2017   | 1
4  | 100    | 3/10/2017   | 1
3  | 45     | 2/28/2017   | 2
2  | 10     | 3/10/2017   | 3
1  |  5     | 3/10/2017   | 2

I want to get an output of

name  | orderid | amount
jon   | 5       | 50 
bob   | 3       | 45
jack  | 2       | 10

I want to get amount of the latest order id per customer, however I keep getting this

name  | orderid | amount
jon   | 5       | 50 
jon   | 4       | 100
bob   | 3       | 45
bob   | 2       | 10
jack  | 1       | 5

I used the query designer and have used the function MAX() to the order id, GROUP BY to all columns (MS Access does not allow to group the rows using a single column), DISTINCT and DISTINCTROW, as well as set the query properties "Unique Records" to Yes but the duplicate records still shows.

Bbob
  • 1
  • Do you want to see just the latest order for each customer? What do you want to happen if there are two orders from the same customer on the same date? – SunKnight0 Mar 03 '17 at 15:48
  • @SunKinght0 yes, I want to get the latest order for each customer. If there are two order from the same date, I want to get the latest or the highest order id – Bbob Mar 06 '17 at 07:42
  • I will try to provide some help when time permits. Are your Order IDs sequential? Is it a guarantee that a newer order will always have a higher Order ID? In that case you just need a sub query that gets the Max(order.id) grouped by customer ID and link that to your two tables. – SunKnight0 Mar 06 '17 at 16:58

0 Answers0