Lookup - can 2 fields be inserted from 1 lookup?

[Robert Robinson]

Hi -

I'm new to the group, and new to advanced table design. I am creating a
multitable database. I will have 4 base tables, then 3-4 secondary tables
which will pull in fields from the base tables using lookups. I am now trying
to pull 2 fields from base table 1 into secondary table 1. When I use the
lookup wizard, I am able to pull 1 field from a base table into a secondary
table, but I am actually trying to look up two fields (say first & last name)
then have both fields insert into the secordary table. Is this possible in
Access (2003)?

 

[John Vinson]

Hi -

I'm new to the group, and new to advanced table design. I am creating a
multitable database. I will have 4 base tables, then 3-4 secondary tables
which will pull in fields from the base tables using lookups.

Do consider the critique at

http://www.mvps.org/access/lookupfields.htm

Table datasheets are NOT appropriate for data editing or data entry.
Forms are much more powerful and flexible; you can put "lookups" -
combo boxes - on a Form, without using Microsoft's misleading Lookup
Wizard.

I am now trying
to pull 2 fields from base table 1 into secondary table 1. When I use the
lookup wizard, I am able to pull 1 field from a base table into a secondary
table, but I am actually trying to look up two fields (say first & last name)
then have both fields insert into the secordary table. Is this possible in
Access (2003)?

This is a perfect example of why a lot of us HATE the lookup wizard.

Your table2 *DOES NOT CONTAIN* the last name, nor should it.

The lookup field does not "insert" data from Table1 into Table2. It
CONCEALS the actual contents of Table2 - a numeric ID field - behind
the lookup combo box. Your Table2 should NOT contain either the first
name or the last name; if you look at it in table datasheet view, it
should contain only a numeric person ID.

If you base a Form on Table2, you can put a Combo Box on that form
based on a query showing the first and last names concatenated into a
single calculated field, and store the ID field in the table. Without
knowing your table or fieldnames I can't be much more specific, but
the Form toolbox wizard can help you set up a combo, or you can post
back.

But you've gotten beyond the very, very limited capabilities of the
lookup wizard. You cannot, to my knowledge, do what you ask in a table
datasheet; on the other hand, it's really easy in a Form.

John W. Vinson[MVP]

 

[Robert Robinson]

That tells me that I was barking up the wrong tree in the first place.
Instead of having 4 primary tables and 4 "secondary" tables, I should use
the 4 tables, and create 4 forms , as I am just spinning off the data in the
4 tables (not creating any new data, save a calculation on the final form.

Thanks. You saved me hours.

 

[Robert Robinson]

One last (suuuure) question...

You speak of basing the form on table 2; can you bring the contents of a
couple of tables into a form? It seems possible from what I have read; I am
just wondering if there are any hidden gotchas. Thanks.

 

[John Vinson]

One last (suuuure) question...

You speak of basing the form on table 2; can you bring the contents of a
couple of tables into a form? It seems possible from what I have read; I am
just wondering if there are any hidden gotchas. Thanks.
--

You can do this in two ways: base the Form on a Query joining the two
tables, or use a Form and a Subform. The latter approach is usually
preferable; a Query joining two tables one-to-many will possibly be
confusing to the user, since the values in the "one" side table will
be shown repeatedly and may not be updateable, or if they are,
updating one record on the form will cause other records to change
(since you're really updating just one record which is displayed many
times).

I'm afraid you may be letting the forms drive your table design. If
so, that's backwards: you should start with a logical, normalized set
of Tables, and then craft the forms to fit the table structure, not
vice versa!

John W. Vinson[MVP]

 

[Robert Robinson]

Yep. The (query join) bell finally went off in my head...

I created a query that concatenates first name and last name, then created a
form that pulls the concatenated name in a combo box as well as fields from
table 2 (both are set to update the new table). After adding one direct entry
field, my form (and new table) are complete. Again, thanks for pointing me in
the right direction.

 

[Robert Robinson]

Earlier on, you mentioned concatenating fields (First, Last Name) into 1
field, then having a query pull the combined value. This works great for the
names, but is there a way (outside of programming/concatenation) to pull two
fields from a record (via query or table) into a form?

 

[John Vinson]

Earlier on, you mentioned concatenating fields (First, Last Name) into 1
field, then having a query pull the combined value. This works great for the
names, but is there a way (outside of programming/concatenation) to pull two
fields from a record (via query or table) into a form?

I'm confused.

You want to concatenate two fields.

You don't want to use concatenation.

If you want to see two fields from a record on a Form, simply include
those two fields on the Form; if you want to concatenate them, use a
Control Source for a textbox concatenating them.

I guess I don't understand what you have and what you want!

John W. Vinson[MVP]

 

[Robert Robinson]

To clarify...

I had a "name" field issue for which your concatenation solution was
perfect. That issue is done.

Now, I have 2 fields from a table (Procedure and Cost) that I want to place
on a form. I want a user to be able to select a "Procedure" and have both
that procedure and related cost populate 2 fields on the new form. I could
create combo boxes for "Procedure" and "Cost" that a user could select
individually, but since these are linked, it makes sense to populate both
fields with 1 selection. I could concatenate these 2 fields, I would be able
to bring both of these values into 1 field on the new table, but that is not
what I am looking for. Hope that clears up the mud I slung earlier...