Lookup Yielding Key Field Values Instead of Desired Field Values

[newt]

This is my first day using Access. The data I am using is specialized, but
my problem is analogous to this. I have a table, call it "Player table" with
an "ID" key field, and "Name" and "Position" fields (which are populated via
lookups to single columns of other tables, say a "Name table" and a "Position
table"). I have a second table (call it "Team table") by which I want to
assign players to teams, so, in addition to an ID key field, I have a
"Player" field in which I want to be able select "Smith - QB", for example --
that is, select a record in the Player table and show the Name and Position
fields, which should be linked together. I have been trying for hours to
enter the correct SELECT text for the "Player" field. I seem to have the
concatenation working, except instead of showing "Smith - QB" as an option in
the list boc, it shows "1-3" -- that is, the ID values. What am I doing
wrong?!?? Thanks

 

[John W. Vinson]

This is my first day using Access. The data I am using is specialized, but
my problem is analogous to this. I have a table, call it "Player table" with
an "ID" key field, and "Name" and "Position" fields (which are populated via
lookups to single columns of other tables, say a "Name table" and a "Position
table"). I have a second table (call it "Team table") by which I want to
assign players to teams, so, in addition to an ID key field, I have a
"Player" field in which I want to be able select "Smith - QB", for example --
that is, select a record in the Player table and show the Name and Position
fields, which should be linked together. I have been trying for hours to
enter the correct SELECT text for the "Player" field. I seem to have the
concatenation working, except instead of showing "Smith - QB" as an option in
the list boc, it shows "1-3" -- that is, the ID values. What am I doing
wrong?!?? Thanks

All you're doing wrong is trusting Microsoft to have been reasonable in their
design. They weren't. See http://www.mvps.org/access/lookupfields.htm for a
critique of what many of us consider a misfeature.

The field in your table does NOT contain a name. It only appears to do so; the
field actually contains the numeric PlayerID field. You'll need to create a
query joining your table to both lookup tables - which is where the names
actually exist - and concatenate *those* fields.

 

[newt]

Thanks, John. I've been reading through this forum after posting my
question, and I did see everyone saying that using lookup fields is a
definite no-no. So I scrapped everything, and am not using any lookup fields
- just text fields, and entering the relevant references in the Row Source
fields. Haven't even done a single query yet.... Maybe I should start
again, step by step:

1. I create a client table, with just 2 columns - ID (key) and client
(e.g., John Smith)
2. I create a project type table, with just ID and project type (e.g.,
painting, or plumbing)
3. I want to create "Projects", which would consist of picking a Client and
a Project Type, where the same Client can have more than 1 Project Type
(e.g., John Smith - Painting; John-Smith - Plumbing). The Project Table
would have 3 columns - ID, Client and Project Type
4. Then, I want to assign to be able to use a form to create a record for
individual "Tasks", each of which would be part of a Project. E.g., if "Send
Estimate" is a type of Task, I want to have a form where a user can pick
"John Smith-Painting' as the Project, and then "Send Estimate" as the
task.... In other words, I don't want the user to have to pick a "ProjectID"
which would come from the "Project Table", but rather the unique
"Client"-"Project Type" pairing that results from creating each record in the
Product table.

This seems so simple, but I've been at it all day....

Thanks in advance for any additional help - this is so frustrating!!

 

[John W. Vinson]

Thanks, John. I've been reading through this forum after posting my
question, and I did see everyone saying that using lookup fields is a
definite no-no. So I scrapped everything, and am not using any lookup fields
- just text fields, and entering the relevant references in the Row Source
fields.

Well... just to clarify: There's *NOTHING* wrong with Lookups. They're
universal; they're nearly indispensible; I use them all the time.

The objection is to using Lookup Fields *in tables*. Instead, you can and
should have lookup tables - say a table of Clients, or a table of Tasks - with
a Long Integer or Autonumber Primary Key, and a text field for the
human-readable content. In your assignments table (see below) you would have
just a Long Integer number field for the ProjectID. On your Form - where all
interaction with the data should take place - you would have a Combo Box (a
"lookup" if you will) bound to the ProjectID, but displaying the project name.

Haven't even done a single query yet.... Maybe I should start
again, step by step:

1. I create a client table, with just 2 columns - ID (key) and client
(e.g., John Smith)

Welll... three or more: ClientID, LastName, FirstName. Maybe middlename, other
contact information. You want to have the freedom to sort or search by last
name alone, first name alone, or the combination; it's easier to concatenate
two fields than it is to split up a single one.

2. I create a project type table, with just ID and project type (e.g.,
painting, or plumbing)

That's good.

3. I want to create "Projects", which would consist of picking a Client and
a Project Type, where the same Client can have more than 1 Project Type
(e.g., John Smith - Painting; John-Smith - Plumbing). The Project Table
would have 3 columns - ID, Client and Project Type

ClientID (a Number/Long Integer link to the Clients table ClientID) and a
ProjectTypeID (again, number, link to Projects).

4. Then, I want to assign to be able to use a form to create a record for
individual "Tasks", each of which would be part of a Project. E.g., if "Send
Estimate" is a type of Task, I want to have a form where a user can pick
"John Smith-Painting' as the Project, and then "Send Estimate" as the
task.... In other words, I don't want the user to have to pick a "ProjectID"
which would come from the "Project Table", but rather the unique
"Client"-"Project Type" pairing that results from creating each record in the
Product table.

Another table! You need a Tasks table with a link to the primary key of the
Projects table, and another link to a table of Tasks: again, linking to a
numeric TaskID.

This seems so simple, but I've been at it all day....

Thanks in advance for any additional help - this is so frustrating!!

Do take a look at the resources, especially Crystal's tutorial; she now has a
YouTube video showing how to set up a database:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[newt]

Thanks so much John. I don't know how you and the other MVPs do it, but your
generosity with your help is simply amazing. I am not a tech person - just a
computer literate lawyer trying to make a database to track my department's
work flow - so this is all "on my own time".... No one in my firm's tech
department can build a database.... I just downloaded Crystal's book and am
reading it with interest. Will now use only IDs when looking up records from
other tables....

I think I am now closing in on at least the proper way to ask the question
-- how, in a form based on/used to populate a TaskTable, to let the user
choose a "Project Name" (e.g., "Smith - Painting"), where the relevant field
in the TaskTable that the user is "really" choosing is ProjectID, which is
looked up from a ProjectTable (where it is the key/auto number field), which
table has ClientID and ProjectTypeID fields, which in turn are looked up from
a ClientTable and a ProjectTypeTable (where they are the key/auto number
fields), which are where the Client Names and Project Type Names that I want
to concatenate to form the "Project Name" that the form user is selecting
ultimately reside....

Whew....Spent 12 hours on this today and I feel like I'm just barely at the
tip of the iceberg....

Again - thanks for being so generous with your time and help!!! You guys
are incredible.

 

[John W. Vinson]

Thanks so much John. I don't know how you and the other MVPs do it, but your
generosity with your help is simply amazing. I am not a tech person - just a
computer literate lawyer trying to make a database to track my department's
work flow - so this is all "on my own time".... No one in my firm's tech
department can build a database.... I just downloaded Crystal's book and am
reading it with interest. Will now use only IDs when looking up records from
other tables....

Good on ya! and thanks for the kind words.

I think I am now closing in on at least the proper way to ask the question
-- how, in a form based on/used to populate a TaskTable, to let the user
choose a "Project Name" (e.g., "Smith - Painting"), where the relevant field
in the TaskTable that the user is "really" choosing is ProjectID, which is
looked up from a ProjectTable (where it is the key/auto number field), which
table has ClientID and ProjectTypeID fields, which in turn are looked up from
a ClientTable and a ProjectTypeTable (where they are the key/auto number
fields), which are where the Client Names and Project Type Names that I want
to concatenate to form the "Project Name" that the form user is selecting
ultimately reside....

If it wasn't clear... there are several ways to do this. Typically you'ld use
a Form based on the project table, with a Subform based on the TaskTable. The
Form would have an unbound combo box to let you locate the desired project
(the Combo Box toolbox wizard will build this for you); the Master/Child Link
Field of the subform would be the ProjectID, so it will fill in automatically
when you create a subform record. On the subform you'ld have a combo box
*storing* the TaskID but displaying the task name.

If you're still thinking of concatenating text strings to store in the tasks
table... don't. That's not how it works; the text stays in its appropriate
table, only, and you can *display* the text values using tools like combo
boxes, listboxes, and queries.