CrossTab Query/Report Help

[Jebba]

Hey. I'm new to Access, and need help creating a report, and I thought
using a crosstab query would be the best way...now I'm not
sure...here's the simplified report I'm trying to create:

Year 1 Year2 YTY Margin
Vendor Cost | #Units Cost | #Units Cost | #Units
------ ---- ------ ---- ------ ---- ------


I can use the crosstab query and make year 1 and year 2 the column
headings and can create a bound field in the report that calcs the
margin, but I can't figure out how to have more than one column
heading (effectively, I'm trying to create a report that looks/acts
like an Excel Pivot Table). Is the crosstab query the best way (maybe
I just need to add some code) or is there another way to go around
this simply working off of the base table or the original queries
dynaset? here are the column headings of the original table:

Vendor,Cost,Year

Thanks for any help.

 

[Grzegorz Danowski]

Hey. I'm new to Access, and need help creating a report, and I thought
using a crosstab query would be the best way...now I'm not
sure...here's the simplified report I'm trying to create:

Year 1 Year2 YTY Margin
Vendor Cost | #Units Cost | #Units Cost | #Units
------ ---- ------ ---- ------ ---- ------


I can use the crosstab query and make year 1 and year 2 the column
headings and can create a bound field in the report that calcs the
margin, but I can't figure out how to have more than one column
heading (effectively, I'm trying to create a report that looks/acts
like an Excel Pivot Table). Is the crosstab query the best way (maybe
I just need to add some code) or is there another way to go around
this simply working off of the base table or the original queries
dynaset? here are the column headings of the original table:

Vendor,Cost,Year

Possible solutions:

1. make two pivot queries: qCost, qUnits and join them by Vendor,

2. instead of access pivot queries make your own cross query:

Select
Vendor,
Sum(IIF(Year = 1, Cost, Null)) As Year1Cost,
Sum(IIF(Year = 1, Units, Null)) As Year1Units,
Sum(IIF(Year = 2, Cost, Null)) As Year2Cost,
Sum(IIF(Year = 2, Units, Null)) As Year2Units
From
SourceTable
Group By
Vendor

3. maybe use Excel Pivot tables using automation?

Regards,
Grzegorz