SQL Problem.... 1

[kmkland]

PLEASE...any help will be appreciated on this matter!!!! This is a repost.

I have a complicated problem...
I am trying to write an SQL statement, but I do not know how to establish the criteria for my statement...
Here is the basic information to start with:

SELECT tblEmpInfo.[EmpFile#], tblEmpInfo.LName, tblAbsences.Date_of_Absence, tblAbsences.Code
FROM tblEmpInfo INNER JOIN tblAbsences ON tblEmpInfo.[EmpFile#] = tblAbsences.[EmpFile#]
WHERE (((tblAbsences.Date_of_Absence)>=Date()-365))
ORDER BY tblAbsences.Date_of_Absence;

What I need to accomplish is this:

1. For each EmpFile#, I need to count the number of times the following Codes occur: 21, 22, and 23. (I.e. if EmpFile# has Code 21 3 times and Code 22 2 times, I need for it to show a grand total of 5 times.)

2. If any combination of the 3 Codes occur 4-7 times, then I want a MsgBox to generate.

The criteria for the SQL statement is what's doing my head in...I'm at a loss as to how I need to write it.

Thanks so much in advance for any assistance given...
Kmkland

 

[PHV]

1.
SELECT E.[EmpFile#], E.LName, Count(*) AS CountOf21_22_23
FROM tblEmpInfo E INNER JOIN tblAbsences A ON E.[EmpFile#] = A.[EmpFile#]
WHERE A.Date_of_Absence>=Date()-365 AND A.Code In ('21','22','23')
GROUP BY E.[EmpFile#], E.LName:

2.
You can't have a MsgBox in a query ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Pigster14]

You might need different queries.

But, for question 1, you want only codes equal to 21, 22 and 23 and grouping by empfile# you want the count for that empfile# for each code.

If that is correct you would do the following:
SELECT tblEmpInfo.[EmpFile#], COUNT(tblAbsences.Code)
FROM tblEmpInfo INNER JOIN tblAbsences ON tblEmpInfo.[EmpFile#] = tblAbsences.[EmpFile#]
Group by tblEmpInfo.EmpFile#, tblAbsences.Code
WHERE (((tblAbsences.Date_of_Absence)>=Date()-365)) and tblAbsences.Code = 21 or tblAbsences.Code = 22 ortblAbsences.Code = 23
ORDER BY tblAbsences.Date_of_Absence;

This would show EmpFile#1 has 2 code 21's and 3 code 22's and 4 code 23's.

If you want to see that EmpFile# has a total of 9 code 21's, 22's or 23's then you would change the Group by statement to just be grouped by tblEmpInfo.EmpFile#.

For your second question you would do the same thing, but have a HAVING clause look for a count greater than your criteria. Then if you have results in that query, then you could have a message box flag stating that someone has had that criteria occur.

I hope this helps. Good Luck!

 

[kmkland]

Thank you both for your help!

PH,
Your statement worked quite well!

Thanks again!!!

Kmkland...

 

[kmkland]

I have more criteria to factor into my statement, but I am not sure how to approach this one, as it is a bit tricky.

I still need to query for the 4-7 occurrences of codes 21, 22, and/or 23 but only if the field ActionTaken is null at any time within those 4-7 occurrences.