Access query with subquery to SQL statement 1

[suzielssma]

I have a working Access 2000 query that generated the following SQL statement:

SELECT zones.name, wo.id, clients.company, wo.closed, users.last, wo.notes, wo.quick, wo.created, [LSSMA-WO-Sub].start, [LSSMA-WO-Sub].stop, [LSSMA-WO-Sub].tech, [LSSMA-WO-Sub].details
FROM (zones INNER JOIN (clients INNER JOIN (users INNER JOIN wo ON users.id = wo.assigntech) ON clients.id = wo.client) ON zones.id = clients.czone) LEFT JOIN [LSSMA-WO-Sub] ON wo.id = [LSSMA-WO-Sub].wolink
WHERE ((([wo]![closed]) Between #5/1/2003# And #5/19/2003#))
ORDER BY zones.name, wo.id;

The referenced subquery LSSMA-WO-Sub generated the following:

SELECT [labor].[start], [labor].[stop], [labor].[details], [labor].[tech], [probs].[wolink]
FROM probs INNER JOIN labor ON [probs].[id]=[labor].[link];

How do I combine those into one SQL statement? I need to insert it in a table used by an application for report generation.
Thanks.

 

[DanChard]

Give this a go:

SELECT zones.name, wo.id, clients.company, wo.closed, users.last, wo.notes, wo.quick, wo.created, [LSSMA-WO-Sub].start, [LSSMA-WO-Sub].stop, [LSSMA-WO-Sub].tech, [LSSMA-WO-Sub].details
FROM (zones INNER JOIN (clients INNER JOIN (users INNER JOIN wo ON users.id = wo.assigntech) ON clients.id = wo.client) ON zones.id = clients.czone) LEFT JOIN (SELECT [labor].[start], [labor].[stop], [labor].[details], [labor].[tech], [probs].[wolink]
FROM probs INNER JOIN labor ON [probs].[id]=[labor].[link]) as [LSSMA-WO-Sub] ON wo.id = [LSSMA-WO-Sub].wolink
WHERE ((([wo]![closed]) Between #5/1/2003# And #5/19/2003#))
ORDER BY zones.name, wo.id;

Can't say if it will work but it shouldn't be far off!

 

[suzielssma]

Dan,
It worked exactly as it did with the subquery. Thank you for taking the time to respond. I can also use it as a template for future queries.