S
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?
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?