Help! I can't understand why this won't work!

S

Sue

I have designed a contacts database which contains names of both personal &
business contacts. To avoid data entry errors, I have several tables which
relate to field in my tblContacts:

tblContactType:
ContactTypeID Autonumber
ContactType Text

tblCity
CityID Autonumber
City Text

tblState/Provinc
State/ProvinceID Autonumber
State/Province Text

tblZip/PostalCode
Zip/PostalCodeID Autonumber
Zip/PostalCode Text

tblContacts
ContactID Autonumber
ContactType
LastName Text
FirstName Text
HomeAddress Text
HomeCity Number Foreign key from
tblCity, relationship established enforcing referential integrity
HomeState/Province Number Foreign key from
tblState/Province, relationship established enforcing referential integrity
HomeZip/PostalCode Number Foreign key from
tblZip/PostalCode, relationship established enforcing referential integrity
BusinessAddress Text
BusinessCity Number Foreign key from
tblCity, relationship established enforcing referential integrity
BusinessState/Province Number Foreign key from
tblState/Province, relationship established enforcing referential integrity
BusinessZip/PostalCode Number Foreign key from
tblZip/PostalCode, relationship established enforcing referential integrity

I tried to develop a query that would allow me to pull out address
information for certain contact types. I found my queries returning void, so
I eliminated all but bare bones - just ContactType, LastName, FirstName -
and I find that I only return a small number out of a list of 700+ contacts.

In the design view of the query, I see relationship lines drawn for city,
state/province, zip/postal code, & country for both business and personal
adddresses. I assume that's the problem - that Access is returning only
those contacts where I have BOTH a business & a personal address. I've
pasted the SQL statement here. How can I get info for all contacts - those
with both personal & home address info, and those with neither (i.e. I have
some contacts that I only have a phone number or an email address for)?



SELECT tblContactType.ContactType, tblContacts.LastName,
tblContacts.FirstName
FROM [tblZip/PostalCode] AS [tblZip/PostalCode_1] INNER JOIN
([tblState/Province] AS [tblState/Province_1] INNER JOIN (tblCountry AS
tblCountry_1 INNER JOIN (tblContactType INNER JOIN (tblCity AS tblCity_1
INNER JOIN (tblCity INNER JOIN ([tblZip/PostalCode] INNER JOIN
([tblState/Province] INNER JOIN (tblCountry INNER JOIN tblContacts ON
tblCountry.Country_ID = tblContacts.HomeCountry) ON
[tblState/Province].[State/Province_ID] = tblContacts.[HomeState/Province])
ON [tblZip/PostalCode].[Zip/PostalCode_ID] = tblContacts.HomeZip) ON
tblCity.City_ID = tblContacts.HomeCity) ON tblCity_1.City_ID =
tblContacts.BusinessCity) ON tblContactType.ContactTypeID =
tblContacts.ContactType) ON tblCountry_1.Country_ID =
tblContacts.BusinessCountry) ON [tblState/Province_1].[State/Province_ID] =
tblContacts.[BusinessState/Province]) ON
[tblZip/PostalCode_1].[Zip/PostalCode_ID] = tblContacts.BusinessZip
ORDER BY tblContactType.ContactType, tblContacts.LastName,
tblContacts.FirstName;
 
D

Duane Hookom

Open your query in design view and double click some join lines. Set the
property/options to include all records from your main table.
--
Duane Hookom
Microsoft Access MVP


Sue said:
I have designed a contacts database which contains names of both personal &
business contacts. To avoid data entry errors, I have several tables which
relate to field in my tblContacts:

tblContactType:
ContactTypeID Autonumber
ContactType Text

tblCity
CityID Autonumber
City Text

tblState/Provinc
State/ProvinceID Autonumber
State/Province Text

tblZip/PostalCode
Zip/PostalCodeID Autonumber
Zip/PostalCode Text

tblContacts
ContactID Autonumber
ContactType
LastName Text
FirstName Text
HomeAddress Text
HomeCity Number Foreign key from
tblCity, relationship established enforcing referential integrity
HomeState/Province Number Foreign key from
tblState/Province, relationship established enforcing referential integrity
HomeZip/PostalCode Number Foreign key from
tblZip/PostalCode, relationship established enforcing referential integrity
BusinessAddress Text
BusinessCity Number Foreign key from
tblCity, relationship established enforcing referential integrity
BusinessState/Province Number Foreign key from
tblState/Province, relationship established enforcing referential integrity
BusinessZip/PostalCode Number Foreign key from
tblZip/PostalCode, relationship established enforcing referential integrity

I tried to develop a query that would allow me to pull out address
information for certain contact types. I found my queries returning void, so
I eliminated all but bare bones - just ContactType, LastName, FirstName -
and I find that I only return a small number out of a list of 700+ contacts.

In the design view of the query, I see relationship lines drawn for city,
state/province, zip/postal code, & country for both business and personal
adddresses. I assume that's the problem - that Access is returning only
those contacts where I have BOTH a business & a personal address. I've
pasted the SQL statement here. How can I get info for all contacts - those
with both personal & home address info, and those with neither (i.e. I have
some contacts that I only have a phone number or an email address for)?



SELECT tblContactType.ContactType, tblContacts.LastName,
tblContacts.FirstName
FROM [tblZip/PostalCode] AS [tblZip/PostalCode_1] INNER JOIN
([tblState/Province] AS [tblState/Province_1] INNER JOIN (tblCountry AS
tblCountry_1 INNER JOIN (tblContactType INNER JOIN (tblCity AS tblCity_1
INNER JOIN (tblCity INNER JOIN ([tblZip/PostalCode] INNER JOIN
([tblState/Province] INNER JOIN (tblCountry INNER JOIN tblContacts ON
tblCountry.Country_ID = tblContacts.HomeCountry) ON
[tblState/Province].[State/Province_ID] = tblContacts.[HomeState/Province])
ON [tblZip/PostalCode].[Zip/PostalCode_ID] = tblContacts.HomeZip) ON
tblCity.City_ID = tblContacts.HomeCity) ON tblCity_1.City_ID =
tblContacts.BusinessCity) ON tblContactType.ContactTypeID =
tblContacts.ContactType) ON tblCountry_1.Country_ID =
tblContacts.BusinessCountry) ON [tblState/Province_1].[State/Province_ID] =
tblContacts.[BusinessState/Province]) ON
[tblZip/PostalCode_1].[Zip/PostalCode_ID] = tblContacts.BusinessZip
ORDER BY tblContactType.ContactType, tblContacts.LastName,
tblContacts.FirstName;
Click to expand...
 
S

Sue

Perfect - thanks.
I find I have tremendous difficulty wrapping my head around when to use
inner vs outer joins.


Duane Hookom said:
Open your query in design view and double click some join lines. Set the
property/options to include all records from your main table.
--
Duane Hookom
Microsoft Access MVP


Sue said:
I have designed a contacts database which contains names of both personal
&
business contacts. To avoid data entry errors, I have several tables
which
relate to field in my tblContacts:

tblContactType:
ContactTypeID Autonumber
ContactType Text

tblCity
CityID Autonumber
City Text

tblState/Provinc
State/ProvinceID Autonumber
State/Province Text

tblZip/PostalCode
Zip/PostalCodeID Autonumber
Zip/PostalCode Text

tblContacts
ContactID Autonumber
ContactType
LastName Text
FirstName Text
HomeAddress Text
HomeCity Number Foreign key from
tblCity, relationship established enforcing referential integrity
HomeState/Province Number Foreign key from
tblState/Province, relationship established enforcing referential
integrity
HomeZip/PostalCode Number Foreign key from
tblZip/PostalCode, relationship established enforcing referential
integrity
BusinessAddress Text
BusinessCity Number Foreign key from
tblCity, relationship established enforcing referential integrity
BusinessState/Province Number Foreign key from
tblState/Province, relationship established enforcing referential
integrity
BusinessZip/PostalCode Number Foreign key from
tblZip/PostalCode, relationship established enforcing referential
integrity

I tried to develop a query that would allow me to pull out address
information for certain contact types. I found my queries returning void,
so
I eliminated all but bare bones - just ContactType, LastName, FirstName -
and I find that I only return a small number out of a list of 700+
contacts.

In the design view of the query, I see relationship lines drawn for city,
state/province, zip/postal code, & country for both business and personal
adddresses. I assume that's the problem - that Access is returning only
those contacts where I have BOTH a business & a personal address. I've
pasted the SQL statement here. How can I get info for all contacts -
those
with both personal & home address info, and those with neither (i.e. I
have
some contacts that I only have a phone number or an email address for)?



SELECT tblContactType.ContactType, tblContacts.LastName,
tblContacts.FirstName
FROM [tblZip/PostalCode] AS [tblZip/PostalCode_1] INNER JOIN
([tblState/Province] AS [tblState/Province_1] INNER JOIN (tblCountry AS
tblCountry_1 INNER JOIN (tblContactType INNER JOIN (tblCity AS tblCity_1
INNER JOIN (tblCity INNER JOIN ([tblZip/PostalCode] INNER JOIN
([tblState/Province] INNER JOIN (tblCountry INNER JOIN tblContacts ON
tblCountry.Country_ID = tblContacts.HomeCountry) ON
[tblState/Province].[State/Province_ID] =
tblContacts.[HomeState/Province])
ON [tblZip/PostalCode].[Zip/PostalCode_ID] = tblContacts.HomeZip) ON
tblCity.City_ID = tblContacts.HomeCity) ON tblCity_1.City_ID =
tblContacts.BusinessCity) ON tblContactType.ContactTypeID =
tblContacts.ContactType) ON tblCountry_1.Country_ID =
tblContacts.BusinessCountry) ON [tblState/Province_1].[State/Province_ID]
=
tblContacts.[BusinessState/Province]) ON
[tblZip/PostalCode_1].[Zip/PostalCode_ID] = tblContacts.BusinessZip
ORDER BY tblContactType.ContactType, tblContacts.LastName,
tblContacts.FirstName;
Click to expand...
Click to expand...
 
J

John W. Vinson

I find I have tremendous difficulty wrapping my head around when to use
inner vs outer joins.
Click to expand...

Use an Inner Join when you want to see data from both tables - and when data
exists in both tables.

Use an Outer Join when you always want to see data from one table (the
preserved table) whether or not there might be data from the other table.

John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with normalization problem. 1

Top