Query/Report Grouping and Bad Totals

[Selwyn Young]

I have the below query used in a report:
SELECT tblRegion.RegionName, tblSource.SourceName AS GName,
tblPack.PackName, Count(tblOrder.OrderID) AS CountOfOrderID, Sum(IIf
(IsNull([Cost]),0,[Cost])) AS SumOfCost, Sum(IIf(IsNull([Tax1]),0,
[Tax1])) AS SumOfTax1, Sum(IIf(IsNull([Tax2]),0,[Tax2])) AS SumOfTax2,
[SumOfCost]+[SumOfTax1]+[SumOfTax2] AS TotalCost
FROM (qryListState RIGHT JOIN tblPeople ON qryListState.StateID =
tblPeople.StateID) INNER JOIN ((vTransactiontoCost RIGHT JOIN (tblRegion
INNER JOIN ((tblOrder INNER JOIN tblOrderDetail ON tblOrder.OrderID =
tblOrderDetail.OrderID) INNER JOIN tblPack ON tblOrderDetail.PackID =
tblPack.PackID) ON tblRegion.RegionID = tblOrder.RegionID) ON
vTransactiontoCost.OrderID = tblOrder.OrderID) INNER JOIN tblSource ON
tblOrder.SourceID = tblSource.SourceID) ON tblPeople.PeopleID =
tblOrder.PeopleID
WHERE (((tblOrder.CreatedStamp) Between [Forms]![frmReportSuper].
[dteFrom] And [Forms]![frmReportSuper].[dteTo]) AND
((vTransactiontoCost.Balance)>=0) AND ((InStr([forms]![frmReportSuper].
[txtRegion],"X" & CStr([tblOrder].[RegionID]) & "X"))>0))
GROUP BY tblRegion.RegionName, tblSource.SourceName, tblPack.PackName
ORDER BY tblRegion.RegionName, tblSource.SourceName, tblPack.PackName;

This generates a sales report grouped by the sales source (Phone order,
web order etc) and showing subtotals for each product (pack) in that
source.
The total comes out to much less than the actual sales. If I remove the
grouping then the grand total is perfect but the query then doesn't have
the detail that the client requires.

How do I get the totals to add up correctly while sitll giving the
client the breakdown?

 

[[MVP] S.Clark]

Sometimes it's best to make a simple query, one that gathers all of the
data, then do all of the Groupings and Sums on the report. Or, create a
table that is used as the basis of the report, and append and manipulate the
data as needed before printing the report. Many times, trying to solve with
just one query is not the answer.

 

[Selwyn Young]

In this case, I need to use as few queries as possible. I have a very
large reporting front end because the client wanted to run any report on
anything. Additional queries above the 10 or so that I already have will
make things even more complicated. What I have posted is not the complete
query but a cutdown version for debugging.

If the code is too complicated, all I need is an answer to a general
query questions: Why would I be losing values when I group in three
groups but have good totals when the result is just a sinle line?

 

[Michael Cheng [MSFT]]

Hi Selwyn,

Thanks for your posting!

I do believe there might be something missing in your codes, but the Query
seems a little bit complicated. Would you please share me a sample mdb file
with some sample records so that I will be able to reproduce it on my side?

My email address is (e-mail address removed) (please remove online as
it is only for SPAM). Note that extension name mdb will be filtered by
Outlook, so that you are encouraged to eliminate the extension name for
your attachment.

Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center