Get a grand total from SQL Query within SQL Query 2

[netrusher]

Below is the sql I have for a query. This works good. What
I would like to add is another Column for Grand Total that
would equal the total of each of the items I am querying
in the query. How can I accomplish this?

SELECT Sum(IIf(([ObserversSecondTbl].[PPEEyeProSafe]=True),1,0)) AS Eye, Sum(IIf(([ObserversSecondTbl].[PPEFaceProSafe]=True),1,0)) AS Face, Sum(IIf(([ObserversSecondTbl].[PPEHeadProSafe]=True),1,0)) AS Head, Sum(IIf(([ObserversSecondTbl].[PPEEarProSafe]=True),1,0)) AS Ear, Sum(IIf(([ObserversSecondTbl].[PPERespiratoryProSafe]=True),1,0)) AS Respiratory, Sum(IIf(([ObserversSecondTbl].[PPEHandProSafe]=True),1,0)) AS Hand, Sum(IIf(([ObserversSecondTbl].[PPEBodyProSafe]=True),1,0)) AS Body, Sum(IIf(([ObserversSecondTbl].[PPEFootProSafe]=True),1,0)) AS Foot
FROM ObserversMainResultsTbl INNER JOIN ObserversSecondTbl ON ObserversMainResultsTbl.FormNbr = ObserversSecondTbl.FormNbr
WHERE (((ObserversMainResultsTbl.Date) Between [Forms]![LiftsQueriesReports]![StartDateTxt] And [Forms]![LiftsQueriesReports]![EndDateTxt]));

 

[PHV]

What about this ?
SELECT Abs(Sum(S.PPEEyeProSafe)) AS Eye
, Abs(Sum(S.PPEFaceProSafe)) AS Face
, Abs(Sum(S.PPEHeadProSafe)) AS Head
, Abs(Sum(S.PPEEarProSafe)) AS Ear
, Abs(Sum(S.PPERespiratoryProSafe)) AS Respiratory
, Abs(Sum(S.PPEHandProSafe)) AS Hand
, Abs(Sum(S.PPEBodyProSafe)) AS Body
, Abs(Sum(S.PPEFootProSafe)) AS Foot
, Abs(Sum(S.PPEEyeProSafe+S.PPEFaceProSafe+S.PPEHeadProSafe+S.PPEEarProSafe+S.PPERespiratoryProSafe+S.PPEHandProSafe+S.PPEBodyProSafe+S.PPEFootProSafe)) AS [Grand Total]
FROM ObserversMainResultsTbl AS M
INNER JOIN ObserversSecondTbl AS S ON M.FormNbr = S.FormNbr
WHERE M.Date Between [Forms]![LiftsQueriesReports]![StartDateTxt] And [Forms]![LiftsQueriesReports]![EndDateTxt]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

netrusher,
Do you understand your table structure isn't normalized? You are storing values in your field names ie: head, face, ear,... If you want to add another check to your ppe observation table, you shouldn't have to add fields to tables, queries, and expressions as well as controls to forms and reports.

A normalized table structure would allow you to use SQL to calculate a grand total.

If your structure works and [red]won't ever change[/red] then you might want to keep it. I would however recommend reading up on normalizing and rebuild your tables.

Duane
Hook'D on Access
MS Access MVP

 

[netrusher]

Thanks Duane, This is one of those DB that I inherited.

 

[netrusher]

PH,

That is great. Thanks for the help.