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

Combining two queries into one

P: 2
I would like to take these two queries and combine them into one if possible. This is the first query:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [LINE 2].[CASE ID] INTO [TABLE 1]
  2. FROM [LINE 2] INNER JOIN NOLDBA_OBLIGATION ON [LINE 2].[CASE ID]=NOLDBA_OBLIGATION.ID_CASE
  3. WHERE (((NOLDBA_OBLIGATION.AMT_PERIODIC)>0) AND ((NOLDBA_OBLIGATION.DT_END_OBLIGATION)>#6/30/2007#) AND ((NOLDBA_OBLIGATION.DT_END_VALIDITY)=#12/31/9999#));
  4.  
And this is the second query which is based on the results of the first query:

Expand|Select|Wrap|Line Numbers
  1. SELECT NOLDBA_CASE_ROLLUP.ID_CASE INTO [TABLE 2]
  2. FROM [LINE 2] INNER JOIN (NOLDBA_CASE_ROLLUP LEFT JOIN [TABLE 1] ON NOLDBA_CASE_ROLLUP.ID_CASE = [TABLE 1].[CASE ID]) ON [LINE 2].[CASE ID] = NOLDBA_CASE_ROLLUP.ID_CASE
  3. WHERE ((([TABLE 1].[CASE ID]) Is Null) AND (([NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_OWED]- [NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_PAID])>0))
  4. GROUP BY NOLDBA_CASE_ROLLUP.ID_CASE;
  5.  
Can this be done and if yes can someone show me how? Thanks
Aug 3 '07 #1
Share this Question
Share on Google+
3 Replies


Stang02GT
Expert 100+
P: 1,208
You can do this using a UNION.

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [LINE 2].[CASE ID] INTO [TABLE 1]
  2. FROM [LINE 2] INNER JOIN NOLDBA_OBLIGATION ON [LINE 2].[CASE ID]=NOLDBA_OBLIGATION.ID_CASE
  3. WHERE (((NOLDBA_OBLIGATION.AMT_PERIODIC)>0) AND ((NOLDBA_OBLIGATION.DT_END_OBLIGATION)>#6/30/2007#) AND ((NOLDBA_OBLIGATION.DT_END_VALIDITY)=#12/31/9999#))
  4. UNION SELECT NOLDBA_CASE_ROLLUP.ID_CASE INTO [TABLE 2]
  5. FROM [LINE 2] INNER JOIN (NOLDBA_CASE_ROLLUP LEFT JOIN [TABLE 1] ON NOLDBA_CASE_ROLLUP.ID_CASE = [TABLE 1].[CASE ID]) ON [LINE 2].[CASE ID] = NOLDBA_CASE_ROLLUP.ID_CASE
  6. WHERE ((([TABLE 1].[CASE ID]) Is Null) AND (([NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_OWED]- [NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_PAID])>0))
  7. GROUP BY NOLDBA_CASE_ROLLUP.ID_CASE;
  8.  
Aug 3 '07 #2

P: 2
I tried and get the error "an action query cannot be used as a row source" Any other ideas?

You can do this using a UNION.


Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [LINE 2].[CASE ID] INTO [TABLE 1]
  2. FROM [LINE 2] INNER JOIN NOLDBA_OBLIGATION ON [LINE 2].[CASE ID]=NOLDBA_OBLIGATION.ID_CASE
  3. WHERE (((NOLDBA_OBLIGATION.AMT_PERIODIC)>0) AND ((NOLDBA_OBLIGATION.DT_END_OBLIGATION)>#6/30/2007#) AND ((NOLDBA_OBLIGATION.DT_END_VALIDITY)=#12/31/9999#));[/b]
  4.  
  5. UNION SELECT NOLDBA_CASE_ROLLUP.ID_CASE INTO [TABLE 2]
  6. FROM [LINE 2] INNER JOIN (NOLDBA_CASE_ROLLUP LEFT JOIN [TABLE 1] ON NOLDBA_CASE_ROLLUP.ID_CASE = [TABLE 1].[CASE ID]) ON [LINE 2].[CASE ID] = NOLDBA_CASE_ROLLUP.ID_CASE
  7. WHERE ((([TABLE 1].[CASE ID]) Is Null) AND (([NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_OWED]-[NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_PAID])>0))
  8. GROUP BY NOLDBA_CASE_ROLLUP.ID_CASE;
  9.  
  10.  
  11.  
Aug 3 '07 #3

Stang02GT
Expert 100+
P: 1,208
Maybe try an INNER JOIN....here is a link that will give you all you need to know about Joins and unions along with the correct syntax for creating the sql statement


Click Here
Aug 3 '07 #4

Post your reply

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