Query help...

L

Larry Kahm

I have three tables:

tblContacts, which contains ContactID (PK), FirstName, LastName, and
assorted other information
tblContactModes, which contains ContactModeID (PK), ContactID (FK),
PhoneNumber, and additional information
tblReferences, which contains ReferenceID (PK), RefNameID (FK), RefStatus,
and more

When I build the following query, it is not updateable.

SELECT tblReferences.ReferenceID, tblReferences.RefStatus,
tblContacts.FirstName, tblContacts.LastName, tblContactModes.PhoneNumber
FROM (tblContacts INNER JOIN tblReferences ON tblContacts.ContactID =
tblReferences.RefNameID) INNER JOIN tblContactModes ON tblContacts.ContactID
= tblContactModes.ContactID;

If I remove the PhoneNumber requirement (and the tblContactModes),
everything is fine.

But I am stumped as to how to show that a person is a reference and what
their phone number is. Any and all help is greatly appreciated.

Thanks!

Larry
 
J

John W. Vinson

I have three tables:

tblContacts, which contains ContactID (PK), FirstName, LastName, and
assorted other information
tblContactModes, which contains ContactModeID (PK), ContactID (FK),
PhoneNumber, and additional information
tblReferences, which contains ReferenceID (PK), RefNameID (FK), RefStatus,
and more

When I build the following query, it is not updateable.

SELECT tblReferences.ReferenceID, tblReferences.RefStatus,
tblContacts.FirstName, tblContacts.LastName, tblContactModes.PhoneNumber
FROM (tblContacts INNER JOIN tblReferences ON tblContacts.ContactID =
tblReferences.RefNameID) INNER JOIN tblContactModes ON tblContacts.ContactID
= tblContactModes.ContactID;

If I remove the PhoneNumber requirement (and the tblContactModes),
everything is fine.

But I am stumped as to how to show that a person is a reference and what
their phone number is. Any and all help is greatly appreciated.

I'd suggest NOT trying to create One Great Master Query to do everything. If
you're trying to update data using a query datasheet, you'll have this and
other problems!

Instead use a Form (probably based on Contacts) with two subforms, one for
ContactModes and the otehr for References.
 
L

Larry Kahm

John,

The client has requested the ability to see "at a glance" References and
their contact information. I was building the query for a continuous form
that would display these fields - well, until I couldn't display anything.

Are there any workarounds?

Thanks!
 
J

John W. Vinson

John,

The client has requested the ability to see "at a glance" References and
their contact information.

A Form with two Subforms will do exactly that: show all the contact
information on the mainform, all the references on one subform, all the
contactmodes on the other subform.
I was building the query for a continuous form
that would display these fields - well, until I couldn't display anything.

Are there any workarounds?

Use Left Joins in the query rather than Inner Joins, so that you'll still see
a client with no References or with no ContactModes. You'll still have
problems - the query will not be updateable (since you don't have a unique
value for the client), and if someone has three ContactModes and four
References, you'll see all twelve possible combinations, since there is no
relationship between ContactModes and References.
 

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

Top