Mail Merge Query

[grep]

Long post - sorry, but I'm trying to give you guys the info you need.

I'm trying to write a query that will give me all the information I need
to fill fields in a Word merge document.

TBLCLIENTS TBLCLIENTCONTACTS TBLCONTRACTINFO
ClientID ContactID ContractInfoID
CompanyName ClientID ClientID
Address FirstName PrimaryContact
City LastName TechnicalContact
State BillingContact
Zip OtherContact1
OtherContact2
OtherContact3
Date



TblClientContacts is related to TblClients via ClientID.

The problem seems to be with relating TblContractInfo and
TblClientContacts. All the Contact fields in TblContractInfo are Number
fields, containing the ContactID for the appropriate contacts from
TblClientContacts. In other words, PrimaryContact may equal 26,
TechnicalContact - 27, etc. On my form, this is okay because I look up
the name fields so it shows up right. But what shows up in the query is
just the numbers, which won't do for the merge.

So I tried making a query with multiple instances of TblClientContacts
(named Primary, Technical, Billing, etc...) and linking the ContactID
fields to the appropriate fields in TblContractInfo. This works, as long
as there are values in all the Contact fields. But there may not always
be an OtherContact3, for example. And when there isn't, the entire
record shows up blank. So how do I get the query to show a record even
when not all the linked fields have values?

grep

 

[kozaw]

Long post - sorry, but I'm trying to give you guys the info you need.

I'm trying to write a query that will give me all the information I need
to fill fields in a Word merge document.

TBLCLIENTS TBLCLIENTCONTACTS TBLCONTRACTINFO
ClientID ContactID ContractInfoID
CompanyName ClientID ClientID
Address FirstName PrimaryContact
City LastName TechnicalContact
State BillingContact
Zip OtherContact1
OtherContact2
OtherContact3
Date

TblClientContacts is related to TblClients via ClientID.

The problem seems to be with relating TblContractInfo and
TblClientContacts. All the Contact fields in TblContractInfo are Number
fields, containing the ContactID for the appropriate contacts from
TblClientContacts. In other words, PrimaryContact may equal 26,
TechnicalContact - 27, etc. On my form, this is okay because I look up
the name fields so it shows up right. But what shows up in the query is
just the numbers, which won't do for the merge.

So I tried making a query with multiple instances of TblClientContacts
(named Primary, Technical, Billing, etc...) and linking the ContactID
fields to the appropriate fields in TblContractInfo. This works, as long
as there are values in all the Contact fields. But there may not always
be an OtherContact3, for example. And when there isn't, the entire
record shows up blank. So how do I get the query to show a record even
when not all the linked fields have values?

grep

Hi Grep!

You just need to change the join type of your query.
If you want to show all records from tblClients then join properties
should be "include all records from tblClients and only those records
from other tables where the joined fields are equal.

Perhaps, if you present sql of your query, I think I can suggest more
for it.

Ko Zaw

 

[grep]

Ko,

You *nailed* it, my friend! I didn't even think about the join type.
Thanks so much for the help!

grep

 

[Bamar]

Ko,

You *nailed* it, my friend! I didn't even think about the join type.
Thanks so much for the help!

grep

Hey!,

You said you created that query,
how should you created that query without joining these tables.