help with main query using two inner joined subqueries

[psuaudi]

I have a main query that I would like to call two different subqueries.

In MS Access, I usually just save the two subqueries as separate queries which are then called by a third separate and main query. However, I'd like to put them all into one SQL command. Is this possible? Here are the queries:

-This query calls the other two queries below-
SELECT [Step 1].[Serial #], [Step 1].[Sub#], Format((([Step 2].Date)-([Step 1].Date)),"Fixed") AS [Time], [Step 1].Type
FROM [Step 1] INNER JOIN [Step 2] ON ([Step 1].[Serial #] = [Step 2].[Serial #]) AND ([Step 1].[Sub#] = [Step 2].[Sub#]);


Step 1
SELECT [Main Table].[Serial #], [Main Table].[Sub#], [Moving Info Table].Date, [Main Table].Type
FROM [Main Table] INNER JOIN [Moving Info Table] ON ([Main Table].[Sub#] = [Moving Info Table].[Sub#]) AND ([Main Table].[Serial #] = [Moving Info Table].[Serial #])
WHERE ((([Main Table].Type)="A" Or ([Main Table].Type)="O") AND (([Moving Info Table].[Status In])="Removal"));


Step 2
SELECT [Main Table].[Serial #], [Main Table].[Sub#], [Moving Info Table].Date, [Main Table].Type
FROM [Main Table] INNER JOIN [Moving Info Table] ON ([Main Table].[Sub#] = [Moving Info Table].[Sub#]) AND ([Main Table].[Serial #] = [Moving Info Table].[Serial #])
WHERE ((([Main Table].Type)="A" Or ([Main Table].Type)="O") AND (([Moving Info Table].[Status Out])="Quality"));


Any help or suggestions would be appreciated. Thank you!

[nico5038]

I would start with skipping the space from the table and query name.
Personally I use a "qry" prefix for queries and "tbl" for tables to know where to find the origin.

In your case I would get:

qryMain:
select B.field1, B.field1
from qryStep1 A JOIN qryStep2 B (JOIN...)

When the above query works, you can simply replace the qryStep1 by the original query like:

qryMain:
select B.field1, B.field1
from (select field1 from tblStep1) A JOIN (select field1 from tblStep2) B (JOIN...)

Getting the idea ?

Nic;o)

[NeoPa]

Sub-queries can be used within SQL using parentheses surrounding the sub-query.
Assigning a name to the sub-query is usual if referring to a field is required.
A simple display query which is equivalent to displaying a table 'tblThis' :

SELECT subQ.* FROM (SELECT * FROM tblThis) AS subQ

This is just some further clarification on the subquery part.