Grouping breakdown in report footer for grand total.

I have a report that is run by a query linked to two tables. Here is the sql of the query.

SELECT tblMailLog.[Delivered Date], tblMailLog.[Receive Date], tblSecurity.UserName AS Expr1, tblMailLog.UserID, tblMailLog.AccountNumOrName, tblMailLog.Abbreviation, tblMailLog.TypeOfMail, tblMailLog.Area, tblMailLog.[Completed Code], tblMailLog.Comments
FROM tblMailLog INNER JOIN tblSecurity ON tblMailLog.UserID = tblSecurity.UserID
WHERE (((tblMailLog.[Delivered Date]) Between [Forms]![frmNTEGReportssup]![txtsupsd] And [Forms]![frmNTEGReportssup]![txtsuped]));

This report is grouped by Delivered Date, By UserName, then by type of mail

There is a calculation in the header for the type of mail
=" (" & Count(*) & " " & IIf(Count(*)=1,"detail record","detail records") & ")"
and a calculation in the UserName footer
="Total " & " = " & " " & [User Name] & " (" & Count(*) & " " & IIf(Count(*)=1,"detail record","detail records") & ")"

I also have a grand total in report footer :
=" (" & Count(*) & " " & IIf(Count(*)=1,"detail record","detail records") & ")"

This will then breakdown the types of mail for each UserName. And a total of all records for the entire report. What I want is to also get a breakdown on the grand total for each mail type just above the grand total in the report footer. Any help will be great.
Who is Participating?
Patrick MatthewsCommented:
I would do this by creating a new query that just grabs the counts by mail type, and using
that query to populate the recordsource of a subquery embedded in the report footer.  That way,
no maintenance is needed if new mail types get added later.

The SQL would be something like:

SELECT m.TypeOfMail, Count(*) AS Qty
    tblSecurity s ON m.UserID = s.UserID
WHERE m.[Delivered Date] Between [Forms]![frmNTEGReportssup]![txtsupsd] And [Forms]![frmNTEGReportssup]![txtsuped];
GROUP BY m.TypeOfMail
I will include a subreport in the report footer to show all the total by mail type.

the query of the subreport will be a group by:

SELECT  tblMailLog.TypeOfMail, count(*) as MailTypeNbr
FROM tblMailLog INNER JOIN tblSecurity ON tblMailLog.UserID = tblSecurity.UserID
WHERE (((tblMailLog.[Delivered Date]) Between [Forms]![frmNTEGReportssup]![txtsupsd] And [Forms]![frmNTEGReportssup]![txtsuped]))
group by tblMailLog.TypeOfMail;

Well, matthewspatrick was quicker than I...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.