Combine 2 queries

[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,

 

[Steve Sanford]

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

 

[Douglas J. Steele]

I'm sorry. I may be missing something, but the two queries look identical to
me!

 

[Sei]

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

 

[Sei]

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.

 

[pietlinden]

If you want to see the duplicates, you need to use

SELECT...
FROM
WHERE
UNION ALL
SELECT...
FROM
WHERE

 

[Steve Sanford]

Oops.... I had another PBKAC episode