Total row in query (1 Viewer)

john_c

Member
Local time
Today, 20:20
Joined
Jun 9, 2020
Messages
38
Hi everyone, I have a query which counts the number of complaints my company receives about each of the products it produces and the number of these complaints which the qc department deemed valid. I have a bar chart in a report which displays this data by product. After some fiddling it works ok. However I would like to add a Total bar to the chart to show the total number of complaints and total number of valid complaints. If I could add a Total row to the query it would do it automatically. Is this possible? If not is there any convenient work around? Many thanks
 

Minty

AWF VIP
Local time
Today, 20:20
Joined
Jul 26, 2013
Messages
10,355
Access Queries can display a total, it's one of the option in the menu in the datasheet view;

1597407677772.png


Not sure if it will magically appear in your chart though?
 

john_c

Member
Local time
Today, 20:20
Joined
Jun 9, 2020
Messages
38
Thanks Minty. Yes it doesn't appear on the chart. Can you think of any way to add it to the chart?
 

Minty

AWF VIP
Local time
Today, 20:20
Joined
Jul 26, 2013
Messages
10,355
Can you show us the SQL of your existing query?
 

john_c

Member
Local time
Today, 20:20
Joined
Jun 9, 2020
Messages
38
SELECT tblComplaints.ComplaintType, tblComplaints.Product, Count(tblComplaints.AccessComplaintID) AS CountOfAccessComplaintID, Sum(Abs([ComplaintValid])) AS Expr1, tblComplaints.DateReported
FROM tblComplaints
GROUP BY tblComplaints.ComplaintType, tblComplaints.Product, tblComplaints.DateReported
HAVING (((tblComplaints.ComplaintType)="Product Quality") AND ((tblComplaints.DateReported)>=[Forms]![frmReports]![txtFromDate] And (tblComplaints.DateReported)<=[Forms]![frmReports]![txtToDate]));
 

sxschech

Registered User.
Local time
Today, 13:20
Joined
Mar 2, 2010
Messages
791
If you want to show an actual additional bar with a Totals Row (TR) you might need to do a union query. I put zTotal rather than total so it would sort last. If you don't have any words that would sort after Total, then you could remove the z. You probably will need to play around with this since I didn't have actual data to test but this will give you a starting point.

Code:
SELECT tblComplaints.ComplaintType, tblComplaints.Product, Count(tblComplaints.AccessComplaintID) AS CountOfAccessComplaintID, Sum(Abs([ComplaintValid])) AS Expr1, tblComplaints.DateReported
FROM tblComplaints
GROUP BY tblComplaints.ComplaintType, tblComplaints.Product, tblComplaints.DateReported
HAVING (((tblComplaints.ComplaintType)="Product Quality") AND ((tblComplaints.DateReported)>=[Forms]![frmReports]![txtFromDate] And (tblComplaints.DateReported)<=[Forms]![frmReports]![txtToDate]))
UNION
SELECT "zTotal" as ComplaintType, Null AS Product, Count(tblComplaints.AccessComplaintID) AS CountOfAccessComplaintID, Sum(Abs([ComplaintValid])) AS Expr1, tblComplaints.DateReported
FROM tblComplaints AS TR
GROUP BY "zTotal", tblComplaints.DateReported
HAVING (((tblComplaints.ComplaintType)="Product Quality") AND ((tblComplaints.DateReported)>=[Forms]![frmReports]![txtFromDate] And (tblComplaints.DateReported)<=[Forms]![frmReports]![txtToDate]));
 

Users who are viewing this thread

Top Bottom