Using The Lookup Wizard

[Debris]

Hello,

The only time I use the Lookup Wizard is to define the relationships between
tables, but only after I've defined the two tables.

In other words,
1. Design Tables
2. Establish Relationship using Lookup Wizard
3. Edit Relationships --> Enforce Referential Integrity (when applicable,
almost always)

Put another way, I rarely if ever use the option to "type in the values I
want" in the wizard dialog box.

Is this Ok, or am I just setting myself for problems down the road?

Thanks,

D

 

[Duane Hookom]

This depends on what you mean by "Lookup Wizard". If you use this in a table
design, them many of us feel you are making a mistake. Read
http://www.mvps.org/access/lookupfields.htm.

 

[John Vinson]

Hello,

The only time I use the Lookup Wizard is to define the relationships between
tables, but only after I've defined the two tables.

In other words,
1. Design Tables
2. Establish Relationship using Lookup Wizard
3. Edit Relationships --> Enforce Referential Integrity (when applicable,
almost always)

Put another way, I rarely if ever use the option to "type in the values I
want" in the wizard dialog box.

Is this Ok, or am I just setting myself for problems down the road?

Thanks,

D

It's certainly *unnecessary* to use it; as Duane says, the wizard can
cause problems (especially if you use the Combo Box option to display
the looked-up field in your tables). You can use the Relationships
window to define and enforce your relationships, just as easily; and
not run into the frequent confusion that table lookups cause.

John W. Vinson[MVP]

 

[Guest]

I am frustrated after having spent an entire day trying to use relationships
to fill in a row in a table. All i am able to do is fill in one column.
Help.
As an example, I want to look for a name in one table and fill in both the
first and last name in my main table. So far I am only able to transfer the
last name only.
jorel

 

[Douglas J. Steele]

If the names already exist in another table, you shouldn't be trying to
store them in any other table. That defeats the whole point of relational
databases.

All you should be storing in the second table is the ID of the appropriate
row in the first table. When you need to know the names in conjunction with
the data from the second table, you create a query that joins the two tables
together, and use the query, rather than the table.

 

[Jeff Boyce]

Joel

From your description, you have used the Lookup Wizard in designing your
table. What it is doing is storing one value (see Douglas' respons), but
displaying another (e.g., you are seeing "LastName").

NOTE: If this describes what you've done, you HAVE NOT been transferring
the "LastName" -- it just looks that way.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

I am frustrated after having spent an entire day trying to use relationships
to fill in a row in a table.

Well, that would be frustrating - as frustrating as using a
screwdriver to drive nails!

Relationships don't "fill" anything. They CONTROL what you're allowed
to enter; a "child" table in a relationship will not let you insert an
"orphan" record that's not linked to a parent.

If you're assuming that two tables in a relationship will
automatically copy fields from the parent into the child table, you're
missing the point. You should not HAVE duplicate fields between the
two tables. Each table will have its own distinct fields; you can use
a Form for the "one" side table with a Subform for the "many"; you can
create Reports based on a query joining the two tables in order to see
data from both.

John W. Vinson[MVP]