transform (crosstab) / union and subquery problem

B

Brotha Lee

LS,

I have the following code in which I try to crosstab a union query and make
a calculation based on a subquery. The calculation should be the current
value devided by the value of 1 year back. Here is the code:

TRANSFORM Sum(tst.ACTUAL) AS SumOfACTUAL
SELECT tst.PeriodBase, tst.Unit, tst.Time,
FROM (SELECT Version, "Per1" AS PeriodBase, "Unit" AS Unit,
DateSerial([the_year],[month_of_year],1) AS [Time], Sum(Tbl1.Value)/ (select
sum(Value) from Tbl1 as Dupe
where dupe.Product = Tbl1.Product and dupe.version = Tbl1.version and
dupe.yearmonth = Tbl1.yearmonth-100) AS ACTUAL
FROM Tbl1
GROUP BY "Per1", "Unit", DateSerial([the_year],[month_of_year],1),
Tbl1.yearmonth, Tbl1.Version, Tbl1.product
HAVING (((TBL1.Version)="REAL") AND ((TBL1.Product)="Product1"))
UNION
SELECT Version, "Per2" AS PeriodBase, "Unit" AS Unit,
DateSerial([the_year],[month_of_year],1) AS [Time], Sum(TBL1.Value) /(select
sum(Value) from Tbl1 as Dupe
where dupe.Product = Tbl1.Product and dupe.version = Tbl1.version and
dupe.yearmonth = Tbl1.yearmonth-100) AS ACTUAL
FROM TBL1
GROUP BY "Per1", "Unit", DateSerial([the_year],[month_of_year],1),
Tbl1.yearmonth, Tbl1.Version, Tbl1.product
HAVING (((TBL1.Version)="FC") AND ((TBL1.Product)="Product1"))) AS tst
GROUP BY tst.PeriodBase, tst.Unit, tst.Time, tst.Measure
PIVOT tst.Version;

When I delete the subquery code: (select sum(Value) from Tbl1 as Dupe
where dupe.Product = Tbl1.Product and dupe.version = Tbl1.version and
dupe.yearmonth = Tbl1.yearmonth-100) the code works just fine, but with this
code include Access is giving me back the error: The Microsoft yet engine
does not recognize tbl1.product.

Does anybody know the solution? I do not want to break the code, as it is
part of a more complex code.

TIA
 
Top