Totals & Grouping on Reports

[tyemm]

I have a report, based on the query below. The report displays some individual totals (specifically, the NYPORTS for a particular sServiceDescription). But I'd like to show additional totals, such as Total of all NYPORTS for a given sServiceDescription, as well as a grand total over all.

The SQL below may be enough (it is for the query underlying the report:


SELECT qryNYPORTSUnion.NYPORTS, Count(*) AS Total, qryNYPORTSUnion.sServiceCode, ClinicalService.sServiceDescription
FROM qryNYPORTSUnion INNER JOIN ClinicalService ON qryNYPORTSUnion.sServiceCode = ClinicalService.sServiceCode
WHERE (((qryNYPORTSUnion.dtReportDate) Between [Forms]![frm NYPORTS Report Generator]![cboStartDate] And [Forms]![frm NYPORTS Report Generator]![cboEndDate]))
GROUP BY qryNYPORTSUnion.NYPORTS, qryNYPORTSUnion.sServiceCode, ClinicalService.sServiceDescription
HAVING (((qryNYPORTSUnion.sServiceCode)="14" Or (qryNYPORTSUnion.sServiceCode)="18" Or (qryNYPORTSUnion.sServiceCode)="21" Or (qryNYPORTSUnion.sServiceCode)="32"));

 

[dhookom]

I don't think I know what you are asking for. Do you have some sample records you would like to share?

BTW: your query should probably be:
SELECT qryNYPORTSUnion.NYPORTS, Count(*) AS Total, qryNYPORTSUnion.sServiceCode, ClinicalService.sServiceDescription
FROM qryNYPORTSUnion INNER JOIN ClinicalService ON qryNYPORTSUnion.sServiceCode = ClinicalService.sServiceCode
WHERE (((qryNYPORTSUnion.dtReportDate) Between [Forms]![frm NYPORTS Report Generator]![cboStartDate] And [Forms]![frm NYPORTS Report Generator]![cboEndDate]))
And qryNYPORTSUnion.sServiceCode IN ("14","18","21","32")
GROUP BY qryNYPORTSUnion.NYPORTS, qryNYPORTSUnion.sServiceCode, ClinicalService.sServiceDescription;


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]