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