Can I write this crosstab query as a select query?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

erick-flores - 23 Apr 2008 17:02 GMT

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

Reply to this Message

KARL DEWEY - 23 Apr 2008 17:38 GMT

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?

Signature

KARL DEWEY
Build a little - Test a little

> I have this crosstab query that is working fine, is there anyway I can
> write this query as a select query?
[quoted text clipped - 12 lines]
>
> Thanks in advance

Reply to this Message

John Spencer - 23 Apr 2008 17:57 GMT

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

> I have this crosstab query that is working fine, is there anyway I can
> write this query as a select query?
[quoted text clipped - 12 lines]
>
> Thanks in advance

Reply to this Message

erick-flores - 23 Apr 2008 18:11 GMT

Thanks for ur replies.

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

Reply to this Message