crosstab query as a subquery? how to do that?

[Pablo]

Is it possible to use crosstab query as a subquery for another query. I
tried something like this in SQL view for query project:


SELECT [New Crosstab Query].[ClientID] FROM

(TRANSFORM Sum(TabToSum.[Operation Amount]) AS [Sum Of Operation Amount]
SELECT TabToSum.ClientID AS [Client No]
FROM TabToSum
GROUP BY TabToSum.ClientID
PIVOT TabToSum.[Operation Description])

AS [New Crosstab Query]



I didn't work.... Trying to execute it caused an error message to appear -
"Syntax error in FROM clause" - indicating the TRANSFORM word.
Please note, that a substring of the above SQL command between TRANSFORM and
PIVOT sections is a valid SQL query (a crosstab query). If I try another
type of subquery, whose SQL command to retrieve it starts with SELECT
keyword, everything is ok?

Why is it so? How to deal with the problem?

 

[Tom Ellison]

Dear Pablo:

A common way of getting something like this to work in Jet is to save
the query, then make the saved query part of the "outer" SELECT query
you're making. Would this overcome your problem?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Pablo]

A common way of getting something like this to work in Jet is to save
the query, then make the saved query part of the "outer" SELECT query
you're making. Would this overcome your problem?

Thank You,

It WOULD somehow overcome my problem. I am aware of such a way to deal with
the problem of "crosstab queries", but I tried to found more elegant
solution. I have to automate some task relating to mail merge and I would
like to have to press only one button to complete this task.

I understand that there is no other way to overcome my problem, since the
number of columns (and indeed their names) in crosstab queries is not fixed.
That is why Access Mail Merge Wizard for MS Word do not allow crosstab
queries to be the source query for the Word document.

By the way: Why crosstab queries do not allow parameters - i.e. why isn't it
possible to put a parameter name instead of a field name, which is typed on
opening the query. I cannot make out the reasons for which Access designers
disabled such a possibility. It would not disturb its overall operation in
any way! Furthermore, if the crosstab query calls "outer" mere "SELECT"
query with parameters an error occurs, no matter that the SELECT query works
perfectly if opened separately.

Regards,
Pawel

 

[Tom Ellison]

Dear Pablo:

Actually, crosstab queries do permit parameters, but they must be
declared and typed. This is generally not required, but optional for
any other type of query.

I don't see how making a separate query of the crosstab, then using
that as a basis for other queries, would mean someone would have to
press more than one button to operate it. Writing a query that
references another query results in the same seamless operation as any
single query would use.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Pablo]

Actually, crosstab queries do permit parameters, but they must be

declared and typed. This is generally not required, but optional for
any other type of query.

Thanks, I'll try to find out how to do that.

I don't see how making a separate query of the crosstab, then using
that as a basis for other queries, would mean someone would have to
press more than one button to operate it.

Right! I should have expressed my thoughts more precisely.

Writing a query that
references another query results in the same seamless operation as any
single query would use.

Oh, I see... I was close to such an explanation, but it was you, who had
pointed this out before I did.

Thank a lot for the time you contributed to my problems!


Regards,
Pawel

 

[Tom Ellison]

Dear Pablo:

Sorry, I was trying to contribute to the solution, not to the problem!
Hope this is all working out for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Gary Walter]

I never met a problem I couldn't contribute to?

 

[Gary Walter]

I never met a problem I couldn't contribute to?

Actually, I think that was the "prime directive" of every
committee I have had the "opportunity" to participate in.

 

[Pablo]

Sorry, I was trying to contribute to the solution, not to the problem!
Hope this is all working out for you.

Yes, right! Again, I didn't expressed my thoughts clearly...