Lookup Wizard - Double links

[JudithJubilee]

Hello there,

I htink there is a simple answer to this but I can't
figure it out, so I'll explain in loads of detail!!

I'm having a bit of trouble with the Lookup Wizard. I
have cretaed my tables and relationships, eg.
tblEmp: EmpID - Primary key, AutoNumber
Name,Addr,Phone,etc.
DeptID - Number
tblDeptCode: DeptID: - Primary key, AutoNumber
DeptName - Text
DeptDescription - Memo

I would like the DeptID in tblEmps to be linked, using
Enforce Ref Int , to the DeptID in tblDeptCodes. I would
also like the field to be a dropdown list in tblEmps.
I can create the relationship: dragging from tblDeptCodes
to tblEmps.
I have then gone into the design of tblEmps and changed
the Data Type of DeptID to a Lookup Wizard. I have
followed the steps and finished creating it. It then
says "Table must be saved before relationships can be
created" So I choose "Yes".

When I look at the Relationships window I can see that
Access has added a table called tblDeptCodes_1. This has
a 1 - Many link with tblEmps. There is also a link from
tblEmps to tblDeptCodes with out 1 to Many indicators.

I don't understand what it is doing and whether I have to
create the Lookup before I create the link. Or if there
is a better way entirely??

Thankyou for reading and any help will be greatfully
appreciated.
Judith

 

[John Vinson]

Hello there,

I htink there is a simple answer to this but I can't
figure it out, so I'll explain in loads of detail!!

There is a simple answer:

Never use the Lookup Wizard. It does very little good and a fair bit
of harm.

See http://www.mvps.org/access/lookupfields.htm for a critique.

But I'll reply in more detail below.

I'm having a bit of trouble with the Lookup Wizard. I
have cretaed my tables and relationships, eg.
tblEmp: EmpID - Primary key, AutoNumber
Name,Addr,Phone,etc.
DeptID - Number
tblDeptCode: DeptID: - Primary key, AutoNumber
DeptName - Text
DeptDescription - Memo

I would like the DeptID in tblEmps to be linked, using
Enforce Ref Int , to the DeptID in tblDeptCodes. I would
also like the field to be a dropdown list in tblEmps.
I can create the relationship: dragging from tblDeptCodes
to tblEmps.
I have then gone into the design of tblEmps and changed
the Data Type of DeptID to a Lookup Wizard. I have
followed the steps and finished creating it. It then
says "Table must be saved before relationships can be
created" So I choose "Yes".

When I look at the Relationships window I can see that
Access has added a table called tblDeptCodes_1. This has
a 1 - Many link with tblEmps. There is also a link from
tblEmps to tblDeptCodes with out 1 to Many indicators.

One of the many flaws in the lookup wizard. As you can see, it creates
a new relationship (and new, redundant, space-eating indexes) *even if
they already exist*. It doesn't even check.

You can open the relationships window, select the join lines (not the
new table icons but the lines) and press the Delete key to get rid of
these extra relationships. Once the join line is gone you can delete
the duplicate table icon (it's just a graphic, not a real new table);
a combo box created by the lookup wizard will *use* an existing
relationship, it just doesn't recognize it when you're running the
wizard.

I don't understand what it is doing and whether I have to
create the Lookup before I create the link. Or if there
is a better way entirely??

There is.

Use Forms for all your data interactions. Table datasheets are best
reserved for debugging and design. It's perfectly straightforward to
create Combo Boxes on your Forms; it is *NOT* necessary to have the
combo box in the Table in order to do so.

John W. Vinson[MVP]

 

[[MVP] S.Clark]

The underscore is showing you that there is a second instance of the same
table in the relationship window.

For example, suppose that you have two tables. One called tblPeople, and
the other called tblCouples, which are PersonID's from tblPeople. Suppose
that tblCouples has two fields, Partner1 & Partner2.

If you want to represent Referential Integrity for both the Partner1 and
Partner2 fields, then you would need to add two instances of the table
tblPeople, with one link from Partner1 and the other from Partner2. In this
case, Access would represent one of the tblPeople tables as tblPeople_1.

 

[JudithJubilee]

Thankyou both for your replies. I'm now using the combo
boxes!!