sum of fields by location 1

[topwaya]

I have the following query:

SELECT tblScr.ScrLoc, Sum(IIf
(tblScr.BP,1,0)) AS ["TBP"], Sum(IIf
(tblScr.Mam,1,0)) AS ["TMam"], Sum(IIf
(tblScr.Osteo,1,0)) AS ["TOsteo"], Sum(IIf(tblScr.PSA,1,0)) AS ["TPSA"], Sum(IIf
(tblScr.Colo,1,0)) AS ["TColo"], Sum(IIf(tblScr.Choles,1,0)) AS ["TCholes"], Sum(IIf(tblScr.Trig,1,0)) AS ["TotalTrig"], Sum(IIf(tblScr.HepC,1,0)) AS ["TotaHepC"], Count(*) AS ["TScreens"]
FROM tblScr
GROUP BY tblScr.ScrLoc, tblScr.ScrD
HAVING (((tblScr.ScrD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]));

It displays the following:

Loc TBP TMam TOsteo TPSA TColo TCholes TTrig THepC TScreens
BTW 1 0 0 0 0 0 0 0 1
BTW 0 0 0 0 2 0 2 1 2
FM 1 0 0 0 0 0 0 0 1
P 1 0 0 1 0 0 0 0 2
SP 2 2 1 0 0 0 0 0 4
SP 0 0 0 0 0 5 0 5 5


I would like it to display:

Loc TBP TMam TOsteo TPSA TColo TCholes TTrig THepC TScreens
BTW 1 0 0 0 2 0 2 1 6
FM 1 0 0 0 0 0 0 0 1
P 1 0 0 1 0 0 0 0 2
SP 2 2 1 0 0 5 0 5 20

So that each location only has one row, and each row is totaled at the end under TScreens.

Any help as to what I am doing wrong is appreciated!!
Thank you!!!

 

[PHV]

FROM tblScr
WHERE (((tblScr.ScrD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]))
GROUP BY tblScr.ScrLoc, tblScr.ScrD;

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

 

[PHV]

Oops, hit submit too fast:
FROM tblScr
WHERE (((tblScr.ScrD) Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]))
GROUP BY tblScr.ScrLoc;

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

 

[topwaya]

You are the best! You make all this seem so easy - I wish it was easy!! I will have to read up on the difference between WHERE and HAVING.

Thank you soo much!!!!!!!!

 

[PHV]

HAVING should be used only when you want to test the result of some aggregate function.

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

 

[topwaya]

Thanks, I'll have to remember that. Unfortunately, I clicked too soon

It displays only one row for each location, but the TotalScreens "TScreens" is not correct.

Here is what displays:

Loc TBP TMam TOsteo TPSA TColo TCholes TTrig THepC TScreens
BTW 1 0 0 0 2 0 2 1 3
FM 1 0 0 0 0 0 0 0 1
P 1 0 0 1 0 0 0 0 2
SP 2 2 1 0 0 5 0 5 9


Thanks for all your help!!

 

[PHV]

Seems that some records have several fields set to true, thus TScreens is the number of records, not the number of True values.
SELECT ScrLoc, Abs(Sum(BP)) TBP, Abs(Sum(Mam)) AS TMam, Abs(Sum(Osteo)) AS TOsteo, Abs(Sum(PSA)) AS TPSA
, Abs(Sum(Colo)) AS TColo, Abs(Sum(Choles)) AS TCholes, Abs(Sum(Trig)) AS TTrig, Abs(Sum(HepC)) AS THepC
, Abs(Sum(BP+Mam+Osteo+PSA+Colo+Choles+Trig+HepC)) AS TScreens
FROM tblScr
WHERE ScrD Between [Forms]![fdlgOps]![txtStart] And [Forms]![fdlgOps]![txtEnd]
GROUP BY ScrLoc;

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

 

[topwaya]

It works. I don't understand why it works, but it works.

You are brilliant