Several relationships from a single table

[Guest]

I use a City-State reference table in my genealogy database for three fields
in another table - the birth, death, and primary residence locations for each
person.

The way I originally defined the relationships in the Relationships Window
was by using the Show Table option and inserting the table three times.
Access shows the tables as City-State_1, City-State_2, City-State_3. Is there
a better way to establish the relationships?

I miss being able to expand the records in my original table with the "+"
button. Evidently setting up the relationships as I did does not allow to
view related records anymore without a query being performed.

 

[tina]

hmm, sounds as though your "persons" table could be normalized a bit in this
area. if you have fields named "birth location", "death location", and
"primary residence location", then you're storing data (birth, death,
primary residence) in fieldnames . that's usually an indication that it is
appropriate to put that data into a subordinate table. suggest you move
"locations" into a child table of your people table, as

tblPeople
PersonID (primary key)
FirstName
LastName
(other fields that describe a person)

tblLocationTypes
LocTypeID (primary key)
LocTypeName
(records in this table would be "birth", "death", "primary residence", and
whatever other location types are appropriate.)

tblPeopleLocations
PLocID (primary key)
PersonID (foreign key from tblPeople)
LocTypeID (foreign key from tblLocationTypes)
CityStateID (foreign key from tblCityState)

relationships would be:
tblPeople 1:n tblPeopleLocations
tblLocationTypes 1:n tblPeopleLocations
tblCityState 1:n tblPeopleLocations

hth

 

[Guest]

Hi Brian,

Your relationships sound fine to me. This is exactly how it is done.

The "+" button that you refer to is known as a SubDatasheet. This is a
feature that was added with Access 2000. Each table can have one table or
query specified in the "Subdatasheet Name" property of the table. To see this
property, open a parent table in design view. Then click on View >
Properties. You should be able to write a query that includes the three
fields (birth, death, and primary residence locations) for each record.
Include the primary key field from this table. Then specify this query in the
Subdatasheet Name property for the CityState table.

There is a reason that you may want to temper your excitement for
Subdatasheets. If you access data over a network (ie. linked tables, as in a
multiuser application), then you'll want to set this property to [None] for
all tables:

BUG: Slow performance on linked tables in Access 2002 and Office Access 2003
http://support.microsoft.com/?id=275085


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I use a City-State reference table in my genealogy database for three fields
in another table - the birth, death, and primary residence locations for each
person.

The way I originally defined the relationships in the Relationships Window
was by using the Show Table option and inserting the table three times.
Access shows the tables as City-State_1, City-State_2, City-State_3. Is there
a better way to establish the relationships?

I miss being able to expand the records in my original table with the "+"
button. Evidently setting up the relationships as I did does not allow to
view related records anymore without a query being performed.

 

[John Vinson]

I use a City-State reference table in my genealogy database for three fields
in another table - the birth, death, and primary residence locations for each
person.

Heh.

For my entry, this would have: Shanghai, China; (Null); Shanghai,
Kashing, Rocky Mount NC, Kobe Japan, Tulsa OK, Sallisaw OK,
Bentonville AR, East Lansing MI, Berkeley CA, Cambridge MA, Ann Arbor
MI, Stockbridge MI, Parma ID... and very likely more in the future.

What's a *primary* residence these days!?

The way I originally defined the relationships in the Relationships Window
was by using the Show Table option and inserting the table three times.
Access shows the tables as City-State_1, City-State_2, City-State_3. Is there
a better way to establish the relationships?

What's wrong with that? It's just letting you define three different
relationships to the same table. It's not creating new tables of
course, just table icons.

I miss being able to expand the records in my original table with the "+"
button. Evidently setting up the relationships as I did does not allow to
view related records anymore without a query being performed.

Table datasheets are of VERY limited utility. What would you want to
see? Three subdatasheets under each record? How would you want them
arranged...???

Use a Form with three combo boxes, or a Query joining the three
instances of the city table.

John W. Vinson[MVP]