Prev: Using IIF to pass query criteria
Next: Append Record with Next Consecutive Number (not Autonumber)
From: Gringarlow on 2 Apr 2010 11:07
I need to find each time a code occurs 5 or more times in a 7 day period.
Example:
CodeA and Town are fields in tblHelpr that I add rows of data to every
morning.
My query will run after the table is updated.
If CodeA occurs in a specific Town 5 or more times in the last 7 days, I
need to see that in my query.


From: Jerry Whittle on 2 Apr 2010 11:22
SELECT tblHelpr.Town,
tblHelpr.CodeA,
Count(tblHelpr.CodeA) AS CountOfCodeA
FROM tblHelpr
WHERE tblHelpr.TheDate > Date()-7
GROUP BY tblHelpr.Town,
tblHelpr.CodeA
HAVING Count(tblHelpr.CodeA>5;

You didn't say the date field name so change TheDate to the proper field name.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Gringarlow" wrote:

> I need to find each time a code occurs 5 or more times in a 7 day period.
> Example:
> CodeA and Town are fields in tblHelpr that I add rows of data to every
> morning.
> My query will run after the table is updated.
> If CodeA occurs in a specific Town 5 or more times in the last 7 days, I
> need to see that in my query.
>
>
 | 
Pages: 1
Prev: Using IIF to pass query criteria
Next: Append Record with Next Consecutive Number (not Autonumber)