Combine 2 queries

  • Thread starter Thread starter Sei
  • Start date Start date
S

Sei

I have these two queries with the following SQL:
SELECT tbl_LogBasicInfo.logID, tbl_LogBasicInfo.logTypeID,
tbl_LogBasicInfo.logSSN, tbl_LogBasicInfo.clmtID, tbl_LogBasicInfo.clmID,
tbl_LogBasicInfo.rAddID, tbl_LogBasicInfo.mAddID, tbl_ClmtAddress.cAddID,
tbl_ClmtAddress.cAddress, tbl_ClmtAddress.cCity, tbl_ClmtAddress.cState,
tbl_ClmtAddress.cZipCode
FROM tbl_Claimant INNER JOIN (tbl_ClmtAddress INNER JOIN tbl_LogBasicInfo ON
tbl_ClmtAddress.cAddID = tbl_LogBasicInfo.mAddID) ON (tbl_Claimant.clmtID =
tbl_LogBasicInfo.clmtID) AND (tbl_Claimant.clmtID = tbl_ClmtAddress.clmtID);

and

SELECT tbl_LogBasicInfo.logID, tbl_LogBasicInfo.logTypeID,
tbl_LogBasicInfo.logSSN, tbl_LogBasicInfo.clmtID, tbl_LogBasicInfo.clmID,
tbl_LogBasicInfo.mAddID, tbl_LogBasicInfo.rAddID, tbl_ClmtAddress.cAddID,
tbl_ClmtAddress.cAddress, tbl_ClmtAddress.cCity, tbl_ClmtAddress.cState,
tbl_ClmtAddress.cZipCode
FROM tbl_Claimant INNER JOIN (tbl_ClmtAddress INNER JOIN tbl_LogBasicInfo ON
tbl_ClmtAddress.cAddID = tbl_LogBasicInfo.rAddID) ON (tbl_Claimant.clmtID =
tbl_LogBasicInfo.clmtID) AND (tbl_Claimant.clmtID = tbl_ClmtAddress.clmtID);

How do I combine the two?
Thank you,
 
I would try a union query.

SELECT tbl_LogBasicInfo.logID, tbl_LogBasicInfo.logTypeID,
tbl_LogBasicInfo.logSSN, tbl_LogBasicInfo.clmtID, tbl_LogBasicInfo.clmID,
tbl_LogBasicInfo.rAddID, tbl_LogBasicInfo.mAddID, tbl_ClmtAddress.cAddID,
tbl_ClmtAddress.cAddress, tbl_ClmtAddress.cCity, tbl_ClmtAddress.cState,
tbl_ClmtAddress.cZipCode FROM tbl_Claimant INNER JOIN (tbl_ClmtAddress INNER
JOIN tbl_LogBasicInfo ON tbl_ClmtAddress.cAddID = tbl_LogBasicInfo.mAddID) ON
(tbl_Claimant.clmtID = tbl_LogBasicInfo.clmtID) AND (tbl_Claimant.clmtID =
tbl_ClmtAddress.clmtID);

UNION

SELECT tbl_LogBasicInfo.logID, tbl_LogBasicInfo.logTypeID,
tbl_LogBasicInfo.logSSN, tbl_LogBasicInfo.clmtID, tbl_LogBasicInfo.clmID,
tbl_LogBasicInfo.mAddID, tbl_LogBasicInfo.rAddID, tbl_ClmtAddress.cAddID,
tbl_ClmtAddress.cAddress, tbl_ClmtAddress.cCity, tbl_ClmtAddress.cState,
tbl_ClmtAddress.cZipCode FROM tbl_Claimant INNER JOIN (tbl_ClmtAddress INNER
JOIN tbl_LogBasicInfo ON tbl_ClmtAddress.cAddID = tbl_LogBasicInfo.rAddID) ON
(tbl_Claimant.clmtID = tbl_LogBasicInfo.clmtID) AND (tbl_Claimant.clmtID =
tbl_ClmtAddress.clmtID);



NOTE: You will have to change to SQL mode (view) of the query designer to
create this query.


HTH
 
I'm sorry. I may be missing something, but the two queries look identical to
me!
 
I only have one Address table but the Log table is asking for the 'residence'
address and 'mailing' address. So in the log table I have mAddID and rAddID
to store the primary key from the Address table.

For the Mailing query I have this line:
tbl_ClmtAddress.cAddID = tbl_LogBasicInfo.mAddID) ON ...

and for the Residence query I have this line:
tbl_ClmtAddress.cAddID = tbl_LogBasicInfo.rAddID) ON ...

That's about the only line they are different
 
This didnt work. I created a query in SQL view but when i went to datasheet
view, it only displayed all the records with the same mAddID and cAddID. I
also want to display records with the same rAddID and cAddID.

so if my record has both mAddID and rAddID, i want to see 2 results instead
of one. Hopefully, I'm not too confusing. Thanks, Sei.
 
If you want to see the duplicates, you need to use

SELECT...
FROM
WHERE
UNION ALL
SELECT...
FROM
WHERE
 
Back
Top