Grouping and totaling a query?

[ErinL]

Hi everyone -

I have a query that users export to Excel to be emailed. The query SQL is below:

SELECT tblItems.Customer, tblCustomers.CustomerName, qryAvailableContainers.ReceiptDate, qryAvailableContainers.ReceiptNumber, qryAvailableContainers.ItemCode, tblItems.Description, qryAvailableContainers.LotNumber, qryAvailableContainers.ContainerID, qryAvailableContainers.UOMWeight, Sum(qryAvailableContainers.QtyAvailable) AS OnHandQty
FROM tblCustomers INNER JOIN (qryAvailableContainers INNER JOIN tblItems ON qryAvailableContainers.ItemCode = tblItems.ItemCode) ON tblCustomers.CustomerNumber = tblItems.Customer
GROUP BY tblItems.Customer, tblCustomers.CustomerName, qryAvailableContainers.ReceiptDate, qryAvailableContainers.ReceiptNumber, qryAvailableContainers.ItemCode, tblItems.Description, qryAvailableContainers.LotNumber, qryAvailableContainers.ContainerID, qryAvailableContainers.UOMWeight
HAVING (((tblItems.Customer)=[forms]![frmMenuInventoryReports]![CustomerName]));

The customer would like to see a total quantity and weight at the end of each item code.

I created a report which is grouped by ItemCode and exported that to Excel. The totals show at the end of each item code but they don't like the look of the exported report as much as they do the query.

Is there a way to get the same type of total line at the end of each item code when exporting the query without going in to Excel and manually doing it? The query is too large to do that.

Thank you in advance for your assistance.

 

[plog]

No there is not. A query essentially provides you with raw data, a report will allow you to include sub-totals. My suggestion would be to configure your report to look like Excel--no formating, just cells of data--and then see if you can export that report to Excel.

 

[ErinL]

Thank you for responding so quickly. I thought the answer was no but wanted to check with the experts first.

I will try to do some reformatting of the report and see if that will work for them.

Thanks again!