C
Christian
Hi NG,
I'm trying to create a query that will calculate the difference between two
systems.
Data from each system is available in two queries; QrySubSys1 and
QrySubSys2.
The two data sets contain the same fields. However the calculation is a bit
tricky as the difference calculation should be done on group level for each
respective system. One system might have one or several records within a
group. Below an example
--- Example of what I would like get ---
Region, Group, Groupname, Site, System, Volume
Americas, 20, North, Site1, Sys1, Vol: 50
Americas, 20, North, Site2, Sys1, Vol: 50
Americas, 20, North, Site1, Sys2, Vol: 105
Query result line for group North:
Diff_1: Sys1 Vol - Sys2 Vol = -5
Americas, 21, South, Site5, Sys1, Vol 100
Americas, 21, South, Site4, Sys2, Vol 40
Americas, 21, South, Site5, Sys2, Vol 45
Query result line for group south:
Diff_2: Sys1 Vol - Sys2 Vol = 15
---------
My problem is that the result I get with the query (listed below) is like
this for the first example
Americas, 20, North, Site1, Sys1, Vol: 50
Americas, 20, North, Site2, Sys1, Vol: 50
Americas, 20, North, Site1, Sys2, Vol: 105
(Americas, 20, North, Site1, Sys2, Vol: 105 this is added by the query)
Query result line:
Diff_1: Sys1 Vol - Sys2 Vol = -110
which is not the result I would expect. How can I get the query not to
repeat the records with fewer data set than the set I compare against? What
I really would like to do is to be able to calculate difference of Sys1 vs
Sys2 by Region or by Group or global (just Sys1 vs Sys2)
The query is a bit messy, but this is what access has listed up:
PARAMETERS [Forms]![FrmMain]![RunPlanWeekCombo] Text ( 255 ),
[Forms]![FrmMain]![RunSupplierCombo] Text ( 255 );
SELECT
QrySubSys1.Region, QrySubSys1.Group, QrySubSys1.GroupName,
"10" AS GroupSort, "" AS Site, QrySubSys1.Supplier, QrySubSys1.PlanWeek,
"3 SiteDiff" AS Source, QrySubSys1.ForecastWeek,
Sum(QrySubSys2.Sys2Volume) AS SumOfSys2Volume, Sum(QrySubSys1.Sys1Volume) AS
SumOfSys1Volume,
Sum([Sys1Volume]-[Sys2Volume]) AS Volume
FROM
QrySubSys1 INNER JOIN QrySubSys2 ON (QrySubSys1.GroupName =
QrySubSys2.GroupName) AND
(QrySubSys1.Group = QrySubSys2.Group) AND (QrySubSys1.Region =
QrySubSys2.Region) AND
(QrySubSys1.FirmPeriod = QrySubSys2.FirmPeriod) AND (QrySubSys1.Quarter =
QrySubSys2.Quarter) AND
(QrySubSys1.Year = QrySubSys2.Year) AND
(QrySubSys1.ForecastWeek = QrySubSys2.ForecastWeek) AND
(QrySubSys1.PlanWeek = QrySubSys2.PlanWeek)
GROUP BY QrySubSys1.Region, QrySubSys1.Group, QrySubSys1.GroupName,
"10", "", QrySubSys1.Supplier, QrySubSys1.PlanWeek, "3 SiteDiff",
QrySubSys1.ForecastWeek;
Any pointers/suggestions are warmly welcome
- Chr
I'm trying to create a query that will calculate the difference between two
systems.
Data from each system is available in two queries; QrySubSys1 and
QrySubSys2.
The two data sets contain the same fields. However the calculation is a bit
tricky as the difference calculation should be done on group level for each
respective system. One system might have one or several records within a
group. Below an example
--- Example of what I would like get ---
Region, Group, Groupname, Site, System, Volume
Americas, 20, North, Site1, Sys1, Vol: 50
Americas, 20, North, Site2, Sys1, Vol: 50
Americas, 20, North, Site1, Sys2, Vol: 105
Query result line for group North:
Diff_1: Sys1 Vol - Sys2 Vol = -5
Americas, 21, South, Site5, Sys1, Vol 100
Americas, 21, South, Site4, Sys2, Vol 40
Americas, 21, South, Site5, Sys2, Vol 45
Query result line for group south:
Diff_2: Sys1 Vol - Sys2 Vol = 15
---------
My problem is that the result I get with the query (listed below) is like
this for the first example
Americas, 20, North, Site1, Sys1, Vol: 50
Americas, 20, North, Site2, Sys1, Vol: 50
Americas, 20, North, Site1, Sys2, Vol: 105
(Americas, 20, North, Site1, Sys2, Vol: 105 this is added by the query)
Query result line:
Diff_1: Sys1 Vol - Sys2 Vol = -110
which is not the result I would expect. How can I get the query not to
repeat the records with fewer data set than the set I compare against? What
I really would like to do is to be able to calculate difference of Sys1 vs
Sys2 by Region or by Group or global (just Sys1 vs Sys2)
The query is a bit messy, but this is what access has listed up:
PARAMETERS [Forms]![FrmMain]![RunPlanWeekCombo] Text ( 255 ),
[Forms]![FrmMain]![RunSupplierCombo] Text ( 255 );
SELECT
QrySubSys1.Region, QrySubSys1.Group, QrySubSys1.GroupName,
"10" AS GroupSort, "" AS Site, QrySubSys1.Supplier, QrySubSys1.PlanWeek,
"3 SiteDiff" AS Source, QrySubSys1.ForecastWeek,
Sum(QrySubSys2.Sys2Volume) AS SumOfSys2Volume, Sum(QrySubSys1.Sys1Volume) AS
SumOfSys1Volume,
Sum([Sys1Volume]-[Sys2Volume]) AS Volume
FROM
QrySubSys1 INNER JOIN QrySubSys2 ON (QrySubSys1.GroupName =
QrySubSys2.GroupName) AND
(QrySubSys1.Group = QrySubSys2.Group) AND (QrySubSys1.Region =
QrySubSys2.Region) AND
(QrySubSys1.FirmPeriod = QrySubSys2.FirmPeriod) AND (QrySubSys1.Quarter =
QrySubSys2.Quarter) AND
(QrySubSys1.Year = QrySubSys2.Year) AND
(QrySubSys1.ForecastWeek = QrySubSys2.ForecastWeek) AND
(QrySubSys1.PlanWeek = QrySubSys2.PlanWeek)
GROUP BY QrySubSys1.Region, QrySubSys1.Group, QrySubSys1.GroupName,
"10", "", QrySubSys1.Supplier, QrySubSys1.PlanWeek, "3 SiteDiff",
QrySubSys1.ForecastWeek;
Any pointers/suggestions are warmly welcome
- Chr
