Picking the Primary Key

[Dwight]

Hi all,

Iam a first time user of any database. I am using A2003. I want to design a
database for county marriage records. So far, I have the following tables:

COUNTY: County GROOM: SurnameGr BRIDE: SurnameBr
MARRIAGEDATA: LicenseIssuedDate
Book GivenNameGr
GivenNameBr MarriageDate
Page AgeGr
AgeBr
MarriagePerformedBy

My question is should I allow Access to use the Autonumber or can I use a
multifield key. I would like to use the three fields in the COUNTY table as
the key since all the other info comes from them. There are no common fields
in the other tables. How can I connect the 4 tables? tia

Dwight

[Allen Browne]

County table: one record for each county, with a CountyID primary key

Person table: one record for each person, with a PersonID primary key

Marriage table: one record for each marriage. Fields:
MarriageID primary key
CountyID where the marriage was registered.
BookID the book where this entry is found.
Page the page number/registry number.
RegistryID if the county has a unique registry number to enter.
GroomID relates to the PersonID field in Person table.
BrideID relates to the PersonID field in Person table.
LicenseIssuedDate When the marraiage was registered.

Since the Marriage involves 2 persons, you will need to add a 2nd copy of
the Person table to the Relations window (Tools | Relationhips). Access will
alias this as Person_1. You can now create the 2 relations from:
Person.PersonID to Marriage.GroomID
Person_1.PersonID to Marriage.BrideID

Now to answer your question, you can use a combination of natural keys (such
as State + County) for simple tables. But for the people, the natural keys
are probably too obsucre. You could use a combination of the person's name,
birthplace, and date of birth, but you are then stymied for someone who has
any one of these items unknown.

Rather than storing the age of the person at marriage, it would be better to
store the BirthDate in the Person table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dwight" <ed*******@comcast.netwrote in message
news:sa******************************@comcast.com. ..

>
Iam a first time user of any database. I am using A2003. I want to design
a database for county marriage records. So far, I have the following
tables:

COUNTY: County GROOM: SurnameGr BRIDE: SurnameBr
MARRIAGEDATA: LicenseIssuedDate
Book GivenNameGr GivenNameBr
MarriageDate
Page AgeGr AgeBr
MarriagePerformedBy

My question is should I allow Access to use the Autonumber or can I use a
multifield key. I would like to use the three fields in the COUNTY table
as the key since all the other info comes from them. There are no common
fields in the other tables. How can I connect the 4 tables? tia

Dwight

[comteck]

Your main objective with any database is to use as little space as possible, which is why an ID of some sort is best used as a PK (autonumber works best for the main table). For example, in your case, let's call the PK of the main table RECORDID (data type is autonumber).

To link to another table add 2 ID fields, one being RECORDID and another ID (say MARRIAGEID), and make both those IDs the PKs for the second table (the data type for these would be "Number" not "Auronumber"). Then you would create a one to many relationship between the 2 tables (the main table would be one, and the second table would be many).

If you were to create a subform on your main form (the subform's Record Source being the second table, and the main form's Record Source being the main table), Access would automatically create a link between the 2 forms due to the relationship that was created.

This is the standard way of creating links between 2 or more tables in access.

Hope this helps.
comteck

[Anne Nolan]

In this case, however, he probably doesn't have birthdates for the people,
just the age info from the marriage license, so it makes sense to store it.
After all, "age at marriage" won't need to be changed as the years go by,
anyway.

Anne (who also does genealogy)

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...

County table: one record for each county, with a CountyID primary key

Person table: one record for each person, with a PersonID primary key

Marriage table: one record for each marriage. Fields:
MarriageID primary key
CountyID where the marriage was registered.
BookID the book where this entry is found.
Page the page number/registry number.
RegistryID if the county has a unique registry number to enter.
GroomID relates to the PersonID field in Person table.
BrideID relates to the PersonID field in Person table.
LicenseIssuedDate When the marraiage was registered.

Since the Marriage involves 2 persons, you will need to add a 2nd copy of
the Person table to the Relations window (Tools | Relationhips). Access
will alias this as Person_1. You can now create the 2 relations from:
Person.PersonID to Marriage.GroomID
Person_1.PersonID to Marriage.BrideID

Now to answer your question, you can use a combination of natural keys
(such as State + County) for simple tables. But for the people, the
natural keys are probably too obsucre. You could use a combination of the
person's name, birthplace, and date of birth, but you are then stymied for
someone who has any one of these items unknown.

Rather than storing the age of the person at marriage, it would be better
to store the BirthDate in the Person table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dwight" <ed*******@comcast.netwrote in message
news:sa******************************@comcast.com. ..

>>
Iam a first time user of any database. I am using A2003. I want to design
a database for county marriage records. So far, I have the following
tables:

COUNTY: County GROOM: SurnameGr BRIDE: SurnameBr
MARRIAGEDATA: LicenseIssuedDate
Book GivenNameGr GivenNameBr
MarriageDate
Page AgeGr AgeBr
MarriagePerformedBy

My question is should I allow Access to use the Autonumber or can I use a
multifield key. I would like to use the three fields in the COUNTY table
as the key since all the other info comes from them. There are no common
fields in the other tables. How can I connect the 4 tables? tia

Dwight

[Allen Browne]

You may be right, Anne.

It seems to me that the person's age is an attribute of the person, not an
attribute of the marriage. Storing age-at-marriage + date-of-marriage
contains redundant data, and therefore introduces the possibility of
inconconsistency (e.g. someone was recorded as 20 when they married in 1900,
and 22 when they married again in 1910.)

But, as you point out, the OP may only have the data that way, and may
*want* to record the fact that the data is inconsistent.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Anne Nolan" <an***************@AOL.COMwrote in message
news:4h************@individual.net...

In this case, however, he probably doesn't have birthdates for the people,
just the age info from the marriage license, so it makes sense to store
it. After all, "age at marriage" won't need to be changed as the years go
by, anyway.

Anne (who also does genealogy)

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...

>County table: one record for each county, with a CountyID primary key

Person table: one record for each person, with a PersonID primary key

Marriage table: one record for each marriage. Fields:
MarriageID primary key
CountyID where the marriage was registered.
BookID the book where this entry is found.
Page the page number/registry number.
RegistryID if the county has a unique registry number to enter.
GroomID relates to the PersonID field in Person table.
BrideID relates to the PersonID field in Person table.
LicenseIssuedDate When the marraiage was registered.

Since the Marriage involves 2 persons, you will need to add a 2nd copy of
the Person table to the Relations window (Tools | Relationhips). Access
will alias this as Person_1. You can now create the 2 relations from:
Person.PersonID to Marriage.GroomID
Person_1.PersonID to Marriage.BrideID

Now to answer your question, you can use a combination of natural keys
(such as State + County) for simple tables. But for the people, the
natural keys are probably too obsucre. You could use a combination of the
person's name, birthplace, and date of birth, but you are then stymied
for someone who has any one of these items unknown.

Rather than storing the age of the person at marriage, it would be better
to store the BirthDate in the Person table.

--
"Dwight" <ed*******@comcast.netwrote in message
news:sa******************************@comcast.com ...

>>>
Iam a first time user of any database. I am using A2003. I want to
design a database for county marriage records. So far, I have the
following tables:

COUNTY: County GROOM: SurnameGr BRIDE: SurnameBr
MARRIAGEDATA: LicenseIssuedDate
Book GivenNameGr GivenNameBr
MarriageDate
Page AgeGr AgeBr
MarriagePerformedBy

My question is should I allow Access to use the Autonumber or can I use
a multifield key. I would like to use the three fields in the COUNTY
table as the key since all the other info comes from them. There are no
common fields in the other tables. How can I connect the 4 tables? tia

Dwight

[Anne Nolan]

If you want to see inconsistencies in ages, try recording ages as reported
in census data over the years. I've seen people age only 6 years during the
10 years between censuses. LOL!

You learn to take that info with a large grain of salt. :)

About the only way to have a reliable birthdate is to have a birth
certificate.

Cheers,

Anne

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...

You may be right, Anne.

It seems to me that the person's age is an attribute of the person, not an
attribute of the marriage. Storing age-at-marriage + date-of-marriage
contains redundant data, and therefore introduces the possibility of
inconconsistency (e.g. someone was recorded as 20 when they married in
1900, and 22 when they married again in 1910.)

But, as you point out, the OP may only have the data that way, and may
*want* to record the fact that the data is inconsistent.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Anne Nolan" <an***************@AOL.COMwrote in message
news:4h************@individual.net...

>In this case, however, he probably doesn't have birthdates for the
people, just the age info from the marriage license, so it makes sense to
store it. After all, "age at marriage" won't need to be changed as the
years go by, anyway.

Anne (who also does genealogy)

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...

>>County table: one record for each county, with a CountyID primary key

Person table: one record for each person, with a PersonID primary key

Marriage table: one record for each marriage. Fields:
MarriageID primary key
CountyID where the marriage was registered.
BookID the book where this entry is found.
Page the page number/registry number.
RegistryID if the county has a unique registry number to enter.
GroomID relates to the PersonID field in Person table.
BrideID relates to the PersonID field in Person table.
LicenseIssuedDate When the marraiage was registered.

Since the Marriage involves 2 persons, you will need to add a 2nd copy
of the Person table to the Relations window (Tools | Relationhips).
Access will alias this as Person_1. You can now create the 2 relations
from:
Person.PersonID to Marriage.GroomID
Person_1.PersonID to Marriage.BrideID

Now to answer your question, you can use a combination of natural keys
(such as State + County) for simple tables. But for the people, the
natural keys are probably too obsucre. You could use a combination of
the person's name, birthplace, and date of birth, but you are then
stymied for someone who has any one of these items unknown.

Rather than storing the age of the person at marriage, it would be
better to store the BirthDate in the Person table.

--
"Dwight" <ed*******@comcast.netwrote in message
news:sa******************************@comcast.co m...

Iam a first time user of any database. I am using A2003. I want to
design a database for county marriage records. So far, I have the
following tables:

COUNTY: County GROOM: SurnameGr BRIDE: SurnameBr
MARRIAGEDATA: LicenseIssuedDate
Book GivenNameGr GivenNameBr
MarriageDate
Page AgeGr AgeBr
MarriagePerformedBy

My question is should I allow Access to use the Autonumber or can I use
a multifield key. I would like to use the three fields in the COUNTY
table as the key since all the other info comes from them. There are no
common fields in the other tables. How can I connect the 4 tables? tia

Dwight