Access Grand Total not correct

Question

I have a Access Report that I have been working on that uses a formula in the Details to add up rows. Then I am wanting to Grand Total that Total column. The Total on the row is working perfectly, however the Grand Total is not adding everything up correctly.

Formula Total for each Row

=IIf([Pkg] Like "*Large Drum*",[SQtyProd]*[DrmChrg],[SQtyPD]*[PltChrg])+[XPallet]+[OrderChg]+[Label]+[Wrap]+[Labor]+[Rush]

Grand Total Formula in Footer

=Sum(IIf([Pkg] Like "*Large Drum*",[SQtyProd]*[DrmChrg],[SQtyPD]*[PltChrg])+[XPallet]+[OrderChg]+[Label]+[Wrap]+[Labor]+[Rush])

SQL from Report

SELECT DISTINCT tblShipping.SDate, tblShipping.shpOrder, tblShipping.shpSKU, tblShipping.shpSKU, tblShipping.shpLot, tblShipping.shpQtyProd, tblShipping.shpQtyPD, tblShipping.shpXPallet, tblShipping.shpOrderChg, tblShipping.shpLabel, tblShipping.shpLabel1, tblShipping.shpWrap, tblShipping.shpBand, tblShipping.shpLabor, tblShipping.shpLabor1, tblShipping.shpRush, tblPricing.*, tblProduct.*, tblInvoice.*
FROM tblPricing, tblInvoice, tblProduct INNER JOIN tblShipping ON tblProduct.SKU = tblShipping.[shpSKU]
WHERE (((tblShipping.SDate) Between [Forms]![frmInvoice]![ctrSDate] And [Forms]![frmInvoice]![ctrEDate]));

Your Grand Total has an extra field in the calculation -- tblInvoice.UPS — Wayne G. Dunn
If I take that out it still doesn't work. What else could be wrong? — Justin TriadTechx
could you be a little more specific about the totals not being correct? Is the grand total too much, too little? Is the Grand Total just supposed to sum the prior row totals? — Wayne G. Dunn
The Grand Total is only summing the (IIf([Pkg] Like "*Large Drum*",[SQtyProd]*[DrmChrg],[SQtyPD]*[PltChrg]). It is not including +[XPallet]+[OrderChg]+[Label]+[Wrap]+[Labor]+[Rush] So therefore the total is too small. The Total column in the details adds up the row totals across and the Grand Total just needs to sum the Total column at the bottom. — Justin TriadTechx

Wayne G. Dunn Wayne G. Dunn · Accepted Answer · 2014-12-02T16:17:05

Your current formula includes a right paren that may be causing your problem. What you currently have is:

=Sum(IIf([Pkg] Like "*Large Drum*",[SQtyProd]*[DrmChrg],[SQtyPD]*[PltChrg])+[XPallet]+[OrderChg]+[Label]+[Wrap]+[Labor]+[Rush])

I suggest trying the following. Although it should produce the same result as what you have, yours is not working (also, to someone picking up later, it seems clearer what the intent is):

=Sum(IIf([Pkg] Like "*Large Drum*",([SQtyProd]*[DrmChrg]+[XPallet]+[OrderChg]+[Label]+[Wrap]+[Labor]+[Rush]),([SQtyPD]*[PltChrg]+[XPallet]+[OrderChg]+[Label]+[Wrap]+[Labor]+[Rush])))

SQL from Report

SELECT DISTINCT tblShipping.SDate, tblShipping.shpOrder, tblShipping.shpSKU, tblShipping.shpSKU, tblShipping.shpLot, tblShipping.shpQtyProd, tblShipping.shpQtyPD, tblShipping.shpXPallet, tblShipping.shpOrderChg, tblShipping.shpLabel, tblShipping.shpLabel1, tblShipping.shpWrap, tblShipping.shpBand, tblShipping.shpLabor, tblShipping.shpLabor1, tblShipping.shpRush, tblPricing.*, tblProduct.*, tblInvoice.*
FROM tblPricing, tblInvoice, tblProduct INNER JOIN tblShipping ON tblProduct.SKU = tblShipping.[shpSKU]
WHERE (((tblShipping.SDate) Between [Forms]![frmInvoice]![ctrSDate] And [Forms]![frmInvoice]![ctrEDate]));

Access Grand Total not correct

2 Answers