Problem with joins - not all records shown

G

Guest

Hi there,

I am trying to build a query from five linked tables: tblAgreement,
tblParty, tblPartyContact, tblRenewalConditions, and tblAssignment. I am
running into a common problem (I've read dozens of other postings complaining
of the same thing) but can't figure out how to fix it: basically the query
will not turn out records if a field in any of the above tables is null. So I
am getting only a partial set of results that meet my criteria.

I suspect the proper "JOIN" language would solve the problem, but I'm new to
Access and just don't get JOINs!

Here's my SQL:

SELECT tblParty.PartyName, tblAgreement.AgName, tblSubsidiary.[Subsidiary
Name], tblAgreement.EffectiveDate, tblRenewalConditions.[Renewal Conditions],
tblAgreement.Comments, tblAssignment.Assignment, tblPartyContact.FirstName,
tblPartyContact.LastName, tblPartyContact.ContactTitle, tblParty.Address1,
tblParty.Address2, tblParty.City, tblParty.State, tblParty.ZipCode,
tblParty.Country
FROM tblRenewalConditions INNER JOIN (tblSubsidiary INNER JOIN
(tblAssignment INNER JOIN (tblPartyContact INNER JOIN (tblParty INNER JOIN
tblAgreement ON tblParty.PartyID = tblAgreement.PartyID) ON (tblParty.PartyID
= tblPartyContact.PartyID) AND (tblPartyContact.ContactID =
tblAgreement.ContactID)) ON tblAssignment.AssignmentID =
tblAgreement.AssignmentID) ON tblSubsidiary.SubsidiaryID =
tblAgreement.SubsidiaryID) ON tblRenewalConditions.RenewalID =
tblAgreement.RenewalID
WHERE (((tblAgreement.[Expired/Terminated])=0) AND ((tblAgreement.Draft)=0)
AND ((tblAgreement.AssignmentID)=2 Or (tblAgreement.AssignmentID)=5));

Or do I maybe need to do a UNION query? If so, would I still need to change
the JOIN language so as not to exclude null values?

Thanks in advance for your help!

Laura Christian
 
J

Jeff Boyce

Open the query in design mode.

Highlight one of the join lines. Double-click, or right-click and select
Properties.

You get a small dialog box that asks how you want the fields joined. The
default is "only show where there's a match in both tables." But you can
pick "show all of TableX, even if TableY doesn't have a matching record", or
vice versa.

Repeat for each join FROM your main table to your joined tables.

Try it now...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
G

Guest

That did it! Thanks so much.

Jeff Boyce said:
Open the query in design mode.

Highlight one of the join lines. Double-click, or right-click and select
Properties.

You get a small dialog box that asks how you want the fields joined. The
default is "only show where there's a match in both tables." But you can
pick "show all of TableX, even if TableY doesn't have a matching record", or
vice versa.

Repeat for each join FROM your main table to your joined tables.

Try it now...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Laura_Christian said:
Hi there,

I am trying to build a query from five linked tables: tblAgreement,
tblParty, tblPartyContact, tblRenewalConditions, and tblAssignment. I am
running into a common problem (I've read dozens of other postings
complaining
of the same thing) but can't figure out how to fix it: basically the query
will not turn out records if a field in any of the above tables is null.
So I
am getting only a partial set of results that meet my criteria.

I suspect the proper "JOIN" language would solve the problem, but I'm new
to
Access and just don't get JOINs!

Here's my SQL:

SELECT tblParty.PartyName, tblAgreement.AgName, tblSubsidiary.[Subsidiary
Name], tblAgreement.EffectiveDate, tblRenewalConditions.[Renewal
Conditions],
tblAgreement.Comments, tblAssignment.Assignment,
tblPartyContact.FirstName,
tblPartyContact.LastName, tblPartyContact.ContactTitle, tblParty.Address1,
tblParty.Address2, tblParty.City, tblParty.State, tblParty.ZipCode,
tblParty.Country
FROM tblRenewalConditions INNER JOIN (tblSubsidiary INNER JOIN
(tblAssignment INNER JOIN (tblPartyContact INNER JOIN (tblParty INNER JOIN
tblAgreement ON tblParty.PartyID = tblAgreement.PartyID) ON
(tblParty.PartyID
= tblPartyContact.PartyID) AND (tblPartyContact.ContactID =
tblAgreement.ContactID)) ON tblAssignment.AssignmentID =
tblAgreement.AssignmentID) ON tblSubsidiary.SubsidiaryID =
tblAgreement.SubsidiaryID) ON tblRenewalConditions.RenewalID =
tblAgreement.RenewalID
WHERE (((tblAgreement.[Expired/Terminated])=0) AND
((tblAgreement.Draft)=0)
AND ((tblAgreement.AssignmentID)=2 Or (tblAgreement.AssignmentID)=5));

Or do I maybe need to do a UNION query? If so, would I still need to
change
the JOIN language so as not to exclude null values?

Thanks in advance for your help!

Laura Christian
Click to expand...
Click to expand...
 
Top