goofed up on value lists

[Guest]

I want to look up a name in another table from a cases table. I just need
the first and last names in the data input form. Right now I am only
concerned with
the tables relating properly. I will work on the form later.
I assume I need 3 columns, the personID, firstname and last name in
3 columns of the look up table? All I need is a pick list. Does Access
match the IDs for me? I noticed there is a relationship now in the graph
from the name field in cases to the person table. Do I have to choose
referential integrity or any of the join properties or am I done? I just
have one field for the name in the case table.
Or do I do this on a form? It isn't clear to me but right now I am only
defining fields and tables.
thanks,

 

[John Vinson]

I want to look up a name in another table from a cases table. I just need
the first and last names in the data input form.

Do you want to just *display* the first and last name (good) or
*store* them redundantly in the other table?

Right now I am only concerned with
the tables relating properly. I will work on the form later.
I assume I need 3 columns, the personID, firstname and last name in
3 columns of the look up table? All I need is a pick list. Does Access
match the IDs for me?

Well... not sure what you mean there. Your Cases table should have
ONLY a PersonID field; it should *NOT* have a first or last name
field, they should exist only in the Persons table. You won't be
looking at the table datasheet at all, except for debugging.

I noticed there is a relationship now in the graph
from the name field in cases to the person table. Do I have to choose
referential integrity or any of the join properties or am I done? I just
have one field for the name in the case table.

If you've used the Lookup Wizard... don't. It's one way to create
relationships, but it's a very limited and confusing tool. See
http://www.mvps.org/access/lookupfields.htm for a critique.

Instead, use the Relationships Window to create a relationship between
the PersonID field in the Cases table and the PersonID primary key of
the people table.

Or do I do this on a form? It isn't clear to me but right now I am only
defining fields and tables.

You define fields and tables first; and *THEN* create a Form to view
and edit the data. On that Form you can put a Combo Box based on a
query such as

SELECT PersonID, [LastName] & ", " & [FirstName] AS FullName
ORDER BY LastName, FirstName;

Use the Combo Box Wizard to create the combo based on this query; it
will store the numeric PersonID in your Cases table, and display the
corresponding human-readable name.

John W. Vinson[MVP]