table relationship

[Walter]

Is it normal to have multiple copies of tables in the
relationship window? I was looking at the relationships
and noticed several base tables had multiple copies
showing. I removed the copies and a few days later was
looking again and there were multiple copies again. I was
wondering if I was doing something to cause this thazt I
shouldn't.
Thanks,
Walter

 

[Van T. Dinh]

You probably used "Lookup" Fields in your Tables. See The Access Web
article:

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

 

[Walter]

I read the article and see lookup fields cause problems.
How do I handle having lists to choose from thereby
assuring speedy and accurate data input? Can there be be
combo boxes on the forms which are used for data entry and
not have lookup fields in the underlying tables. Also
some combo boxes values need to be based on a previous
one's value.
Thanks,
Walter

 

[John Vinson]

Can there be be
combo boxes on the forms which are used for data entry and
not have lookup fields in the underlying tables.

Certainly. There's a Combo Box wizard in the form toolbox that will
set up combos on Forms very easily; table Lookup fields don't help
(and as the website article makes clear, can do harm).

You may want to select the *JOIN LINES* in the Relationship windows
and delete *them*, before deleting the extra table icons. The Lookup
Wizard creates an extra relationship even if one already exists -
deleting these duplicate relationships will make your database smaller
and more efficient.

Also some combo boxes values need to be based on a previous
one's value.

This is easy on a Form, but - to my knowledge - impossible in a table
datasheet. See

http://www.mvps.org/access/forms/frm0028.htm

 

[Walter]

Thanks John for your help. To be sure I understand
correctly, I need to change the field properties in my
tables to long integer since all are autonumber primary
keys in their original table. Then go to the relationship
window, delete the joins and then delete the table copies.
Thanks again,
Walter

 

[John Vinson]

Thanks John for your help. To be sure I understand
correctly, I need to change the field properties in my
tables to long integer since all are autonumber primary
keys in their original table. Then go to the relationship
window, delete the joins and then delete the table copies.
Thanks again,
Walter

WHOA!!! No. That's NOT what I said at ALL!

Autonumber primary keys are fine. They should not be changed unless
you are presenting the primary key values to users as something
meaningful.

My concern is for "Lookup Fields" created by the Lookup Wizard.

It's perfectly normal to have lookup *tables* - small tables with two
fields, an autonumber ID and a text value; and to relate that
autonumber ID primary key to a Foreign Key in one or more other
tables. For instance, you might have a lookup table of Departments and
use the DepartmentID in a Project table and a Personnel table.

The excess relationships appear because the Lookup Wizard is *stupid*,
and creates a new relationship even if the relationship already
exists.

I'm suggesting that you delete the *relationship lines* that are not
needed - not change your field types or anything else.

 

[Walter]

I have those types of tables. (tblFarms = FarmID
autonumber primary key, FarmName, text)(tblFields =
FieldID autonumber primary key, FarmID, Field Number)
(tblPlantings = PlantingID autonumber primary key, FarmID,
FieldID, etc). My question is in tblPlantings should the
FarmID and FieldID fields be lookup fields or long integer.
When I first noticed the multiple copies of tables in the
relationship window, I deleted the joins and then the
copies but they are back. If they(the foreign key fields)
are correct as lookup fields in the underlying table then
is that causing the multiple copies of the tables.
Thanks,
Walter