Joining queries together?

[fat controller]

I currently have a sub-report that counts the number of records between two specified dates, and sorts them into order based on a field called 'Route' however I am looking to refine the data a little further based on another field 'Fault'

Essentially, I am looking to have a sub report that shows:

- Count (total) of records between specified dates, sorted into order on field [Route]

- Count of those on each route where the value of the field [Fault] is:
= "Yes"
= "No"
= "N/A"

If possible, I would like to have all four counts show side by side on the one sub report.

I have got four separate queries at the moment, each of which will give the relevant count for each parameter, however is there a way to join them all together?

 

[arnelgp]

on aggregate query, used as Expression

select count(iif([yourfield]="yes",[yourfield],Null)) as CountYes, count(iif([yourField]="No",[yourfield],Null)) As CountNo, Count(iif([yourfield]="N/A",[yourfield],Null)) As CountNA

 

[plog]

I don't believe that expression will work. You either need to change those Count() to Sum(), or remove the False condition from the IIf.

SELECT COUNT(0) FROM YourTable

Will return the number of records in YourTable.

 

[fat controller]

Sorry guys, I am being really thick here, but where would I put the expression?

If it helps, here is the SQL for each of the three queries:

SELECT PublicComplaints.Route, Count(PublicComplaints.Route) AS “NumberOfComplaints”
FROM PublicComplaints
WHERE (((PublicComplaints.DateProcessed) Between [Forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![txtEndDate]) AND ((PublicComplaints.Fault)="Yes"))
GROUP BY PublicComplaints.Route
HAVING (((Count(PublicComplaints.Route))>0));

SELECT PublicComplaints.Route, Count(PublicComplaints.Route) AS “NumberOfComplaints”
FROM PublicComplaints
WHERE (((PublicComplaints.DateProcessed) Between [Forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![txtEndDate]) AND ((PublicComplaints.Fault)="No"))
GROUP BY PublicComplaints.Route
HAVING (((Count(PublicComplaints.Route))>0));

SELECT PublicComplaints.Route, Count(PublicComplaints.Route) AS “NumberOfComplaints”
FROM PublicComplaints
WHERE (((PublicComplaints.DateProcessed) Between [Forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![txtEndDate]) AND ((PublicComplaints.Fault)="N/A"))
GROUP BY PublicComplaints.Route
HAVING (((Count(PublicComplaints.Route))>0));

I have attached a screenshot of the sort of output I am looking to achieve - the first two columns are already provided by an existing query (which I can provide the SQL for if it helps), and I am looking to fill the next three columns using the three queries above?

 

[MarlaC]

If you can post the SQL that provides the recordsource for your subreport, we can modify it to contain the fields you need to add. You need fields in the query, not additional queries.

 

[fat controller]

This is the code of the subform which simply shows the total number of complaints (first and second column on the screenshot):

SELECT PublicComplaints.Route, Count(PublicComplaints.Route) AS “NumberOfComplaints”
FROM PublicComplaints
WHERE (((PublicComplaints.DateProcessed) Between [Forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![txtEndDate]))
GROUP BY PublicComplaints.Route
HAVING (((Count(PublicComplaints.Route))>0));

 

[plog]

What you do is remove the Fault criteria from the WHERE clauses and put them in the SELECT clause. So instead of 3 queries, it becomes one. You move the Fault criteria using an IIF statement like so:

ValidComplaints: SUM(Iif(Fault="Yes", 1,0))

You do that for each of your 3 types of Fault values (above I assumed Yes made it a valid complaint.

 

[MarlaC]

Change your subform source from this

SELECT PublicComplaints.Route, Count(PublicComplaints.Route) AS “NumberOfComplaints”
FROM PublicComplaints
WHERE (((PublicComplaints.DateProcessed) Between [Forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![txtEndDate]))
GROUP BY PublicComplaints.Route
HAVING (((Count(PublicComplaints.Route))>0));

to this

SELECT PublicComplaints.Route, Count(PublicComplaints.Route) AS “NumberOfComplaints”, SUM(Iif(Fault="Yes", 1,0)) AS ComplaintsYes, SUM(Iif(Fault="No", 1,0)) AS ComplaintsNo, SUM(Iif(Fault="N/A", 1,0)) AS ComplaintsNA
FROM PublicComplaints
WHERE (((PublicComplaints.DateProcessed) Between [Forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![txtEndDate]))
GROUP BY PublicComplaints.Route
HAVING (((Count(PublicComplaints.Route))>0));

You can change the field names from ComplaintsYes, etc., to whatever you like (avoiding spaces and reserved names, etc.), then place controls on the form bound to those fields in the query.

 

[fat controller]

Fantastic! Thank you!!

Works like a charm, and now that I see it, I have absolutely no idea what made me start faffing around with more queries.