expanding a pivot table with more information

[rust]

This should be a lot easier than the way I'm doing it.
my begining tables look like

------------------------
Table A

Arowid ItemID Year aValue useit
1 1 1999 30 true
2 1 2000 20 true
3 1 2001 10 true
4 2 1999 40 true
5 2 2000 40 true
6 3 2001 12 false
7 4 1999 10 false


Table B
BrowID Year W1 W2
1 1999 .1 .2
2 2000 .2 .4
3 2001 .3 .6

----------------------------------------------------

What the customer wants to see:

ItemID 1999 2000 2001 sum(W1*aValue) sum(W2*aValue)
1 30 20 10 10 20
2 40 40 12 24


---------------------------------------------------

writing the queries to get the values was easy enough but displaying in the above format isn't working

 

[rust]

False Alarm sort of

I had a Query which did two things - filtered on the useit flag and also joined table A with table B which got the appropriate W1 and W2
so the query results looked like:
ItemID Year aValue W1av W2av
1 1999 30 3 6
1 2000 20 4 8
1 2001 10 3 6
2 1999 40 4 8
2 2000 40 8 16

I then tried to make a crosstab query:
column heading based on Year row based on ItemID.

The values under the year's would be the average(aValue) which in this case would be the aValue and then the sum of the W1av and the sum of the W2av.
The crosstab query wouldn't work - wouldn't recognize the
table's A values.

So I put the results in a temporary table redid the crosstab query and somehow it is working. I would rather use the query and not the temporary table - but a half of loaf is better than none.

 

[PHV]

1) Create a Select query Query1 like this:
SELECT TableA.ItemID, TableA.Year, TableA.aValue, [W1]*[aValue] AS Value1, [W2]*[aValue] AS Value2
FROM TableA INNER JOIN TableB ON TableA.Year = TableB.Year;
2) Create a Pivot query Query2 like this:
TRANSFORM Sum(Query1.aValue) AS SommeDeaValue
SELECT Query1.ItemID
FROM Query1
GROUP BY Query1.ItemID
PIVOT Query1.Year;
3) Create a Select query Query3 like this:
SELECT Query1.ItemID, Sum(Query1.Value1) AS SommeDeValue1, Sum(Query1.Value2) AS SommeDeValue2
FROM Query1
GROUP BY Query1.ItemID;
4) Create a Select query Query4 like this:
SELECT Query2.*, Query3.SommeDeValue1, Query3.SommeDeValue2
FROM Query2 INNER JOIN Query3 ON Query2.ItemID = Query3.ItemID;

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[rust]

I can't thank you guys enough - middle of the night and poof an answer pops out. I'll try your idea, though I may be ovelooking something, it looks like I loose the aValue in the row.
The result has to look like:
ItemID 1999 2000 2001 sum(W1*aValue) sum(W2*aValue)
1 30 20 10 10 20
2 40 40 12 24


step 1:
is a little hairy because my tTemp table is the results of a left outer join between two tables with an annoying where clause, besides getting the W1 and W2. Its looking for a true flag on several date fields. So there are really three tables involved.

step 2:
TRANSFORM Avg([tTemp].[AValue]) AS AvgOfAValue
SELECT [tTemp].[actID], [tTemp].[PID], format(Sum([tTemp].[CL]),"#0.00") AS CL, format(sum([tTemp].[SL]),"0.00") AS SL
FROM tTemp
GROUP BY [tTemp].[actID], [tTemp].[PID]
PIVOT [tTemp].[gYEAR];

 

[PHV]

This the result I have with your TableA and TableB example:
[tt]
ItemID 1999 2000 2001 SommeDeValue1 SommeDeValue2
1 30 20 10 10 20
2 40 40 12 24
3 12 3,6 7,2
4 10 1 2[/tt]

AFAIK, TableA and TableB may be tables or queries, doesn't matter.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[rust]

Thanks

 

[rust]

Hi PHV
It didn't work. My situation was slightly more complicated than originally shown. I keep getting ACCESS doesn't recognize [tblA].[gYear] in query one when running from query 2
Even though query1 works fine as a stand alone. That's why I ended up storing to a temporary table.

There is a parameter in the Query to choose a year from the tTheAct table.

I'll try to show you what I have here. Not sure how it will look.

Query 1

SELECT tTheAct.actID, tTheAct.PID, tblA.SValue, tblA.FValue, ([tblA].[SValue]-[tblA].[FValue]) AS DIFVALUE, (([tblA].[SValue]-[tblA].[FValue])*(select [tblW].[W1] from tblW where [tblW].[WYear] = [tblA].[gYear])/100) AS CL, (([tblA].[SValue]-[tblA].[FValue])*(select [tblW].[W2] from tblW where [tblW].[WYear] = [tblA].[gYear])/100) AS SL, tblA.gYEAR, (select [tblW].[W1] from tblW where [tblW].[WYear] = [tblA].[gYear]) AS theW1, (select [tblW].[W2] from tblW where [tblW].[WYear] = [tblA].[gYear]) AS theW2
FROM tTheAct LEFT JOIN tblA ON tTheAct.actID = tblA. actID
WHERE (((tTheAct.S1OutCome)='WON') AND ((tTheAct.Finished)=True) AND ((Year([tTheAct].[S1Date]))=[myYear])) OR (((tTheAct.Finished)=True) AND ((tTheAct.S2OutCome)='WON') AND ((Year([tTheAct].[S2Date]))=[myYear])) OR (((tTheAct.Finished)=True) AND ((tTheAct.S3OutCome)='WON') AND ((Year([tTheAct].[S3Date]))=[myYear])) OR (((tTheAct.Finished)=True) AND ((tTheAct.S4OutCome)='WON') AND ((Year([tTheAct].[S4Date]))=[myYear])) OR (((tTheAct.Finished)=True) AND ((tTheAct.S5OutCome)='WON') AND ((Year([tTheAct].[Stage5Date]))=[myYear])) OR (((tTheAct.Finished)=True) AND ((tTheAct.S6OutCome)='WON') AND ((Year([tTheAct].[S6Date]))=[myYear]))
GROUP BY tTheAct.actID, tTheAct.PID, tblA.SValue, tblA.FValue, tblA.gYEAR;

Table tTheAct

actID | PID | S1OutCome | S1Date | S2OutCome | S2Date|S3OutCome | S3Date| S4OutCome | S4Date| S5OutCome | S5Date| S6OutCome | S6Date| Finished

data rows :
|1, 1 , ‘LOOSE’,#01/01/1993#,’WON’,#03/03/19997#, RUN’,#01/01/1998#,’WON’,#03/03/1999#,,,,True |
|2, 1 , ‘SKIP’,#01/01/1999#,’WON’,#03/03/2004#,,,,,,,,True |
|3, 2 , ‘HOP’,#01/01/1996#,’WON’,#03/03/1997#,,,,,,,,True |
|4, 2 , ‘JUMP’,#01/01/1999#,’WON’,#03/03/2004#,,,,,,,,True |

Table tblA A
actID | PID | Svalue|Fvalue|gYear|

actID PID Year Svalue Fvalue
|1, 1, 1997, 34, 10 |
|1, 1, 1998, 12, 21 |
|2, 1, 1999, 60 , 30 |
|2, 1, 2000, 40, 20 |
|2, 1, 2001, 50, 40 |
|3, 2, 1996, 20, 11 |
|3, 2, 1997, 20, 13 |
|4, 2, 1999, 80, 40 |
|4, 2, 2000, 100, 60 |
|5, 3, 2001, 24, 12 |
|6 , 4, 1999, 30, 20 |

tblW

|WD |Year| W1| W2|
|1, 1999, 10, 20 |
|2, 2000, 20, 40 |
|3, 2001, 30, 60 |


query2 wont run

TRANSFORM Sum(Query1.CL) AS SommeDeaCL
SELECT Query1.actID, Query1.PIDFROM Query1
GROUP BY Query1.actID, Query1.PID