TRANSFORM and subqueries

[Aristarco]

I've tried to make a subquery with a transform query inside but it seems it's not working. Does anyone know if this is a forbidden/non-implemented thing in Jet SQL?

My code look like this:

SELECT FQ.AnyField 
FROM [TRANSFORM FieldX SELECT blahblahblah... PIVOT yaddayadda... ]. AS FQ

To boldly code, where no programmer has compiled before!

 

[lespaul]

what are you trying to accomplish with this?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Aristarco]

I have sort of an inventory of codes. Every code belongs to a group of records belonging to different types. Lets say code SC_025 has some records of "Works" type, one of "Actions" type and a couple of "Projects" type. The transform query results in something like this:

Code | Actions | Works | Projects
-------+-----------+----------+-----------
SC_025 | 1 | 4 | 2
SD_234 | 2 | | 1
ED_546 | 5 | |

Code in the external query calling the internal 'Transform' query checks the quantity of records of each type to determine the type of Code. In this case, SC_025, having majority of "Works" is a "Works" code. One having majority of "Actions" and no "Works" or "Projects" is a "Termination" code, and so on.

I make this in two steps. First make the Transform query and then the query that uses it to create a table. I'd like to make it in one step. But I think it can't be done now. Can it?

To boldly code, where no programmer has compiled before!