Pivoting Data

[gkrenton]

I have a Table (and query) that gives me the results that I'm looking for but I need to pivot them.
The results currently are shown as:
Date, Dept, Class, Avg Price, Avg Cost
2/01/04 1 3 25.00 13.33
3/15/04 1 3 24.57 13.25
etc
What I'm trying do is pivot this data to look like this

Date Dept/Class Avg Price, Avg Cost so using the above numbers

Dept Class Date Date
2/01/04 03/15/04
1 3 25.00,13.33 24.57,13.25

Cross tab'ing the query only allows me 1 set of data within the row so I can do price or cost but not both. I ultimately need to export the data to a CSV in this format. I know how to do this in Crystal but seems like I should be able to do the same pivot in Access & keep my exporting to one DB product at a time.

Thanks in advance -

Gina

 

[dhookom]

You can create crosstabs with multiple values. One method is described in faq701-4524. There are several report samples at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[gkrenton]

Thanks for the help, but I'm still not quite 'getting' it to work correctly. I'm unable to review the sample reports since I'm using Access XP so am working from the FAQ example.
Using the FAQ assistance I have the following

TRANSFORM Sum(IIf([fldname]="Rtl",[AvgRtl],[AvgCostt])) AS DaVal
SELECT PriceOrd.Dept1, PriceOrd.Class
FROM priceordXtable, PriceOrd
GROUP BY PriceOrd.Dept1, PriceOrd.Class
PIVOT ([fldname] & [OTBDate1]);

For the priceordxtable fldname I've input the value Rtl & Cst into the table. Additionally all the calculations are already done within the 'priceord' table so no calculations are needed within the crosstab.

When I run this crosstab query I only get the cost dollars & not the retail dollars-
Obviously I'm missign something but I don't have a clue as to what it is -

Thanks in advance for some extra help.

Gina

 

[clinresys]

Base your crosstab on a query with functions that return what you want.

 

[dhookom]

Gina,
What happens if you try:
TRANSFORM Sum(IIf([fldname]="Cst",[AvgCostt],[AvgRtl])) AS DaVal
SELECT PriceOrd.Dept1, PriceOrd.Class
FROM priceordXtable, PriceOrd
GROUP BY PriceOrd.Dept1, PriceOrd.Class
PIVOT ([fldname] & [OTBDate1]);


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[gkrenton]

Oh lord I'm blind - scrolling left it my 'problem' but now the kicker -
I need retail & cost dollars to coincide one column after

I get

Cst11/2003, Cst12/2003, rtl11/2003,rtl12/2003
Dept 1, Class 3, 18.58 18.37 38.23 37.24

What I need is
Rtl 11/2003, Cst 11/2003, Rtl 12/2003, Cost12/3000
Dept 1 Class3 38.23,18.58 38.23,18.37 etc

Any suggestions up your magic sleeve?

Thanks

Gina

 

[dhookom]

I would display the results in a report so the order of the columns would not make any difference to me. If you want the months together, you could enter column headings property of
RTL11/2003,Cst11/20003,...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[gkrenton]

Oh if could be as simple as that - I need to export the ending data into a csv comma delimited format which a report won't let me do, thus trying to get the query & crosstab to organize the data in a manner that I can export.
Technically speaking the column titles/dates aren't needed but they need to be in a calendar date order with retail$, Cost$ following to match up to the product I'm importing into -

Unless someone has a better solution I think I'm stuck doing this one process in Excel?

Gina

 

[dhookom]

All you should have to do is switch the names of the fields in the table to an order that will sort correctly. Then change:
PIVOT ([fldname] & [OTBDate1]);
to:
PIVOT ([OTBDate1] & [fldname]);


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[gkrenton]

Duane-
You're my hero! A Simple solution to a somewhat simple conundrum for this hacker.
Thanks a ton - not only looks like it resolves my issue but definately moved my understanding of crosstabs and Access a few chapters foward.

Gina