Dcount function?

[lefty1324]

I have the following statement:

SELECT States.st, Licenses.[Expiration Date], IIf([Expiration Date]<Date()," Not Compliant","Compliant") AS Compliant
FROM States INNER JOIN Licenses ON States.st_ID = Licenses.st_ID;


What I need to do now is count the number of compliant returns with-in each state. Finally I need to see that if there is at least 1 compliant return in each state, said state would be expressed as compliant.

Can this be done? Should this be a dcount function?

 

[Galaxiom]

Change your query to output True and False instead of the strings for Compliant.

Create another query based on your first one. Include only State and Compliant fields. Group by State and Sum Compliant. True is -1 while False is zero so the Sum will give the negative of the total for each State which you can easy reverse.

If you don't want to use True/False then include the Compliant field twice. Group By one instance and Count the other. This will give you the count of each compliant and non-compliant for each state.

 

[plog]

This SQL will do it all at once:

SELECT States.st, Min(IIf([Expiration Date]<Date(),"Not ") & "Compliant") AS Compliant, Sum(IIf([Expiration Date]<Date(),0,1)) AS CompliantTotal
FROM States INNER JOIN Licenses ON States.st_ID = Licenses.st_ID
GROUP BY States.st;

 

[lefty1324]

Perfect thanks for the help! works great!