Full Outer Join -- How to?

[WesHarding]

Hello!



I have encountered a stage in my Access database where I will require to be capable of seeing all information from two tables (that do in many cases have linked data via a primary-secondary key relationship) without null values from either side being omitted. After some research, it looks like a full outer join is what i'm looking to do.

I have looked for some tips on how to do this, and from what I tried I didn't get the results I was hoping for, as in my case: farms that did not have contact names yet did not show up in the query results.

This is the SQL I tried through help from the Microscoft help website. Hope to hear some insight on where I went a stray!

SELECT tblContacts.FirstName1,
tblContacts.MiddleName,
tblContacts.LastName1,
tblWHO.FarmName,
tblWHO.Phone1C1,
tblWHO.Phone2C1,
tblWHO.FarmCivicAddress,
tblWHO.FarmCommunity,
tblWHO.FarmPostalCode,
tblWHO.Province,
tblWHO.Email,
tblWHO.Website,
tblWHO.Fax,
tblWHO.Facebook,
tblWHO.DateofEntry,
tblWHO.Notes,
tblWHO.RR,
tblWHO.VERIFIED,
tblWHO.RegisteredBusiness,
tblWHO.RegisteredFarm,
tblWHO.Export,
tblWHO.Export,
tblWHO.Municipality_WHO,
tblWHO.FarmerID
FROM tblWHO LEFT JOIN tblContacts ON tblWHO.FarmerID = tblContacts.FarmerContactID
WHERE (((tblContacts.FirstName1) Like "*" & [Forms]![MainSearchForm]![txtfirst] & "*") AND ((tblContacts.LastName1) Like "*" & [Forms]![MainSearchForm]![txtlast] & "*") AND ((tblWHO.FarmName) Like "*" & [Forms]![MainSearchForm]![txtfarm] & "*") AND ((tblWHO.FarmCivicAddress) Like "*" & [Forms]![MainSearchForm]![txtaddress] & "*") AND ((tblWHO.FarmCommunity) Like "*" & [Forms]![MainSearchForm]![txtcommunity] & "*") AND ((tblWHO.Municipality_WHO) Like "*" & [Forms]![MainSearchForm]![txtmunicipality] & "*"));
UNION
SELECT tblContacts.FirstName1,
tblContacts.MiddleName,
tblContacts.LastName1,
tblWHO.FarmName,
tblWHO.Phone1C1,
tblWHO.Phone2C1,
tblWHO.FarmCivicAddress,
tblWHO.FarmCommunity,
tblWHO.FarmPostalCode,
tblWHO.Province,
tblWHO.Email,
tblWHO.Website,
tblWHO.Fax,
tblWHO.Facebook,
tblWHO.DateofEntry,
tblWHO.Notes, tblWHO.RR,
tblWHO.VERIFIED,
tblWHO.RegisteredBusiness,
tblWHO.RegisteredFarm,
tblWHO.Export,
tblWHO.Export,
tblWHO.Municipality_WHO,
tblWHO.FarmerID
FROM tblWHO RIGHT JOIN tblContacts ON tblWHO.FarmerID =tblContacts.FarmerContactID
WHERE (((tblContacts.FirstName1) Like "*" & [Forms]![MainSearchForm]![txtfirst] & "*") AND ((tblContacts.LastName1) Like "*" & [Forms]![MainSearchForm]![txtlast] & "*") AND ((tblWHO.FarmName) Like "*" & [Forms]![MainSearchForm]![txtfarm] & "*") AND ((tblWHO.FarmCivicAddress) Like "*" & [Forms]![MainSearchForm]![txtaddress] & "*") AND ((tblWHO.FarmCommunity) Like "*" & [Forms]![MainSearchForm]![txtcommunity] & "*") AND ((tblWHO.Municipality_WHO) Like "*" & [Forms]![MainSearchForm]![txtmunicipality] & "*"));

Here is the source in which I followed instructions:

https://support.office.com/en-us/art...1-07061a1478f6

Thank you in advance,

Wesley

[aytee111]

This is all very complicated! First get a simple query working before adding all the fields and all the criteria. Can you explain this further?

all information from two tables (that do in many cases have linked data via a primary-secondary key relationship) without null values from either side being omitted

Maybe show us some sample data.

[WesHarding]

Good point- absolutely I will do that.

My main project contains a large list of farms and their owners. However, some farms do not have any associated contacts (as of right now).

I have a search query designed to sort thorugh the records. However, since some farms do not have contacts, the previous join I had between my two tables (tblWHO and tblContacts), caused farms that do not have contacts to not show up in my query.

For now, i'll remove all the extra fields. You're right, it's just making things overly complicated before I get any success.

I'm still looking further into this issue and it seems that Access doesn't directly support full outer joins.

[aytee111]

Still trying to get what you want to see - all farms and their contacts, but if they don't have a contact then you want to still see the farm (a simple left join)? Then you also want to see all contacts that don't have farms? Another simple left join. How then are these two sets of information shown to the user? After getting these two queries working, a UNION will put them both together.

Or to simplify things even further:
q1 - all farms and their contacts
q2 - all farms with no contacts
q3 - all contacts with no farms
q4 - union q1, q2, q3
q5 - q4, add criteria

[pbaldy]

I normally use SQL Server, which supports the FULL OUTER JOIN. Sadly Access does not. The workaround is typically a UNION query that pulls together LEFT, RIGHT and INNER join queries like aytee describes. The LEFT and RIGHT queries are frustrated joins, as produced by the unmatched query wizard. They would have a criteria of

WHERE JoinField Is Null

[WesHarding]

Thank you both very much! The process was not as straight forward as I was hoping, but it worked! Definitely another learning curve with Access. I have now discovered a new hurtle I will try to overcome or possibly start a new thread for. Thanks again

Wesley