MS Access 2013 Nested Queries

Question

I have two queries that form a third query (below). This is currently working, but I need the whole lot in a single sql statement. I have simplified the naming, but this is effectively what I have:

SELECT Query1.Field1, Query2.Field2
FROM Query1 LEFT JOIN Query2 ON Query1.PK1 = Query2.PK2
ORDER BY Query1.Field1;

Query1 has four linked tables due to the Criteria used and Query2 is a many to many relationship to Query 1 containing 2 tables. I needed to create two separate queries because of the location of the LEFT JOIN - I kept getting ambiguous join errors if one big query was used.

I have tried aliasing the two queries and using brackets, but am stuck. The error I get is "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'.

Query1: (SELECT ....)
FROM ....
Query2: (SELECT ....)
FROM ....
WHERE ....
ORDER BY ....
SELECT [Query1].Field1, [Query2].Field2
FROM [Query1] LEFT JOIN [Query2] ON [Query1].Field1 = [Query2].Field2;

Mihai Ovidiu Drăgoi Mihai Ovidiu Drăgoi · Accepted Answer · 2017-02-28T08:24:04

Try the following:

SELECT [Query1].Field1, [Query2].Field2
FROM 
(
paste first query here
) [Query1]
LEFT JOIN 
(
paste second query here
) [Query2] ON [Query1].Field1 = [Query2].Field2;

Please do not replace Query1 and Query2 as names. They are used as aliases.

Hope it helps!

MS Access 2013 Nested Queries

1 Answers