LOOKUP TABLE PROBLEMS

[Reggie]

Yes, I am a novice. I am home for two weeks on Christmas leave so I thought
I would teach myself Access. I am designing a database to retain information
obtained during historical research. I have one main table that I use to
keep track of every new individual I discover in my research. The table is
as follows(some fields I will omit for this discussion):

Citizen ID # (autonumber)
Citizen name (test)
Date of Birth (text)
Birth Place (text)
City Name (lookup)
State Name (lookup)

The city and state Lookup fields draw information from tables listing cities
and states.

I have another table in which I have family data. One of the Lookup fields
is "Citizen Name" and it draws its information from the Citizen Name field
mentioned above. I thought that using Lookup fields for citizen names would
save time and cut down on errors so I have used them in several tables. I
assumed that I would be able to regularly update the Citizen Name table as
new names were discovered while at the same time accessing its Citizen Name
field via Lookup fields in other tables. However, I get an error when I try
to add a new name. Have I sinned? Is the Citizen Name table permanently
locked so that I can no longer add names to it. I really only want to have
to type each name one time as I am talking hundreds of names.

The relationship between the tables are: City Name table -1, to many
(Citizen Name table). I have a copy of the relationship table page if anyone
needs to see it. I do not know if newsgroups can accept attachments. If an
attachment can be sent in I will do so if needed to clarify.

I have simplified this situation to, hopefully, make it easier to
understand.

I clearly see why Access drives people nuts!

Thank you for your time. I appreciate any insight you can give.

Reggie

 

[John Vinson]

Yes, I am a novice. I am home for two weeks on Christmas leave so I thought
I would teach myself Access.

<chuckle> I've been using Access since 1991. I'm still learning at
least one (often several) new features every week. Access is a
powerful program but it DOES take a lot of learning.

I am designing a database to retain information
obtained during historical research. I have one main table that I use to
keep track of every new individual I discover in my research. The table is
as follows(some fields I will omit for this discussion):

Citizen ID # (autonumber)
Citizen name (test)
Date of Birth (text)
Birth Place (text)
City Name (lookup)
State Name (lookup)

Do take a look at http://www.mvps.org/access/lookupfields.htm for a
critique of the so-called Lookup field. They are VERY limited,
deceptive, and confusing, and most developers avoid them altogether.

The city and state Lookup fields draw information from tables listing cities
and states.

That's fine, and using a lookup *feature* is perfectly normal and
acceptable... but use it in the form of a Combo Box control on a Form,
not in a table.

I have another table in which I have family data. One of the Lookup fields
is "Citizen Name" and it draws its information from the Citizen Name field
mentioned above. I thought that using Lookup fields for citizen names would
save time and cut down on errors so I have used them in several tables.

Only partially true. See reference above.

I assumed that I would be able to regularly update the Citizen Name table as
new names were discovered while at the same time accessing its Citizen Name
field via Lookup fields in other tables. However, I get an error when I try
to add a new name. Have I sinned? Is the Citizen Name table permanently
locked so that I can no longer add names to it. I really only want to have
to type each name one time as I am talking hundreds of names.

This is straightforward ON A FORM - you can Requery the combo box when
you add a new citizen; there is no capability to do this in a table
datasheet Lookup field.

The relationship between the tables are: City Name table -1, to many
(Citizen Name table). I have a copy of the relationship table page if anyone
needs to see it. I do not know if newsgroups can accept attachments. If an
attachment can be sent in I will do so if needed to clarify.

Please do NOT POST ATTACHMENTS. They are most unwelcome on these
newsgroups, and they're not necessary.

It appears that it's Microsoft's misdesigned, misleading, inadequate,
and annoying Lookup Wizard misfeature that's at the root of most of
your frustration. As you may be able to tell, I don't like it much.

 

[Reggie]

Ouch!

Well, back to the drawing board. Access is one confusing program to learn.
Thanks for your honest and helpful reply.

Reggie

 

[Rolls]

I handle this situation as follows.

tblPeople and tblLocation are Entity tables. tblPeople-Location is a
Relationship table. Because there is a potential many:many relationship
between people and locations, the relationship table stores information
about valid combinations.

A subform with combo boxes having the Entity tables as a rowsource lets the
user lookup "Person" using cboPerson and store it's key value in field A and
"Location" 's key value, storing that in field B via cboLocation.
tblPeople-Location therefore contains only the foreign keys representing a
valid combination, and there are no duplicate pairs of key values. Each
combo box is set up with 2 columns. The column width of the first column
should be zero, the second a length adequate for the width of the field
(person's name or a location) to be displayed on the form. If there are
additional fields in an Entity table that you want to display as separate
text boxes on a form outside of the combo box, use the Column() property for
these additional fields. When you change the combo box the related fields
will also change. Adding a new record in the subform will save key vield
values each time you change the combo box values.

Because SQL and Access use an Entity-Relationship model this combination of
tables forms, and subforms works for all parent-child relationships in the
database. A key point about understanding Access is nderstanding the
relational model and "data normalization" which is the subject matter for
table definition.

Don't use "lookup fields" in tables.