Crosstab query > make table

[J Shrimps, Jr.]

Original table is in this format:
Product
Region
Price
Month
etc.
Typical crosstab query output
transposing month field:

Product Region Jan04 Feb04 Mar04 Apr04
Hairspray South $4.95 $2.25 $5.15 $9.95

However, for every crosstab query I make, I
export it to Excel and re-import it again.
Would like to just make a table in the
crosstab format.

Output below is from a crosstab
query whose output makes a table:
Product Region FirstofPrice Month
Hairspray South $4.95 Jan04
Hairspray South $2.25 Feb04
Hairspray South $5.15 Mar04
Hairspray South $9.95 Apr04
etc.

Not the table I was hoping to make.
Is there any way to make a table from
a crosstab query?

 

[JohnFol]

Yes, make it the basis of a MakeTable query.

For example in NWind, this will create a new table based off the CrossTab
query called Quarterly Orders by Product

SELECT [Quarterly Orders by Product].* INTO NewTable
FROM [Quarterly Orders by Product];

 

[J Shrimps, Jr.]

Good answer.
Now.
I am constantly having to compare a sub-regions total sales/vol/etc. with
the
total sales/vol/etc. for that region.
This is always a five step process for me (using a macro).
1. Create a Totals table by region with overall Total Sales/Vol, etc. by
Region.
2 Create a more granular, sub-totals table by sub-region with Totals by
sub-region.
3. Add "TotalRegion Sales/Vol", etc field to sub-totals table.
4. Add "Percent of TotalRegion Sales/Vol, etc. field to sub-totals table.
5. Create an update query that updates "Percent of TotalRegion" field
with matching Totals values found in Totals table.
This is getting very old and have been using it for years

Isn't there a way to update values in a table from a summarized table
using the summarized query as a basis?

Yes, make it the basis of a MakeTable query.

For example in NWind, this will create a new table based off the CrossTab
query called Quarterly Orders by Product

SELECT [Quarterly Orders by Product].* INTO NewTable
FROM [Quarterly Orders by Product];

Original table is in this format:
Product
Region
Price
Month
etc.
Typical crosstab query output
transposing month field:

Product Region Jan04 Feb04 Mar04 Apr04
Hairspray South $4.95 $2.25 $5.15 $9.95

However, for every crosstab query I make, I
export it to Excel and re-import it again.
Would like to just make a table in the
crosstab format.

Output below is from a crosstab
query whose output makes a table:
Product Region FirstofPrice Month
Hairspray South $4.95 Jan04
Hairspray South $2.25 Feb04
Hairspray South $5.15 Mar04
Hairspray South $9.95 Apr04
etc.

Not the table I was hoping to make.
Is there any way to make a table from
a crosstab query?

 

[Duane Hookom]

Have you considered using a pivot table in Excel? You can slice and dice and
total and average etc by just dragging and dropping fields.

--
Duane Hookom
MS Access MVP

Good answer.
Now.
I am constantly having to compare a sub-regions total sales/vol/etc. with
the
total sales/vol/etc. for that region.
This is always a five step process for me (using a macro).
1. Create a Totals table by region with overall Total Sales/Vol, etc.
by
Region.
2 Create a more granular, sub-totals table by sub-region with Totals
by
sub-region.
3. Add "TotalRegion Sales/Vol", etc field to sub-totals table.
4. Add "Percent of TotalRegion Sales/Vol, etc. field to sub-totals
table.
5. Create an update query that updates "Percent of TotalRegion" field
with matching Totals values found in Totals table.
This is getting very old and have been using it for years

Isn't there a way to update values in a table from a summarized table
using the summarized query as a basis?

Yes, make it the basis of a MakeTable query.

For example in NWind, this will create a new table based off the CrossTab
query called Quarterly Orders by Product

SELECT [Quarterly Orders by Product].* INTO NewTable
FROM [Quarterly Orders by Product];

Original table is in this format:
Product
Region
Price
Month
etc.
Typical crosstab query output
transposing month field:

Product Region Jan04 Feb04 Mar04 Apr04
Hairspray South $4.95 $2.25 $5.15 $9.95

However, for every crosstab query I make, I
export it to Excel and re-import it again.
Would like to just make a table in the
crosstab format.

Output below is from a crosstab
query whose output makes a table:
Product Region FirstofPrice Month
Hairspray South $4.95 Jan04
Hairspray South $2.25 Feb04
Hairspray South $5.15 Mar04
Hairspray South $9.95 Apr04
etc.

Not the table I was hoping to make.
Is there any way to make a table from
a crosstab query?