Autonumber as Primary Key and Relationships

Hi Jeff,

Makes sense to me. Depending on the situation, I could easily end up
with something similar.

Clifford Bass

"Jeff Boyce" wrote:

Cliff

In the very-specific environment I faced, I needed to track who-where-in
what role, so I used a single identifier for each valid combination (i.e., a
"contextID"), and kept Individual, Organization, and Position tables.

Then, since 'contexts' could share an address (think small town city hall),
or a phone number (think very small town) and, theoretically, an email
address, I kept Address, Phone and Email tables.

Finally, to handle the many-to-many, I created junction tables between the
contexts and the address, phone and email records.

A real pain, and definitely not your father's CRM system, but absolutely
required to handle the complexities of the environment.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I might suggest the following instead; subject to
modification/refinement as needed and subject to it not being overkill to
your situation.

Again, one table for people that would include all of the service
members, the spouses and the dependents. An address table, a telephone table
and some junction tables (people/spouse, people/dependents, people/address
and people/telephone).

tblPeople
Person_ID (autonumber, primary key)
Last_Name
First_Name
Rank
other person-specific information

tblAddresses
Address_ID (autonumber, primary key)
Address
City
etc.

tblTelephones
Telephone_ID (autonumber, primary key)
Telephone_Type
Telephone_Number
maybe other info such if it can accept text messages

tblPeople_Spouses
Person_ID (long int, connected to tblPeople.Person_ID, part of primary
key)
Spouse_ID (long int, connected to tblPeople.Person_ID, part of primary key)
other spouse-specific information

tblPeople_Dependents
Person_ID (long int, connected to tblPeople.Person_ID, part of primary
key)
Dependent_ID (long int, connected to tblPeople.Person_ID, part of primary
key)
other dependent-specific information

tblPeople_Addresses
Person_ID (long int, connected to tblPeople.Person_ID, part of primary
key)
Address_ID (long int, connected to tblAddresses.Address_ID, part of
primary key)
other information specific to this person/address combination if needed

tblPeople_Telephones
Person_ID (long int, connected to tblPeople.Person_ID, part of primary
key)
Telephone_ID (long int, connected to tblTelephones.Telephone_ID, part of
primary key)
other information specific to this person/telephone combination if needed


However if you want to keep your current design: Unless you are
allowing for polygamists and such, you only need the SM_ID in the spouse
table (one person has one spouse). However for the dependent table you would
need need an additional field in your primary key so that you can have
multiple dependents for each service number. If I were doing it, I probably
would add an integer dependent number field that would start with 1 and go up
for each service member. So service member 1 might have dependents numbered
1 and 2; and service member 2 might have dependents numbered 1, 2, 3 and 4.
This is easy enough to automate in a form so the user would not have to enter
the number.

Hope that helps,

Clifford Bass

"cmc096" wrote:

Gentlemen, thanks for all the input. I think I am on the right track now. I
am going to set my table up like this:

Parent Table
tbl_Service_Member_Info
SM_ID (autonumber_PK)
L_Name
F_Name
Rank
Address
Phone

Child Table
tbl_Spouse_Info
SM_ID (long int, PK)
L_Name
F_Name
Address
Phone

tbl_Dependent_Info
SM_ID (long int, PK)
L_Name
F_Name
Address
Phone

My question is with this design will it allow me to have a one to many
relationship with the Parent tbl (Service_Member_Info) to the Child table
(Dependent_Info)? Also, will one service member then be linked to the spouce
and dependent tables using the autonumber to link them and track for instance
multiple dependents?

If I send a screen shot of the tables in the relationship view can someone
help me out with linking them together?

"John W. Vinson" wrote:

On Wed, 8 Jul 2009 08:34:02 -0700, cmc096
wrote:

Another question. When I am creating the relationships it is only showing me
a one-to-one in the relationship type when joining the PK autonumber to a PK
long int field, is there something else I need to do?

Yes. Give each child table ITS OWN PK. You *cannot* use the SM_ID as the
primary key of the child table since a primary key is, by definition, unique -
you can only have one child record for a give SM_ID because you're
(erroneously) using it as the primary key. It's a foreign key which should be
indexed but not unique.

--

John W. Vinson [MVP]

Hi,

Not really anything to the actual setup of the relationships once you
have decided on the table structures. Just click and drag from one table's
field to the other table's field. So you would click on the
tbl_Service_Member's SM_ID and drag that to the tbl_Spouse_Info's SM_ID. You
can add other fields if needed for relations that require multiple fields.
Double-click the linking line to set some of the link's properties.

Clifford Bass

"cmc096" wrote:

If I send a screen shot of the tables in the relationship view can someone
help me out with linking them together?