check box count wiht total records noted 1

[Ali29J]

hi all

i have a query using the following

SampleRequired - YES/NO
POReceived - YES/NO

My query using the following settings:

SampleRequired - Total Where - criteria True
POReceived - Total Count
POReceived - Total Where - criteria True

It returns number of PO received for every sample sent out, but what i really need to incorporate is the total number of sample requests which dont have, regardless of PO received or not.

Can anyone help me how i can display this also without it counting only POreceived.

Ultimately the data will be exported to excel to create pie chart using a macro.

please help

Ali

 

[rac2]

The key to this kind of problem is to write expressions using the logical function IIf( ). An expression meaning If SampleRequired is True then the value is 1, otherwise the value is 0. Then SUM these expressions instead of COUNTing rows. For the total you can use COUNT.

Assuming the columns are Boolean datatypes you could do this -

SELECT SUM( IIf( SampleRequired, 1, 0) AS "NumberOfSamples",
            SUM( IIf( POReceived, 1, 0) AS "NumberOfPurchaseOrdersReceived",
            COUNT(*) AS "TotalExpectedOrders"
FROM MyTable

 

[topwaya]

Thanks for this valuable resource!

I tried the above in a query and I'm getting a syntax error for a missing operator. It highlights the first "AS" after the error. My query is:

SELECT SUM( IIf( tblScr.BP, 1, 0) AS "TotalBP",
             SUM( IIf( tblScr.Mam, 1, 0) AS "TotalMam",
             SUM( IIf( tblScr.Osteo, 1, 0) AS "TotalOsteo",
             SUM( IIf( tblScr.Gluc, 1, 0) AS "TotalGluc",
             SUM( IIf( tblScr.PSA, 1, 0) AS "TotalPSA",
             SUM( IIf( tblScr.DRE, 1, 0) AS "TotalDRE",
             SUM( IIf( tblScr.Colo, 1, 0) AS "TotalColo",
             SUM( IIf( tblScr.Choles, 1, 0) AS "TotalCholes",
             SUM( IIf( tblScr.Trig, 1, 0) AS "TotalTrig",
             SUM( IIf( tblScr.HepC, 1, 0) AS "TotaHepC",
             SUM( IIf( tblScr.HRA, 1, 0) AS "TotalHRA",
             COUNT(*) AS "TotalScreens"
FROM tblScr
GROUP BY tblScr.ScrLoc
HAVING (((tblScr.ScrD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]);

All of the SUM fields are YES/NO checkbox fields.
Thank you for any help!

 

[PHV]

SELECT Sum(IIf(BP, 1, 0)[highlight])[/highlight] AS TotalBP,

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

 

[topwaya]

Awesome! Worked like a charm - thank you sooo much!!!!