By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,569 Members | 1,386 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,569 IT Pros & Developers. It's quick & easy.

help with main query using two inner joined subqueries

P: 27
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!
Dec 6 '06 #1
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Dec 6 '06 #2

NeoPa
Expert Mod 15k+
P: 31,476
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' :
Expand|Select|Wrap|Line Numbers
  1. SELECT subQ.* FROM (SELECT * FROM tblThis) AS subQ
This is just some further clarification on the subquery part.
Dec 7 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.