Newbie Query Help!

[Kabby]

I'm driving myself insane trying to find the answer to what I believe is an
incredibly simple question....trying to teach myself Access as I go along.

I have created a media contact database. I want to be able to see all the
calls made to particular contact. I have one table Contacts 1 with
individual contact info in and another table Calls 1 with call info.

I'm trying to create a combo box in the Call table that allows me to select
the contacts name - however when I try this I just get the contact ID number
and not the name - what am I doing wrong?!!!!

Expr1: [Contacts 1].[Contacts 1]!FirstName & " " & [Contacts 1]!LastName

Sorry if this is a bit basic but I'm at a total loss! I want the combo box
to return a name. I've had to put the data type under Contact ID (where the
combo box is) as a number so I can link it to the Contacts table - is this
the issue?

I'd really appreciate some guideance
K

 

[John W. Vinson]

I'm driving myself insane trying to find the answer to what I believe is an
incredibly simple question....trying to teach myself Access as I go along.

I have created a media contact database. I want to be able to see all the
calls made to particular contact. I have one table Contacts 1 with
individual contact info in and another table Calls 1 with call info.

I'm trying to create a combo box in the Call table that allows me to select
the contacts name - however when I try this I just get the contact ID number
and not the name - what am I doing wrong?!!!!

Expr1: [Contacts 1].[Contacts 1]!FirstName & " " & [Contacts 1]!LastName

Sorry if this is a bit basic but I'm at a total loss! I want the combo box
to return a name. I've had to put the data type under Contact ID (where the
combo box is) as a number so I can link it to the Contacts table - is this
the issue?

I'd really appreciate some guideance
K

Are you trying to work *IN THE TABLE*?

If so... don't. Tables are for data storage; their capabilities in data
editing and display are very limited. Yes, they can be used... but you'll run
into their limitations very quickly. If you're trying to put a combo box in a
table read http://www.mvps.org/access/lookupfields.htm for a critique of what
many of us consider a misfeature in Access.

The simplest way would be to build a Form based on Contacts (with textboxes
for firstname, lastname, etc.) and a Subform based on Calls. I presume (well,
I hope) that you have a Primary Key of ContactID in the Contacts table, with a
foreign key ContactID in the Calls table; if so, use this (whatever the field
is named) as the master/child link of the subform.

You can use the Combo Box Wizard to create an unbound combo box on the
mainform - choose the option "use this combo to find a record".

 

[Kabby]

John - thank you so much. You are correct I was trying to create the combo
box in the table. I already have a 'Contacts' form, with a tab for calls
showing the list of calls made to that contact using a calls subform.
However I must still be doing something wrong as in the calls table I am
still getting a list of the Contact ID in numbers rather than name (I do have
the primary key Contact ID in Contacts linked to Contact ID in Calls table!).
I need it to show the name so I can run reports showing the last calls made
to certain contacts.

I've been adapting the sales contact database template from Access 2003 if
that is any help!

I'll continue playing and see if I get anywhere!

I'm driving myself insane trying to find the answer to what I believe is an
incredibly simple question....trying to teach myself Access as I go along.

I have created a media contact database. I want to be able to see all the
calls made to particular contact. I have one table Contacts 1 with
individual contact info in and another table Calls 1 with call info.

I'm trying to create a combo box in the Call table that allows me to select
the contacts name - however when I try this I just get the contact ID number
and not the name - what am I doing wrong?!!!!

Expr1: [Contacts 1].[Contacts 1]!FirstName & " " & [Contacts 1]!LastName

Sorry if this is a bit basic but I'm at a total loss! I want the combo box
to return a name. I've had to put the data type under Contact ID (where the
combo box is) as a number so I can link it to the Contacts table - is this
the issue?

I'd really appreciate some guideance
K

Are you trying to work *IN THE TABLE*?

If so... don't. Tables are for data storage; their capabilities in data
editing and display are very limited. Yes, they can be used... but you'll run
into their limitations very quickly. If you're trying to put a combo box in a
table read http://www.mvps.org/access/lookupfields.htm for a critique of what
many of us consider a misfeature in Access.

The simplest way would be to build a Form based on Contacts (with textboxes
for firstname, lastname, etc.) and a Subform based on Calls. I presume (well,
I hope) that you have a Primary Key of ContactID in the Contacts table, with a
foreign key ContactID in the Calls table; if so, use this (whatever the field
is named) as the master/child link of the subform.

You can use the Combo Box Wizard to create an unbound combo box on the
mainform - choose the option "use this combo to find a record".

 

[John W. Vinson]

John - thank you so much. You are correct I was trying to create the combo
box in the table. I already have a 'Contacts' form, with a tab for calls
showing the list of calls made to that contact using a calls subform.
However I must still be doing something wrong as in the calls table I am
still getting a list of the Contact ID in numbers rather than name (I do have
the primary key Contact ID in Contacts linked to Contact ID in Calls table!).
I need it to show the name so I can run reports showing the last calls made
to certain contacts.

No. You do NOT need to store the name redundantly in the Calls table in order
to produce a report! Storing the ContactID is exactly the correct behavior.

You may be trying to base the report only on the Calls table. That's not how
relational databases work! You would base the report *on a Query* joining the
Contacts table to the Calls table, and pull the contact name from the contacts
table, and the call information from the calls table.

 

[Kabby]

Got it!!! Sorted now - thank you for your advice
K