How can I group and get MS Access query to show only rows with a maximum value in a specified field-CodePudding

I have a large access table that I need to pull specific data from with a query.

I need to get a list of all the IDs that meet a specific criteria, i.e. 3 months in a row with a cage number less than 50.

The SQL code I'm currently working with is below, but it only gives me which months of the past 3 had a cage number below 50.

SELECT [AbBehWeeklyMonitor Database].AnimalID, [AbBehWeeklyMonitor Database].Date, [AbBehWeeklyMonitor Database].Cage
FROM [AbBehWeeklyMonitor Database]
WHERE ((([AbBehWeeklyMonitor Database].Date)>=DateAdd("m",-3,Date())) AND (([AbBehWeeklyMonitor Database].Cage)<50))
ORDER BY [AbBehWeeklyMonitor Database].AnimalID DESC;

I would need it to look at the past 3 months for each ID, and only output if all 3 met the specific criteria, but I'm not sure where to go from here.

Any help would be appreciated.

Data Sample:

Date	AnimalID	Cage
6/28/2022	12345	50
5/19/2021	12345	32
3/20/2008	12345	75
5/20/2022	23569	4
8/20/2022	23569	4
5/20/2022	44444	71
8/1/2012	44444	4
4/1/2022	78986	30
1/20/2022	78986	1
9/14/2022	65659	59
8/10/2022	65659	48
7/14/2022	65659	30
6/14/2022	95659	12
8/14/2022	91111	51
7/14/2022	91111	5
6/14/2022	91111	90
8/14/2022	88888	4
7/14/2022	88888	5
6/14/2022	88888	15

CodePudding user response：

Consider:

Query1:

SELECT AnimalID, Count(*) AS Cnt
FROM Table1
WHERE (((Cage)<50) AND (([Date]) Between #6/1/2022# And #8/31/2022#))
GROUP BY AnimalID
HAVING (((Count(*))=3));

Query2

SELECT Table1.*
FROM Query1 INNER JOIN Table1 ON Query1.AnimalID = Table1.AnimalID
WHERE ((([Date]) Between #6/1/2022# And #8/31/2022#));

Output:

Date      AnimalID  Cage
6/14/2022   65659   12
7/14/2022   65659   30
8/10/2022   65659   48
6/14/2022   88888   15
7/14/2022   88888   5
8/14/2022   88888   4

Date is a reserved word and really should not use reserved words as names.