crosstab query as a subquery? how to do that?

  • Thread starter Thread starter Pablo
  • Start date Start date
P

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?
 
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
 
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?
Click to expand...

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
 
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
 
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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
 
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
 
I never met a problem I couldn't contribute to?
Click to expand...
Actually, I think that was the "prime directive" of every
committee I have had the "opportunity" to participate in.
 
Sorry, I was trying to contribute to the solution, not to the problem!
Hope this is all working out for you.
Click to expand...

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