Query assistance with multiple tables and get all info

[cjsorel]

I am creating a Contract information database and have broken information into multiple tables as to no have duplicate addresses for Company and contacts. Since there are multiple contacts for each company coule have the same address as the company.

I have run into a problem trying to pull the information for a report. The report will show all current information for a user to fill out a Contract Request Form. The tables I have are:
Contract Register - contains the bulk of the contract information
Company - Company name and addressid (reference to address table)
Contact - Contact name, title, phone, etc and addressid (reference to address table)-This is where I am having a problem as soon as I add the address for contact. I am only getting records that have a record in the address table. If the contact does not have anything in the address table then the addressid is 0.

Here is my current Select statement:
SELECT Contact.ContactID, Prefix.PrefixDesc, Contact.FirstName, Contact.LastName, Contact.Title, Contact.Phone, Contact.PhoneExtension, Contact.Email, Address.Address1, Address.Address2, Address.City, State.State, Address.PostalCode, Address.Country
FROM (State INNER JOIN (Address INNER JOIN Contact ON Address.AddressID = Contact.AddressID) ON State.StateID = Address.StateID) INNER JOIN Prefix ON Contact.PrefixID = Prefix.PrefixID;

 

[Golom]

I think that you need to re-arrange your joins as follows:

FROM (Contact LEFT JOIN (Address INNER JOIN State ON State.StateID = Address.StateID) ON Contact.AddressID = Address.AddressID) INNER JOIN Prefix ON Contact.PrefixID = Prefix.PrefixID;

This will force retrieval of every contact in the Contact table, even if there is no address for that contact. The value of AddressID is irrelevant in the instances where it does not exist in the Address table. You will simply get NULL values for the fields that originate in the Address or State tables.

 

[cjsorel]

Golom,
Thank you for the information but I tried this in Access XP and get a error 'Join expression not supported'