If Any of the Results are "Null", then?????

[kmkland]

The following is my statement:

SELECT E.[EmpFile#], E!FName & ' ' & E!MName & ' ' & E!LName AS [Employee Name], E.DeptCode, E.Supervisor, Count(*) AS CountOf21_22_23
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!FName & ' ' & E!MName & ' ' & E!LName, E.DeptCode, E.Supervisor
HAVING (((Count(*)) Between 4 And 7));

I 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.

I have tried multiple approaches, but the only results I received were those who had no empty fields in any of the 4-7 occurrences or received no results at all!

I've received a considerable amount of help writing this statement and am grateful. So, I welcome any additional suggestions!!

Thanks in advance!

Rgds,
Kmkland

 

[PHV]

Perhaps this ?
HAVING Count(*) Between 4 And 7
AND Count(ActionTaken)<Count(*);

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

 

[kmkland]

Brilliant!

Thanks, PH!!

I have another question for you....

I am trying to create a crosstab query from the following query. This query works just fine:

SELECT qryCountAbsJoin.Date_of_Absence, tblReasonsForAbsences.Code, Count(qryCountAbsJoin.[EmpFile#]) AS [CountOfEmpFile#]
FROM tblReasonsForAbsences LEFT JOIN qryCountAbsJoin ON tblReasonsForAbsences.Code = qryCountAbsJoin.Code
WHERE (((qryCountAbsJoin.Date_of_Absence) Is Null Or (qryCountAbsJoin.Date_of_Absence) Between [Forms]![frmParameters]![StartDate] And [Forms]![frmParameters]![EndDate]))
GROUP BY qryCountAbsJoin.Date_of_Absence, tblReasonsForAbsences.Code;

I need all of the codes to list, whether there is an entry or not, but when I create the crosstab query, this is the error message I get:

The MS Jet DB engine does not recognise '[Forms]![frmParameters]![StartDate]' as a valid field name or expression.

Any suggestions?

Thanks in advance!!!

Rgds,
kmkland

 

[PHV]

Add the following line on the top of the SQL code of the crosstab query:
PARAMETERS [Forms]![frmParameters]![StartDate] DateTime, [Forms]![frmParameters]![EndDate] DateTime;

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,

This works, but it puts a count under the codes which should be empty. So, it's counting the total of the null values...

Rgds,
Kmkland

 

[PHV]

Replace LEFT JOIN by INNER JOIN and get rid of the test about null dates.

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,
When I did that, not all of the codes were listed, and I need all of the codes to list for my report...
Thanks so much for your help!!!
Kmkland

 

[PHV]

You said that Count(qryCountAbsJoin.[EmpFile#]) is not 0 (zero) when qryCountAbsJoin.Date_of_Absence is null ?

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 changed the 'Count' to 'Sum', and that solved the problem of having a count in a null-value field...

HOWEVER!!!....I do not want the zero's to appear in the field if it is a null value....

Rgds,
Kmkland

 

[kmkland]

PH,

Right. The count for the codes that were null was '1' instead of '0'....

Kmkland

 

[PHV]

I do not want the zero's to appear
It's a format issue.

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,

When I change the format, do I go into my crosstab query to make the necessary changes, or do i make the changes in the query upon which the crosstab was based?

I have tried changing the format in the query, not crosstab, but I just can't quite get it to work. I've used various formats, but it's just not working for me. (This was in the field "CountOfEmpFile#", since that was the value for the crosstab.)

What format should I use?

Thanks so much PH!!!!

Rgds,
Kmkland

 

[kmkland]

PH,

I figured it out shortly after posting my last reply!! But the row which had the zero values still shows up in my results!!! How is it doing that when there are no values a'tall in it!?!?!?!

Rgds,
Kmkland