Is possible to use crosstab query as subquery?

[selahlynch]

Hello,

Is it possible to use a crosstab query as a subquery?

I'm trying to do the following:

SELECT * FROM 
(TRANSFORM Max(Expirations.DateRequired) AS MaxOfDateRequired
SELECT Expirations.EmpID
FROM Expirations
GROUP BY Expirations.EmpID
PIVOT Expirations.EventID) as Xtab

But I get an error "Syntax error in FROM clause."

And in case you are curious... I'm trying to do this so that I can use the query as the record source for a subform within a form. Choices on the form affect the query.

 

[selahlynch]

Still hoping for an answer, even if it is a negative one. Is it possible to use a crosstab query as a subquery?
B. U. M. P.
Thanks

 

[jdraw]

Still hoping for an answer, even if it is a negative one. Is it possible to use a crosstab query as a subquery?
B. U. M. P.
Thanks

I really don't have an answer to this based on experience. However, I did find this advice when googling - this is a quote from a response to same question

I don't see anything obvious. I suggest that you create a query to gather up
all the information including the IIf and criteria. Make sure that it returns
the proper records. Next base a crosstab on the first query. Include the
column headings. Then create a new form or report based on this second
crosstab query.

Good luck

 

[selahlynch]

It sounds to me like that quote is talking about someone wanting to use a subquery within their crosstab query. I'm actually trying to do the opposite. I would like to use my crosstab query as a subquery.

SELECT * FROM (TRANSFORM......PIVOT.....)

No luck yet.

 

[selahlynch]

To any readers out there:
I guess the answer is no, you can not use a crosstab query as a subquery.
It seems strange to me though. I don't know why you can't. It seems like you should be able to.

 

[Simon_MT]

I pretty sure you can, I remember using them in Access 1997, the only problem was that were damn slow as Access had to calculate the what was in the columns so in the end I gave up because the crosstabs were just too slow.

Simon

 

[xboxremote]

Hey,

Sorry about the late reply, but I just came accross your post while looking for a solution to my own crosstab query problem. From what I've discovered, you can use a crosstab query as a subquery... The only caveat is that you must save that crosstab query and then query on that saved query. So if you saved your crosstab query in Access as "MaxCrosstab", then you could create a new query, and query on top of it in the FROM statement (SELECT * FROM MaxCrosstab). This runs the crosstab query first, then queries on top of it.

If you don't go about it this way and have the nested query in the same SQL statement, then it gets confused because it doesn't know necessarily what the resulting columns will be (since they are based on the values in a particular field for each record). Since it does not know in advance what the resulting columns will be, it won't let you SELECT those columns.

Nesting a crosstab query is also how you sort date columns in a crosstab query. When your pivoted columns are dates, they won't sort by actual date, but instead alphanumerically. So the best way that I've found to work around this is to query on top of that crosstab query to return the columns in the exact order you would like.

I hope this helps!