Problem with joins - not all records shown

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
 
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...
 
Back
Top