Querying a Query

[TorrediPisa]

Good Morning (or Evening) to everyone!

Pls kindly explain me how it is possible in SQL ACCESS to query the result of another query: For Example if I have:

SELECT FIELD1, FIELD2, FIELD3 FROM TABLE

I want to query the resultset of the above with another query. I know in SQL Server is possible to do like this:

SELECT FIELD2, FIELD3 FROM
(SELECT FIELD1, FIELD2, FIELD3 FROM TABLE) AS TBL

But if I try this in Access it doesn't work.
Could You Help Me in understanding this?

Thank You very much for yr help.
Regards

Tdp

 

[Spenney]

Save your first Query - eg qryTable, then use this as the source of your second query.

IE - SELECT Field1, Field2 Field3 FROM qryTable

 

[Remou]

Then there is this:
SELECT TBL.FIELD2, TBL.FIELD3
FROM [SELECT FIELD1, FIELD2, FIELD3 FROM TABLE2]. AS TBL;

Also subqueries. You will find that PHV (member in these fora) is very hot on all forms of SQL, including subqueries.

 

[LittleSmudge]

If you are finding that

SELECT FIELD2, FIELD3 FROM
(SELECT FIELD1, FIELD2, FIELD3 FROM TABLE) AS TBL

doesn't work in Jet SQL then go back and carefully look at your typing because it is perfectly good JetSQL format.


remou's [ and ]. format only seems to work when there is one subquery in the statement and when you go from the QBE grid to SQL view Access will convert ( to [ and ) to ]. for you.

However, if you have mutliple subqueries you have to use ( and ) for Jet to interpret it. Otherwise you get all sorts of obscure and wrong error messages


You can even do something like

SELECT FieldA1, FieldA2, FieldB2 
FROM ( FieldW As FieldA1, Count(FieldX) As FieldA2
       FROM TableA
       GROUP BY FieldW
     ) As A
INNER JOIN ( FieldY As FieldB1, Max(FieldZ) As FieldB2 
             FROM TableB
             GROUP BY FieldY
           ) As B
ON A.FieldA1 = B.FieldB1
WHERE FieldA1 > 427

Now the above will work in JetSQL
BUT if you put that into a query and then view it in the QBE grid and then look at it again in SQL view it will no longer work because of the changes that the QBE grid will make to it.



'ope-that-'elps.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[TorrediPisa]

Ok Remou,

I tried as you suggested, but I receive an error: incorrect syntax of FROM. Are you sure this is applicable also for MS Access?

Sorry, What is PHV?
Thanks again
Bye
TdP

 

[Golom]

... it's perfectly good SQL ...

IF you are running Access 2K or above. Access 97 does not support in-line sub-queries in the FROM clause.

 

[Remou]

Hi TorrediPisa
If you have not sorted out your query, perhaps you could post your SQL and the version of Access you are using?

(PHV is a member of Tek-Tips, as I said. Of course there are lots of members that are very good at this stuff, it is just a handle that sprang to mind. )

 

[LittleSmudge]

Thank you for that clarification Golom. ;-)