Filtered data, etc.

[James Kennedy]

I have used Access for a number of years, although mostly just basic
functions, and now have the new 2007 version. My question stripped down:

I have a table with a few thousand lines of data, the important fields being
1) the SSN field, which will have multiple lines of the same SSN, so no
unique identifier; 2) the DATE field, which may have a two dates per
month--the 15th and the 31st; and the HOURS worked field. I need to find out
how many peope (SSNs) have 70 hours or more PER MONTH. The problem is that
the dates are not monthly but twice a month. Anybody have any ideas on how
to go about this?

Thanks in advance for your advice.

 

[Jerry Whittle]

SELECT SSN,
Year([DATE]) AS TheYEARS,
Month([DATE]) AS TheMONTHS,
Sum([HOURS]) AS TheHOURS
FROM YourTableName
HAVING Sum([HOURS]) >= 70
GROUP BY SSN, Year([DATE]), Month([DATE]) ;

 

[James Kennedy]

Thanks for the previous help. One further question: I now have a list of
people who have an amount greater than 70 per month. I may have multiple
names with different months. I need to find out if there are any people who
have this criteria for 5 or more months--they don't need to be consecutive
months.

For instance, John Doe has for Jan 72.38, Mar 83.00, Jun 120.30, Jul 93.01,
and Sep 122.01--qualifies, has 5 months; Jane Person has Mar 74.22, Apr
88.27, and Jun 99.09--doesn't qualify, only 3 months.

Thanks in advance,

 

[John Spencer]

Use the first query as the source for a second query

SELECT SSN, Count(SSN) as CountTimes
FROM TheSavedQuery
GROUP BY SSN
HAVING COUNT(SSN) >4

In theory you could do this all in one query as long as your field and
tablenames followed the naming rules. (no spaces in the names, no reserved
words, only characters that are A to Z, o to 9, or the underscore
character). Date is a reserved word, but you may be able to avoid any
problem by appending the table name to the field name when you refer to it.
So the following MIGHT work.


SELECT SSN, Count(SSN) as CountTimes
FROM (
SELECT SSN,
Year(YourTableName.DATE) AS TheYEARS,
Month(YourTableName.DATE) AS TheMONTHS,
Sum(HOURS) AS TheHOURS
FROM YourTableName
HAVING Sum(HOURS) >= 70
GROUP BY SSN, Year(YourTableName.DATE), Month(YourTableName.DATE) ;
) as TMP
GROUP BY SSN
HAVING COUNT(SSN) >4


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

 

[James Kennedy]

Thank you. I'll try this out now.

Use the first query as the source for a second query

SELECT SSN, Count(SSN) as CountTimes
FROM TheSavedQuery
GROUP BY SSN
HAVING COUNT(SSN) >4

In theory you could do this all in one query as long as your field and
tablenames followed the naming rules. (no spaces in the names, no reserved
words, only characters that are A to Z, o to 9, or the underscore
character). Date is a reserved word, but you may be able to avoid any
problem by appending the table name to the field name when you refer to it.
So the following MIGHT work.


SELECT SSN, Count(SSN) as CountTimes
FROM (
SELECT SSN,
Year(YourTableName.DATE) AS TheYEARS,
Month(YourTableName.DATE) AS TheMONTHS,
Sum(HOURS) AS TheHOURS
FROM YourTableName
HAVING Sum(HOURS) >= 70
GROUP BY SSN, Year(YourTableName.DATE), Month(YourTableName.DATE) ;
) as TMP
GROUP BY SSN
HAVING COUNT(SSN) >4


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

 

[James Kennedy]

Thanks. That first part worked fine. Another question: If I wanted to find
out how many people had, say, 5 or more months in consecutive order, how
would I go about that?

Thanks in advance,