ms access date query (1 Viewer)

aouaas

New member
Local time
Today, 13:44
Joined
Mar 4, 2021
Messages
2
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

I’m here to help
Staff member
Local time
Today, 04:44
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

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

June7

AWF VIP
Local time
Today, 03:44
Joined
Mar 9, 2014
Messages
5,425
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

New member
Local time
Today, 13:44
Joined
Mar 4, 2021
Messages
2
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

I’m here to help
Staff member
Local time
Today, 04:44
Joined
Oct 29, 2018
Messages
21,358
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.
 

Users who are viewing this thread

Top Bottom