Query

[ladybug via AccessMonster.com]

I have a table with two columns. First column is Date. Second column is
Employee. I want a query to return only the employees that have 6 or more
dates (or entries) and their corresponding dates. How do I do this?

 

[John Spencer]

SELECT Employee, [Date]
FROM YourTable
WHERE Employee in
(SELECT Employee
FROM YourTable
GROUP BY Employee
HAVING Count(*) > 6)

You can do this is two queries.

Create and save a query called MoreThan5
-- Open New query
-- Select your table
-- Drag the employee field to the table twice
-- Select View: Totals from the menu
-- Change the SECOND group by to Count
-- Enter > 5 in the where under Count

Open a new query
-- Select your table and the saved query.
-- drag from employee to employee
-- Select the fields you want to display from your table
-- run the query.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[ladybug via AccessMonster.com]

That works perfect. I was wondering if I could go one step further. I
really only want the query to retrieve the entries if there are 6 or more
entries within 6 months. Do you know how to do that?

SELECT Employee, [Date]
FROM YourTable
WHERE Employee in
(SELECT Employee
FROM YourTable
GROUP BY Employee
HAVING Count(*) > 6)

You can do this is two queries.

Create and save a query called MoreThan5
-- Open New query
-- Select your table
-- Drag the employee field to the table twice
-- Select View: Totals from the menu
-- Change the SECOND group by to Count
-- Enter > 5 in the where under Count

Open a new query
-- Select your table and the saved query.
-- drag from employee to employee
-- Select the fields you want to display from your table
-- run the query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a table with two columns. First column is Date. Second column is
Employee. I want a query to return only the employees that have 6 or more
dates (or entries) and their corresponding dates. How do I do this?

 

[John Spencer]

The following might work. I have no way to test this without building the
table and populating it with data.

SELECT T2.Employee, T2.Date
FROM YourTable as T2
WHERE DateAdd("m",-6,T2.Date) >=
(SELECT Min(T3.Date)
FROM
(SELECT TOP 5 T1.Date
FROM YourTable as T1
WHERE T1.Employee = T2.Employee
AND T1.Date < T2.Date
ORDER BY T1.Date ) as T3)

I know there is a better way, I'm just having an off day on seeing my way to
a valid solution.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

It would be the second one...of course. Thank you for your time!

Do you mean in a defined 6-month period (any employee with 6 dates between
July 1, 2006 and December 31, 2006) or
do you mean any employee with any period of 6 months that had 6 or more
visits within a running six month time frame?

The first is fairly simple, you just modify the subquery to use the
specified date range.
SELECT Employee, [Date]
FROM YourTable
WHERE Employee in
(SELECT Employee
FROM YourTable
WHERE [Date] Between #2006/07/01# and #2006/12/31#
GROUP BY Employee
HAVING Count(*) > 6)

The second is quite bit more complex to implement. Perhaps someone has an
example available if that is what you want.

That works perfect. I was wondering if I could go one step further. I
really only want the query to retrieve the entries if there are 6 or
more

[quoted text clipped - 35 lines]

more
dates (or entries) and their corresponding dates. How do I do this?