Select Records where Value appears several times over specified period.

[DTK0902]

Hi all,
Recently new to this forum and so far it has been a great help in helping me and the people on here are all very informative and friendly. Got a query problem, within my database which is used for medicine management and administration, i want to be able to run a query which will return the values of the animals who have had a recorded high temperature over a specified period of time, e.g over 1 month, 3 months, 6 months and 1 year.



Was just wondering how you set up the criteria to only select and return the animal who has had more than (say 3) enteries over that period of time.

Regards,
Daniel

[June7]

Do you want all the fields of record or just the animal and the count?

If just the animal and count, do a GROUP BY (Totals). Apply criteria to the count of >=3 and include data range filter criteria.

If you want the complete record, a little harder to do.

A subquery is one way, review http://allenbrowne.com/subquery-01.html
especially the example for Aggregation: Counts and totals

Another is use of domain aggregate function (DCount, DLookup, DAvg, etc) expression, something like:
SELECT * FROM tablename WHERE DCount("*","tablename","animalID=" & [animalID] & " AND datefield BETWEEN #" & [startdate] & "# AND #" & [enddate] & "#")>=3;

[pbaldy]

It would help to know your data structure, but it might be as simple as:

SELECT Animal, Count(Temp) AS HowHot
FROM TableName
WHERE Temp > 98.6
GROUP BY Animal
HAVING Count(Temp) > 2

Edit: you'd also want your time frame restriction in there.

[DTK0902]

Do you want all the fields of record or just the animal and the count?

If just the animal and count, do a GROUP BY (Totals). Apply criteria to the count of >=3 and include data range filter criteria.

If you want the complete record, a little harder to do.

A subquery is one way, review http://allenbrowne.com/subquery-01.html
especially the example for Aggregation: Counts and totals

Another is use of domain aggregate function (DCount, DLookup, DAvg, etc) expression, something like:
SELECT * FROM tablename WHERE DCount("*","tablename","animalID=" & [animalID] & " AND datefield BETWEEN #" & [startdate] & "# AND #" & [enddate] & "#")>=3;

Hi June,
For this I would be looking All Animals returned who have had a temperature recorded multiple times over a defined period. This would be beneficial for us in identifying animals with on-going health issues and enable us to be better informed. I will try your first recommendation of the group by, and will also look into subquery.

I had looked at the domain aggregate function and think it could be the way forward if none of the above work.

Regards,
Daniel