Can I write this crosstab query as a select query?

[erick-flores]

I have this crosstab query that is working fine, is there anyway I can
write this query as a select query?

TRANSFORM Sum(dm_spiisumm_local.qty) AS SumOfqty
SELECT dm_spiisumm_local.partgroup, dm_spiisumm_local.region,
Max(Months.Month_lon) AS MaxOfMonth_lon, Max(Months.Number) AS
MaxOfNumber, Max(dm_spiisumm_local.fiscal_year) AS MaxOffiscal_year
FROM dm_spiisumm_local LEFT JOIN Months ON dm_spiisumm_local.header =
Months.Month
WHERE (((dm_spiisumm_local.region)="Western"))
GROUP BY dm_spiisumm_local.partgroup, dm_spiisumm_local.region
PIVOT dm_spiisumm_local.header;

Any ideas?

Thanks in advance

 

[KARL DEWEY]

A crosstab query is a 'select' query with a few more bells and whistles.
Can you post what you want the select query to produce?

 

[John Spencer]

Open the query in design view
Select Query: Select from the menu

That should return all the same data, but it won't be organized in the same
manner.

IF you mean, you want to write the equivalent of a crosstab query and get the
equivalent output, then that can be done. It will require you to use
subqueries to get the desired output and you will need to know what each
column generated by the PIVOT Clause would be.

I needed to do this one time and the crosstab query was 895 characters. The
equivalent query was approximately 13 thousand characters.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[erick-flores]

Thanks for ur replies.

I got it by changing the query to select and then doing some
expression to get the bells and whistles.