How can I group and get MS Access query to show only rows with a maximum value in a specified field for a consecutive number of times?

HoneyBear 0
Viewed 53

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
date ms-access count sql
1 Answers

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.

June70
Related