Query to to exclude records with a date within last 4 months, and show records where most recent date is older than that

[kevin_maher]

Hi all, am hoping to get some help with this issue I'm facing, please.

I work for a private training organisation that trains the people that work in child care centres. We have a Business Manager who amongst other things tries to visit the centres on a regular basis. We have a spreadsheet of all the centres that I've imported into Access, with centre details in the t_Centres table (ID, Centre Name, Address, Suburb and Region plus a few other fields). The visits are in t_Visits (ID, Centre_ID, Date_visited, SpokeTo and Comments).

I am trying to create a report which will show the centres that were last visited 120+ days ago, and I've been able to get a query to work on its own (shown below),
SELECT SubQuery.Centre_ID, SubQuery.MaxOfDate_visited, SubQuery.[Centre Name], SubQuery.Suburb, SubQuery.Region
FROM (SELECT t_Visits.Centre_ID, Max(t_Visits.Date_visited) AS MaxOfDate_visited, t_Centres.[Centre Name], t_Centres.Suburb, t_Centres.Region FROM t_Centres INNER JOIN t_Visits ON t_Centres.ID = t_Visits.Centre_ID GROUP BY t_Visits.Centre_ID, t_Centres.[Centre Name], t_Centres.Suburb, t_Centres.Region) AS SubQuery
WHERE (((SubQuery.[MaxOfDate_visited])<=DateAdd("d",-120,Date())) AND ((SubQuery.[Region])=[Forms]![f_Reports]![cboRegion]))
ORDER BY SubQuery.[Centre Name];

,...but when put into a form (which is itself called from the Reports form after the user chooses the region from cboRegion, then the button they click next to it will launch that report), it asks for Date_visited... and whatever date gets entered is what is shown as the date on the report. If I leave it blank then there's no dates showing.

What I want it to give me is basically what you see below, with the date of the last visit showing alongside the centre instead of the 1/4/24 that I entered just now, please. As an exmple, if Centenary FDC had been visited on June 14th then it would not show on this report, and if C & K Taranganba was visited on April 18th then 18/04/24 would show up.

Let me know if I need to clarify anything further.

Kevin

 

[plog]

Nothing in your query requires the inputting of a date. Further, no fields of the query are called 'Date_visited'.

If your report is asking for that it means an unbound field/value of the report is named 'Date_Visited'. My guess is that you mistakenly put that field in the details section of your report where all those 1/4/24 values are showing. That field should instead be using the MaxofDate_visited value from your query

 

[CJ_London]

Your criteria is using

<=DateAdd("d",-120,Date())

Shouldn’t that be referencing a control on you form?

And why bother with a sub query?

 

[DHookom]

Hi all, am hoping to get some help with this issue I'm facing, please.

I work for a private training organisation that trains the people that work in child care centres. We have a Business Manager who amongst other things tries to visit the centres on a regular basis. We have a spreadsheet of all the centres that I've imported into Access, with centre details in the t_Centres table (ID, Centre Name, Address, Suburb and Region plus a few other fields). The visits are in t_Visits (ID, Centre_ID, Date_visited, SpokeTo and Comments).

I am trying to create a report which will show the centres that were last visited 120+ days ago, and I've been able to get a query to work on its own (shown below),
SELECT SubQuery.Centre_ID, SubQuery.MaxOfDate_visited, SubQuery.[Centre Name], SubQuery.Suburb, SubQuery.Region
FROM (SELECT t_Visits.Centre_ID, Max(t_Visits.Date_visited) AS MaxOfDate_visited, t_Centres.[Centre Name], t_Centres.Suburb, t_Centres.Region FROM t_Centres INNER JOIN t_Visits ON t_Centres.ID = t_Visits.Centre_ID GROUP BY t_Visits.Centre_ID, t_Centres.[Centre Name], t_Centres.Suburb, t_Centres.Region) AS SubQuery
WHERE (((SubQuery.[MaxOfDate_visited])<=DateAdd("d",-120,Date())) AND ((SubQuery.[Region])=[Forms]![f_Reports]![cboRegion]))
ORDER BY SubQuery.[Centre Name];

,...but when put into a form (which is itself called from the Reports form after the user chooses the region from cboRegion, then the button they click next to it will launch that report), it asks for Date_visited... and whatever date gets entered is what is shown as the date on the report. If I leave it blank then there's no dates showing.

What I want it to give me is basically what you see below, with the date of the last visit showing alongside the centre instead of the 1/4/24 that I entered just now, please. As an exmple, if Centenary FDC had been visited on June 14th then it would not show on this report, and if C & K Taranganba was visited on April 18th then 18/04/24 would show up.

View attachment 115904

Let me know if I need to clarify anything further.

Kevin

I would simplify by breaking the query with a subquery into two separate queries, the sub and the main. I expect you are smart with queries and will have no problem with this.

 

[MarkK]

I don't see why you even need a subquery. Does this query work with your data?

SELECT tv.Centre_ID, Max(tv.Date_visited) AS MaxOfDate_visited, tc.[Centre Name], tc.Suburb, tc.Region
FROM t_Centres As tc INNER JOIN t_Visits As tv ON tc.ID = tv.Centre_ID
GROUP BY tv.Centre_ID, tc.[Centre Name], tc.Suburb, tc.Region
WHERE tv.Date_visited > Date() - 120 AND tc.Region = [Forms]![f_Reports]![cboRegion]
ORDER BY tc.[Centre Name];

This query aliases the table names for clarity, changes the date math, and performs the date filter prior to executing the Max(), which is probably faster anyway.

 

[kevin_maher]

I don't see why you even need a subquery. Does this query work with your data?

SELECT tv.Centre_ID, Max(tv.Date_visited) AS MaxOfDate_visited, tc.[Centre Name], tc.Suburb, tc.Region
FROM t_Centres As tc INNER JOIN t_Visits As tv ON tc.ID = tv.Centre_ID
GROUP BY tv.Centre_ID, tc.[Centre Name], tc.Suburb, tc.Region
WHERE tv.Date_visited > Date() - 120 AND tc.Region = [Forms]![f_Reports]![cboRegion]
ORDER BY tc.[Centre Name];

This query aliases the table names for clarity, changes the date math, and performs the date filter prior to executing the Max(), which is probably faster anyway.

Thank you MarkK, yes it in essence does in a slightly modified form. I had to fight some syntax errors here and there, and have gotten closer however it shows those that were visited 4+ months ago regardless of whether or not they have been visited within 4 months from today - which is not what I'm after.


SELECT tv.Centre_ID, tc.Suburb, tc.Centre_Name, tc.Region, Max(tv.Visit_Date) AS MaxOfDate_Visited

FROM t_Centres As tc INNER JOIN t_Visits As tv ON tc.ID = tv.Centre_ID

WHERE (((tv.Visit_Date)<=DateAdd("m",-4,Now())) AND ((tc.Region)=[Forms]![f_Reports]![cboRegion]))

GROUP BY tv.Centre_ID, tc.Suburb, tc.Centre_Name, tc.Region

ORDER BY tc.Suburb;

 

[kevin_maher]

I would simplify by breaking the query with a subquery into two separate queries, the sub and the main. I expect you are smart with queries and will have no problem with this.

Not as smart as that, I will admit - thank you for the suggestion though. I learnt Access 25 years ago and have had very little exposure to it till now, so am quite rusty but the rust is being sanded off as I go. I had got the subquery from ChatGPT, but wasn't overly happy with its answer.

 

[kevin_maher]

Nothing in your query requires the inputting of a date. Further, no fields of the query are called 'Date_visited'.

If your report is asking for that it means an unbound field/value of the report is named 'Date_Visited'. My guess is that you mistakenly put that field in the details section of your report where all those 1/4/24 values are showing. That field should instead be using the MaxofDate_visited value from your query

Thanks - I ultimately found that I had a text field in the form which was looking for that field. Was only when I changed the text field to read off my now renamed Visit_Date field that I realised why it was asking for that.

 

[kevin_maher]

Your criteria is using

<=DateAdd("d",-120,Date())

Shouldn’t that be referencing a control on you form?

And why bother with a sub query?

Ta - ultimately it probably will feed off a control, but for now the requirement is for anything we haven't visited in at least 4 months, hence the hard-coded timeframe.

I got the subquery from ChatGPT... but wasn't happy with it so it didn't stay.

 

[kevin_maher]

Got it working now by the looks of things. Earlier versions weren't excluding a centre that had been visited within the last 4 monhts, so I added that in as a subquery, and it works now by providing a list of centres that are not in the query on those that have been visited within the last 4 months:

SELECT tv.Centre_ID, tc.Suburb, tc.Centre_Name, tc.Region, Max(tv.Visit_Date) AS MaxOfDate_Visited
FROM t_Centres AS tc INNER JOIN t_Visits AS tv ON tc.ID = tv.Centre_ID
WHERE (((tv.Centre_ID) Not In (SELECT q_Visited_Within_4_months.Centre_ID from q_Visited_Within_4_months)) AND ((tc.Region)=[Forms]![f_Reports]![cboRegion]))
GROUP BY tv.Centre_ID, tc.Suburb, tc.Centre_Name, tc.Region
ORDER BY tc.Suburb;