query to get only last entry for every user

[cyberdoc]

Hi guys,
ive a table with entries of users and the orders they made and payments details etc. now i need a query wherein only the latest orders for all users are shown and not all of them. for example,


ORDERID | USERID | ORDERDATE | AMOUNT | PAID | BALANCE |
ORD0001 | USR0001| 12/2/17 | 1500 | 1200 | 300 |
ORD0002 | USR0002| 12/2/17 | 400 | 210 | 190 |


ORD0003 | USR0002| 12/2/17 | 1000 | 800 | 200 |
ORD0004 | USR0003| 12/2/17 | 500 | 200 | 300 |


SO in this we would want
only order 3 to be shown for user2 and not order2.
Thanks in advance

[andy49]

How will you differentiate between two orders from customer2 on 12/2/17 and 12/2/17


Sent from my iPhone using Tapatalk

[andy49]

Create a grouped query with max in the date field



https://support.office.com/en-gb/art...__toc258021887


Sent from my iPhone using Tapatalk

[cyberdoc]

the one with higher order number is the later one and needs to be retrieved. order number is the unique id hence no duplicacy

[andy49]

Ok group the customer id and max the orderid


Sent from my iPhone using Tapatalk

[cyberdoc]

Ok group the customer id and max the orderid


Sent from my iPhone using Tapatalk

but the problem is i need the other fields also and not only these two.. it doesnt work, try oncee

[andy49]

Can I see your current sql? Or a design view?


Sent from my iPhone using Tapatalk

[andy49]

Ok I'm sure there's a shorter route but get the most recent orderid and user (2 field grouped query) and call it qryorder. Then create another query with qryorder and your original table left joined on the orderid fields.


Sent from my iPhone using Tapatalk