Combining result of two queries (1 Viewer)

[zeeforel]

Dear people out here,

I am trying to combine the result of two specific (and different) queries on a set of loans. As a result I have to sets of query results:
+ result query 1: with first field LoanID and several other fields
+ result query 2: with first field LoanID and several other fields

Now I want to create a list of the combination of all LoanID's, without duplicates.

How do I do that? I read about UNION but that appears to work only on tables.

Many thanks in advance.

Marcel
Netherlands

 

[plog]

Why does it appear that way? A datasource is a datasource. A UNION query can be used on either, or both.

However, UNION queries are a sign that you aren't doing things correctly. Most likely query1 and query 2 can be combined into 1 query statement from the get go. What does your SQL for both of those queries look like?

 

[zeeforel]

Hi Plog,

Thanks for responding so quickly.

At first I did try to combine all querying and filtering in one query but that resulted in strange things and above all was not that transparent. The seperate queries are used to identify loanid's that need to go from a certain portfolio based on different criteria (different fields and filters).

By the way, here are the two separate SQL pieces:

SELECT Borrowers.Loannumber, [OLB voor Interest Only].SumOfOriginal_Loan_Balance, Borrowers.Guarantee_Indication, [Max origination datum].MaxOfOrigination_Date, Borrowers.Appraisal_Value, Round(([SumOfOriginal_Loan_Balance]/[Appraisal_Value]),2) AS [IO%FV], Borrowers.Original_Market_Value, Round(([OLB voor Interest Only]![SumOfOriginal_Loan_Balance]/[Borrowers]![Original_Market_Value]),2) AS [IO%OMV], Borrowers.OLTOFV, Borrowers.OLTOMV
FROM (Borrowers INNER JOIN [Max origination datum] ON Borrowers.Loannumber = [Max origination datum].Borrower_ID) LEFT JOIN [OLB voor Interest Only] ON Borrowers.Loannumber = [OLB voor Interest Only].Borrower_ID
WHERE (((Borrowers.Guarantee_Indication) Is Null) AND (([Max origination datum].MaxOfOrigination_Date)<#8/1/2011#) AND ((Borrowers.OLTOFV)>1.25)) OR (((Borrowers.Guarantee_Indication) Is Null) AND (([Max origination datum].MaxOfOrigination_Date) Between #8/1/2011# And #12/31/2012#) AND ((Borrowers.OLTOMV)>1.06)) OR (((Borrowers.Guarantee_Indication) Is Null) AND (([Max origination datum].MaxOfOrigination_Date) Between #1/1/2013# And #12/31/2013#) AND ((Borrowers.OLTOMV)>1.05)) OR (((Borrowers.Guarantee_Indication) Is Null) AND (([Max origination datum].MaxOfOrigination_Date)>=#1/1/2014#) AND ((Borrowers.OLTOMV)>1.04));

and

SELECT Borrowers.Loannumber, [OLB voor Interest Only].SumOfOriginal_Loan_Balance, Borrowers.Guarantee_Indication, [Max origination datum].MaxOfOrigination_Date, Borrowers.Appraisal_Value, Round(([SumOfOriginal_Loan_Balance]/[Appraisal_Value]),2) AS [IO%FV], Borrowers.Original_Market_Value, Round(([OLB voor Interest Only]![SumOfOriginal_Loan_Balance]/[Borrowers]![Original_Market_Value]),2) AS [IO%OMV], Borrowers.OLTOFV, Borrowers.OLTOMV
FROM (Borrowers INNER JOIN [Max origination datum] ON Borrowers.Loannumber = [Max origination datum].Borrower_ID) LEFT JOIN [OLB voor Interest Only] ON Borrowers.Loannumber = [OLB voor Interest Only].Borrower_ID
WHERE ((([Max origination datum].MaxOfOrigination_Date)<#8/1/2011#) AND ((Round(([SumOfOriginal_Loan_Balance]/[Appraisal_Value]),2))>0.75)) OR ((([Max origination datum].MaxOfOrigination_Date)>=#8/1/2011#) AND ((Round(([OLB voor Interest Only]![SumOfOriginal_Loan_Balance]/[Borrowers]![Original_Market_Value]),2))>0.5));

Kind of complex if you ask me, therefore I tried to seperate and now need to combine. Looking forward to any help.

Thanks very much.

 

[Sketchin]

I am trying to do something similar. I have 2 queries that have 2 fields, companyID and CompanyName. I want to combine the 2 to give me a resulting list with no duplicates.

Sorry to hijack, I am just wanting to jump in on the answer here!

 

[plog]

I would use a simple union query (http://www.techonthenet.com/sql/union.php). Let's assume the first query is named Q1 and the second Q2. This SQL would provide you with a list of unique account numbers in both of them:

SELECT Loannumber FROM Q1
UNION
SELECT Loannumber FROM Q2;

That will provide you with the unique Loannumber values. If you wanted all the data, regarldess of duplicates, you would replace 'UNION' with 'UNION ALL'.

 

[Sketchin]

I was thinking that would work for me...thanks!

 

[zeeforel]

I did that exact recipe earlier but then Access seemed to skip the where clauses from both seperate queries...

Will try again today.

 

[plog]

How do you know that? Did you run the UNION query and find an account number not in either query?