Problem combining SELECT queries

[Laura McKittrick]

Hello, all,

I worked out a set of queries for a report, and now I'm putting them
together into a single module.

Three of these are related SELECT queries. The first two extract data
from a parent and a child table based on different criteria, and the
third combines them into one table with a LEFT JOIN. To assemble them
in the module, the first two need to be converted into subqueries
within one big SELECT statement. The queries look sort of like this:

Query 1: SELECT * FROM parent WHERE date1 < startdate

QUERY 2: SELECT * FROM child WHERE date2 < startdate

QUERY 3: SELECT * FROM query1, query2 LEFT JOIN ON query1.id =
query2.id

They work fine as separate queries, but I'm not having any success
combining them into one big query in my module. Can anyone help?

Thanks very much in advance!

Laura

 

[John Spencer (MVP)]

Depending on your version of Access and your actual table and field names, the
following may work for you

SELECT *
FROM (SELECT * FROM Parent WHERE Date1 <StartDate) as A
LEFT JOIN (SELECT * FROM child WHERE date2 < startdate) as B

 

[Laura]

Works, thanks! Seems my problem was not using the aliases.