OK, just for review. Your query that should be named qryDetails:
SELECT [1-1 history week].dated AS movement_date, [1-3 sales week].dated AS sales_date, [1-0 products].dept,
[Manufacturing Department].[Department Name], [Sub Group].prod_type, [1-0 products].product, [1-0 products].long_description,
[1-0 products].sub_grp, [1-2 bom].alpha_code, [1-0 products].unit_code, [1-0 products].ship_cat AS [sheets/box],
[1-1 history week].SumOfmovement_quantity AS produced,
[1-0 products]![ship_cat]*[1-1 history week]![SumOfmovement_quantity] AS sheets, [1-3 sales week].SumOfquantity AS sales_qty,
[1-0 products]![ship_cat]*[1-3 sales week]![SumOfquantity] AS sheets_sold, [1-3 sales week].SumOfval AS sales_val,
[1-3 sales week].SumOfcost AS cost, [1-2 bom].instructions1, [1-2 bom].instructions2, [1-2 bom].instructions3,
[1-2 bom].instructions4
FROM (((([1-0 products] LEFT JOIN [1-2 bom]
ON [1-0 products].product = [1-2 bom].product_code)
LEFT JOIN [1-1 history week]
ON [1-0 products].product = [1-1 history week].product)
LEFT JOIN [1-3 sales week]
ON [1-0 products].product = [1-3 sales week].product)
INNER JOIN [Manufacturing Department]
ON [1-0 products].dept = [Manufacturing Department].[Department No]) INNER JOIN [Sub Group]
ON [1-0 products].sub_grp = [Sub Group].[sub grp]
WHERE ((([1-0 products].dept)="40") AND
(([1-1 history week].SumOfmovement_quantity) Is Not Null)) OR
((([1-0 products].dept)="40") AND
(([1-3 sales week].SumOfquantity) Is Not Null));
My query that should be named qryTotals:
SELECT qryDetails.prod_type, qryDetails.long_description, Sum(qryDetails.produced) As TotalProduced,
Sum(qryDetails.sheets) As TotalSheets, Sum(qryDetails.sales_qty) As TotalSales_Qty,
Sum(qryDetails.sheets_sold) As TotalSheets_Sold, Sum(qryDetails.sales_val) As TotalSales_Val,
Sum(Cost) As TotalCost
FROM qryDetails
GROUP BY qryDetails.prod_type, qryDetails.long_description;
UNION query:
SELECT qryDetails.*, "" As Seq
FROM qryDetails
UNION ALL
SELECT Null As movement_Date, Null As sales_Date, "" As dept, "" As [Department Name], qryTotals.prod_type, "" As product,
qryTotals.long_description, "" As sub_grp, "" As alpha_code, "" As unit_code, "" As [sheets/box], qryTotals.TotalProduced, qryTotals.TotalSheets, qryTotals.TotalSales_Qty, qryTotals.TotalSheets_Sold,
qryTotals.TotalSales_Val, qryTotals.TotalCost, "" As instructions1, "" As instructions2, "" As instructions3, "" As instructions4, "Totals" As Seq
FROM qryTotals
ORDER BY prod_type, long_description, Seq;
That should give you all the details rows for a prod_type and long_description followed by the one totals row. You can then use that to export to Excel from Access or import to Excel from Excel.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
