Q: Make Table from Crosstab?

[Mark]

Using Access 2000.

Is there a way to create a make-table from a crosstab query? I know I can
create a crosstab query (QRY 1) , then create another query using that
crosstab query (QRY 2), but I'm wondering if I can create a make table
straight from that crosstab query.

The reason is, to update QRY 2 (the base table has a monthly append, so we
have to add the new month) requires QRY 1 to be run to determine the column
headings. If I could just make a table from QRY 1, I wouldn't have to wait.

Thanks!
-Mark

 

[Roger Carlson]

No. You cannot do it.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[[MVP] S.Clark]

Other than the way you described, I think the answer is no.

I tried the following in Northwind, but get a Syntax Error.

SELECT test.* INTO NewTable
FROM
(
TRANSFORM Sum(CCur([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS ProductAmount
SELECT Products.ProductName, Orders.CustomerID, Year([OrderDate]) AS
OrderYear
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
WHERE (((Orders.OrderDate) Between #1/1/1997# And #12/31/1997#))
GROUP BY Products.ProductName, Orders.CustomerID, Year([OrderDate])
PIVOT "Qtr " & DatePart("q",[OrderDate],1,0) In ("Qtr 1","Qtr 2","Qtr
3","Qtr 4");
) as test

 

[Mark]

Thanks Steve... looks like I'll just have to go with Plan B.

-Mark

Other than the way you described, I think the answer is no.

I tried the following in Northwind, but get a Syntax Error.

SELECT test.* INTO NewTable
FROM
(
TRANSFORM Sum(CCur([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS ProductAmount
SELECT Products.ProductName, Orders.CustomerID, Year([OrderDate]) AS
OrderYear
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
WHERE (((Orders.OrderDate) Between #1/1/1997# And #12/31/1997#))
GROUP BY Products.ProductName, Orders.CustomerID, Year([OrderDate])
PIVOT "Qtr " & DatePart("q",[OrderDate],1,0) In ("Qtr 1","Qtr 2","Qtr
3","Qtr 4");
) as test

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Using Access 2000.

Is there a way to create a make-table from a crosstab query? I know I can
create a crosstab query (QRY 1) , then create another query using that
crosstab query (QRY 2), but I'm wondering if I can create a make table
straight from that crosstab query.

The reason is, to update QRY 2 (the base table has a monthly append, so we
have to add the new month) requires QRY 1 to be run to determine the
column
headings. If I could just make a table from QRY 1, I wouldn't have to
wait.

Thanks!
-Mark

 

[Mark]

Thanks. I expected as much, but thought there'd be a nice workaround I hadn't
thought of, or some drop down I missed.

 

[John Vinson]

Is there a way to create a make-table from a crosstab query? I know I can
create a crosstab query (QRY 1) , then create another query using that
crosstab query (QRY 2), but I'm wondering if I can create a make table
straight from that crosstab query.

Nope. You're doing it the only way it will work - two queries.

The reason is, to update QRY 2 (the base table has a monthly append, so we
have to add the new month) requires QRY 1 to be run to determine the column
headings. If I could just make a table from QRY 1, I wouldn't have to wait.

Do you really need the make-table? It sounds like you'ld be building a
badly denormalized table; can you not perhaps base your reports
directly on the crosstab query? Or are you updating the "crosstab"
data?

John W. Vinson[MVP]

 

[Mark]

Do you really need the make-table? It sounds like you'ld be building a
badly denormalized table; can you not perhaps base your reports
directly on the crosstab query? Or are you updating the "crosstab"
data?

Thanks for the response John. The crosstab query result will be put into
Excel, but we tend to be over cautious here and so we save the crosstab query
as an Access table every month. It allows us to look at the history of data,
should we ever need to. And yes, sometimes the crosstab info is updated.

John W. Vinson[MVP]

-Mark

 

[John Vinson]

Thanks for the response John. The crosstab query result will be put into
Excel, but we tend to be over cautious here and so we save the crosstab query
as an Access table every month. It allows us to look at the history of data,
should we ever need to. And yes, sometimes the crosstab info is updated.

OK, sounds like you've got a rationale for doing so - and AFAIK you're
doing it the only way that it can be done.

John W. Vinson[MVP]