ms access date query

[aouaas]

hello. english is not my primary language so please be kind. i'm new to microsoft access, i know the very very basics only. at work we use an access database with 2 tables, named clients and orders. in the orders table, there are dates of the orders of each client, multiple orders per client. is there a way to create a query that will show all clients whose last order was more than 3 months or 6 months ago?
* could an admin move this post to queries? i posted here by mistake.

 

[theDBguy]

Hi. Welcome to AWF!

Certainly (on both requests)! On the first one, you could try using a subquery (or a stacked query).

 

[June7]

Move to Queries not Reports?

Maybe:

SELECT Clients.*, MaxOD FROM Clients LEFT JOIN (SELECT ClientID, Max(OrderDate) AS MaxOD FROM ORDERS GROUP BY ClientID) AS MaxOrders ON Clients.ID = MaxOrders.ClientID WHERE MaxOD < DateAdd("m", -3, Date());

DMax() domain aggregate function could be used instead of nested query but performance likely much slower.

 

[aouaas]

thanks for the replies. i didn't know anything about subqueries or writing sql code. i did a little search, watched some tutorial videos and found a solution to my problem!

 

[theDBguy]

thanks for the replies. i didn't know anything about subqueries or writing sql code. i did a little search, watched some tutorial videos and found a solution to my problem!

Congratulations! Glad to hear you got it sorted out. Good luck with your project.