Count a calculated field

[Opal]

I'm not sure how to achieve this in Access 2003...

I have the following query showing when the
next time an employee needs to be certified:

SELECT EMPInfo.EMPNumber,
[LastName] & "," & [FirstName] AS Name,
EMPInfo.SUPLink, EMPInfo.CertDate,
IIf([CertDate] Is Null,Date(),[CertDate]+1095) AS NextDue,
IIf([NextDue]<=Date()-1,"Re-Certification Past Due",
IIf([NextDue]<=Date()+30,"Re-Certification Due","")) AS Flag
FROM EMPInfo
WHERE (((TMInfo.SUPLink)=
[Forms]![HoldingInfo]![txtHoldSUPNumber]));

I want to be able to count the instances where
Re-Certification is Due and where Re-Certification is Past
Due so I am attempting to base another
query on this query - qryEMPCert but I am
getting errors - "Your tried to execute a query that does not
include the specified expression 'Not qryEMPCert.Flag Is Null'
as part of an aggregate function."

How can I count in this query?

 

[KARL DEWEY]

Try this --
SELECT Count([qryEMPCert].[EMPNumber]) AS [Un-certified]
FROM qryEMPCert
WHERE [qryEMPCert].[Flag] Is Not Null;

 

[Opal]

Hi Karl,

This counts the total records which for
this example is 28. There should be 27
that are not flagged and one that is ....
I need the query to return 1....it returned
28.

 

[KARL DEWEY]

NextDue may be giving a problem as aliases should not be used for calculation
in the same query that generates them.
Try this --
SELECT Sum(IIf([CertDate] Is Null,Date(),[CertDate]+1095) <=Date()-1, 1, 0)
AS [Un-certified]
FROM EMPInfo;

Your structure requies writing over old data so you do not have historical
data. A better way would be to create a record for each certification. You
would need to use Max([CertDate) in the query.

 

[Opal]

Karl,

Since the only data I need to maintain is the last
certified date, I don't need to maintain historical
data.

As for your suggestion, I am trying to work it
out as I am getting an error pertaining to the
wrong number of arguments......

 

[Opal]

I thought a multiple IIf would work:

SELECT Sum(IIf([CertDate] Is Null,1,IIf([CertDate]+1095<=Date(),
1,0)))
AS [Un-certified]
FROM EMPInfo;

But, I get 53 as my result.... :-S

 

[Opal]

NVM

I got it!

SELECT Sum(IIf([CertDate] Is Null,1,IIf([CertDate]+1095<=Date(),
1,0)))
AS [Un-certified]
FROM EMPInfo
GROUP BY EMPInfo.SUPLink
HAVING (((EMPInfo.SUPLink)=[Forms]![HoldingInfo]![txtHoldSUPNumber]));

Thanks for giving me the direction to think this one through!