Puzzling over a query - possilbe subquery solution

[Indigo]

Hello all! I have been puzzling over this for a day and a half and am getting nowhere fast. I am using Access 2003 and have a table with the following fields:

ID (autonumber)
InputDate (date/time)
Group (text)
TotalAudited (number)
Unsafe (number)

Now I want to count (not sum) the records and create a stacked column chart in a report.

I can create a query which counts the total audits:

SELECT SafetyGLDrivingAudittbl.Group, 
Count(SafetyGLDrivingAudittbl.SGDAID) AS [AllAudits]
FROM SafetyGLDrivingAudittbl 
WHERE (((SafetyGLDrivingAudittbl.InputDate) Between [Forms]![ReportFrm]![TxtStartDate] And [Forms]![ReportFrm]![TxtEndDate]));

And I get:

Group All Audits
Group 1 5
Group 2 4
Group 3 6
Group 4 3

And I can create a query that counts the audits with unsafe activities:

SELECT SafetyGLDrivingAudittbl.Group, 
Count(SafetyGLDrivingAudittbl.SGDAID) AS Unsafe
FROM SafetyGLDrivingAudittbl 
WHERE (((SafetyGLDrivingAudittbl.TotalUnsafe)<>0) 
AND ((SafetyGLDrivingAudittbl.InputDate) Between [Forms]![ReportFrm]![TxtStartDate] And [Forms]![ReportFrm]![TxtEndDate]));

and I get:

Group Unsafe
Group 1 1
Group 3 2

But I want:

Group All Audits Unsafe
Group 1 5 1
Group 2 4 0
Group 3 6 2
Group 4 3 0

Can someone help me pull this together?

 

[plog]

You don't need a sub-query, just a conditional field in your query. Since there's a conflict between the names in the table structure you posted and the SQL you posted, ('TotalUnsafe' in the second query SQL you posted and the table example you called it 'Unsafe'), I will use the one you listed in the table example--'Unsafe' in my example:

SELECT SafetyGLDrivingAudittbl.Group, 
Count(SafetyGLDrivingAudittbl.SGDAID) AS [AllAudits], SUM(Iif(SafetyGLDrivingAudittbl.Unsafe<>0, 1,0) AS TotalUnsafe
FROM SafetyGLDrivingAudittbl 
WHERE (((SafetyGLDrivingAudittbl.InputDate) Between [Forms]![ReportFrm]![TxtStartDate] And [Forms]![ReportFrm]![TxtEndDate]));

 

[Indigo]

Thank you for your quick response.... but I am getting an error:

SELECT SafetyGLDrivingAudittbl.Group, 
Count(SafetyGLDrivingAudittbl.SGDAID) AS AllAudits, 
Sum(IIf([SafetyGLDrivingAudittbl].[Unsafe]<>0,1,0)) AS TotalUnsafe
FROM SafetyGLDrivingAudittbl
GROUP BY SafetyGLDrivingAudittbl.Group, SafetyGLDrivingAudittbl.InputDate
HAVING (((SafetyGLDrivingAudittbl.InputDate) Between [Forms]![ReportFrm]![TxtStartDate] And [Forms]![ReportFrm]![TxtEndDate]));

But I am getting a parameter pop up for the "TotalUnsafe" field?

 

[Indigo]

oops.... I see it.... nvm

SELECT SafetyGLDrivingAudittbl.Group, 
Count(SafetyGLDrivingAudittbl.SGDAID) AS AllAudits, 
Sum(IIf([SafetyGLDrivingAudittbl].[TotalUnsafe]<>0,1,0)) AS TallyUnsafe
FROM SafetyGLDrivingAudittbl
WHERE (((SafetyGLDrivingAudittbl.InputDate) 
Between [Forms]![ReportFrm]![TxtStartDate] And [Forms]![ReportFrm]![TxtEndDate]))
GROUP BY SafetyGLDrivingAudittbl.Group;

Thank you so so much! I didn't think of IIf!

 

[vbaInet]

I would have gone for a Crosstab query just to keep things dynamic. Worth looking into for the future.