No activity in last 30 days

[CMLS]

Hello

I'm trying to create a query to that shows only records that haven't had an entry within the last 30 days.

Obviously it's easy to show the whole list using my current SQL...

*************************************


SELECT EventLog.EnquiryID, EventLog.EventDate
FROM EventLog
*************************************

I'm guessing that they query will have to look at the main table [customers.EnquiryID] to compare records.

I've been trying several things but this one is a little over my head so any help would be gratefully received.

Thanks in advance

[jzwp11]

Welcome to the forum!

Assuming that you might have many records with the same EnquiryID (but different dates), I would create a query that finds the most recent eventdate for each enquiryID

query name: qryMostRecentEvent
SELECT EventLog.EnquiryID, Max(EventLog.EventDate) as MostRecentEventDate
FROM EventLog
GROUP BY EventLog.EnquiryID


Now you can create another query that find those enquiries which have a MostRecentEventDate > 30 days from today

SELECT qryMostRecentEvent.EnquiryID, qryMostRecentEvent.MostRecentEventDate
FROM qryMostRecentEvent
WHERE datediff("d",qryMostRecentEvent.MostRecentEventDat e,date())>30

[AccessPonderer]

You can also use the dateAdd function, pretty much the same but it can't harm to have another solution:

WHERE DateAdd("d", 30, qryMostRecentEvent.MostRecentEventDate) > now()
or
WHERE DateAdd("d", -30, now()) > qryMostRecentEvent.MostRecentEventDate

[CMLS]

It works! Thank you, thank you, thank you. I would have never have got that by myself.

[jzwp11]

You're welcome. Glad we were able to help out.