SQL: Counting Totals from Different Entries in 1 Field

[kmkland]

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;

Now, what I would like to accomplish is this:

1. For each EmpFile#, I need to count the number of times the Code "21" occurs.

2. If Code 21 occurs 4 times, then I want a MsgBox to generate. The same for 5, 6, and 7 times.

3. The same for Codes 22 and 23.

I will have this statement (or script, if someone takes on the dirty task of writing one for me, as I am a VB Learner) attached to a command button (btnCloseForm) on frmEmpInfo.

Any thoughts....suggestions????

Any and all help is, of course, always appreciated!!!

Kind Regards,
KMK

 

[PHV]

A starting point:
SELECT E.[EmpFile#], E.LName, A.Code, Count(*) AS CountOfCode
FROM tblEmpInfo AS E INNER JOIN tblAbsences AS 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, A.Code
HAVING Count(*) Between 4 And 7;


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

 

[kmkland]

PH,
Hmmm...it says "Data type mismatch in criteria expression."

KMK

 

[PHV]

If Code isn't defined as numeric in tblAbsences :
SELECT E.[EmpFile#], E.LName, A.Code, Count(*) AS CountOfCode
FROM tblEmpInfo AS E INNER JOIN tblAbsences AS 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, A.Code
HAVING Count(*) Between 4 And 7;

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

 

[kmkland]

PH,

I looked at the requirements that I had typed earlier in this thread, and I explained it wrong...I do apologise.

This is 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.

(I think I was in a bit of a rush yesterday when I posted my thread! I apologise for that.)

Thanks so much.

KMK

 

[kmkland]

Does anyone have any thoughts on what I should change??

All help is appreciated!!!

Kmkland