How to include null values in query

[ittechguy]

I have a table in my database which stores customer information. Information that is rarely duplicated (LastName, FirstName, Phone, and Email) has its own separate field set as text. Information which would contain lots of duplicates has been broken apart into separate tables to avoid duplicate and reduce/eliminate confusion. That is, Organization, ShopName, OfficeSymbol, and Rank.



On a search form, I have list boxes which get their data based on a query of all of the data in tblCustomers as well as tblOrganization, tblShopName, tblOfficeSym, and tblRank (so that actual text is displayed, and not numbers). The problem I'm having is that all fields in one row of tblCustomers can be filled in, or there can be null values. The most common example is if it is for a particular Organization/Company, Shop, and/or Office Symbol... there would not be a first name, last name, or phone number so all those fields are blank. Also although most do have an office symbol, some do not.

Currently, the query I have does not display all records if there is a field in the query that contains a null value. How can I avoid this? I've been trying to allow null values, but clearly I'm doing something wrong. Also, I question if this is a sign of a un-normalized database. I hope not as I do not want to further complicate the database design by changing tables and relationships.

Here is the SQL for my query:

SELECT tblOrganization.OrganizationName, tblShopName.ShopName, tblOfficeSym.OfficeSym, tblRank.Rank, tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.PhoneNo, tblCustomer.Email
FROM tblShopName
INNER JOIN (tblRank INNER JOIN (tblOrganization INNER JOIN (tblOfficeSym INNER JOIN (tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK)
ON tblOfficeSym.OfficeSymPK = tblCustomer.OfficeSymFK) ON tblOrganization.OrganizationPK = tblCustomer.OrganizationFK)
ON tblRank.RankPK = tblCustomer.RankFK) ON tblShopName.ShopNamePK = tblCustomer.ShopNameFK;

[JamesDeckert]

If some records don't display, it's probably because of your join between the tables. You are using an INNER JOIN (the default) which will only show records if they are in both tables which are joined together. So if you have a customer table and an orders table and you query the data with an INNER JOIN you'll only see customers who have orders. If you want to see customers even if they don't have orders you need to right click the join line in the query and select to see all customers and their matching orders.

[ittechguy]

Thanks JamesDeckert!

I guess my troubleshooting skills need some work. It seemed to me like it wasn't handing null values when dealing w/ numbers and not text. I changed the INNER join to RIGHT join, and now it works great!