Trouble with relationship

I have three tables (tblData, tblMilitaryData, and tblCompetency). All have
the SSN as the primary key. I cannot get them to have a one-to-one
relationship. Why?

I have to have them in separate tables because I ran out of the 255 fields
in tblData.

Access wants to do a one-to-many, but there is only ONE person for each tbl.

HELP! Thanks

Before you get too far with this application, you might want to evaluate why
you need so many fields. Your application appears to need some
normalization.
--
Duane Hookom
MS Access MVP


"SFC Traver" wrote in message
...
I have three tables (tblData, tblMilitaryData, and tblCompetency). All have
the SSN as the primary key. I cannot get them to have a one-to-one
relationship. Why?

I have to have them in separate tables because I ran out of the 255 fields
in tblData.

Access wants to do a one-to-many, but there is only ONE person for each
tbl.

HELP! Thanks

I'm with Duane -- it is rare to find a well-normalized table that requires
more than 30 fields.

Perhaps you are treating Access as if it were a spreadsheet?

As Duane suggests, spend some time considering your data structure before
you try to establish relationships.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"SFC Traver" wrote in message
...
I have three tables (tblData, tblMilitaryData, and tblCompetency). All have
the SSN as the primary key. I cannot get them to have a one-to-one
relationship. Why?

I have to have them in separate tables because I ran out of the 255 fields
in tblData.

Access wants to do a one-to-many, but there is only ONE person for each
tbl.

HELP! Thanks

The USAF to the rescue yet again! ;-) A retired E-8 at that.

For a 1-1 relationship to work, you need one record with the same primary
key in all three tables. If any table has a duplicate SSAN or is missing a
matching SSAN, Access can't create a 1-1 relationship.

Normally I would caution against using the SSAN for anything, but as the US
military uses it as the service number, it's 'OK' this time just as long as
it's only military members in the tables. One foriegn national and all bets
are off.

What Duane said is very, very true about needing so many fields. Do you have
repeating data? Are you doing things like a different training course in each
column? I highly suggest getting some relational database training or
reading "Database Design for Mere Mortals" by Hernandez before proceeding any
further on this database. If you happen to be working a 'purple suit' job at
HQ Transcom, I teach Access at SWIC.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"SFC Traver" wrote:

I have three tables (tblData, tblMilitaryData, and tblCompetency). All have
the SSN as the primary key. I cannot get them to have a one-to-one
relationship. Why?

I have to have them in separate tables because I ran out of the 255 fields
in tblData.

Access wants to do a one-to-many, but there is only ONE person for each tbl.

HELP! Thanks

To a certain extent you are right about treating it as if it were a
spreadsheet. Trying to track training/competency/medical/military data on
over 200+ employees (that's why sooo many fields). Everyone is using
spreadsheets now. Boss wants to combine all the data from the different wards
together (centralize the data so everyone can "see" and use) and use querie
and report capabilities that you just can't get in excel.

Bad idea?

"Jeff Boyce" wrote:

I'm with Duane -- it is rare to find a well-normalized table that requires
more than 30 fields.

Perhaps you are treating Access as if it were a spreadsheet?

As Duane suggests, spend some time considering your data structure before
you try to establish relationships.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"SFC Traver" wrote in message
...
I have three tables (tblData, tblMilitaryData, and tblCompetency). All have
the SSN as the primary key. I cannot get them to have a one-to-one
relationship. Why?

I have to have them in separate tables because I ran out of the 255 fields
in tblData.

Access wants to do a one-to-many, but there is only ONE person for each
tbl.

HELP! Thanks

Try looking here for tips on normalization:

http://support.microsoft.com/kb/283878/EN-US/

Basically . . .

1) Don't repeat groups in individual tables, such as vendor1, vendor2,
vendor3.
2) Put all related data into their own tables.
3) Tables should only contain data that is related to each other in some way.
4) Use primary keys to identify the related data that is in its own table.

Once you have done those, then

5) Create separate tables for values that apply to multiple records. Such
as, when you use a rank to identify someone, you can put all ranks into a
table, then use a foreign key to signify which rank applies to a soldier.

If you have worked with spreadsheets in the past, you will have to relearn
everything you think you know about data management. A table might bear a
passing resemblance to a spreadsheet, but a database is not a spreadsheet.

"SFC Traver" wrote:

I have three tables (tblData, tblMilitaryData, and tblCompetency). All have
the SSN as the primary key. I cannot get them to have a one-to-one
relationship. Why?

I have to have them in separate tables because I ran out of the 255 fields
in tblData.

Access wants to do a one-to-many, but there is only ONE person for each tbl.

HELP! Thanks

Since your retired military, albiet AF you know what kind of tracking we
have to do for training, shots, medical, and through into it since we are a
hospital, all the JCAHO stuff. Just a thought with all the recommendations. I
was planning on using a form to enter/edit/view all the data. Would have
tabs/pages so I could fit all the data in one "screenshot". Would breaking
the tables up into what is going to be on the tabs/pages be smart.

Thanks!

"Jerry Whittle" wrote:

The USAF to the rescue yet again! ;-) A retired E-8 at that.

For a 1-1 relationship to work, you need one record with the same primary
key in all three tables. If any table has a duplicate SSAN or is missing a
matching SSAN, Access can't create a 1-1 relationship.

Normally I would caution against using the SSAN for anything, but as the US
military uses it as the service number, it's 'OK' this time just as long as
it's only military members in the tables. One foriegn national and all bets
are off.

What Duane said is very, very true about needing so many fields. Do you have
repeating data? Are you doing things like a different training course in each
column? I highly suggest getting some relational database training or
reading "Database Design for Mere Mortals" by Hernandez before proceeding any
further on this database. If you happen to be working a 'purple suit' job at
HQ Transcom, I teach Access at SWIC.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"SFC Traver" wrote:

I have three tables (tblData, tblMilitaryData, and tblCompetency). All have
the SSN as the primary key. I cannot get them to have a one-to-one
relationship. Why?

I have to have them in separate tables because I ran out of the 255 fields
in tblData.

Access wants to do a one-to-many, but there is only ONE person for each tbl.

HELP! Thanks

To a certain extent you are right about treating it as if it were a
spreadsheet. Trying to track training/competency/medical/military data on
over 200+ employees (that's why sooo many fields). Everyone is using
spreadsheets now. Boss wants to combine all the data from the different wards
together (centralize the data so everyone can "see" and use) and use querie
and report capabilities that you just can't get in excel.

Bad idea?

Bad, bad, bad idea. But you have come to the right place for help.

Start with just one table, which will be Employees. What data do you have
that relates directly and specifically to employees? You will have name,
rank, serial number (OK, too many old war movies . . . social security
number). What won't you have in this table? You won't have anything that
should be in a separate table, such as training, competencies, medical,
military service. Those will be separate, because they are things that your
employees do or have happen to them. In addition, you can have multiple
employees who train to the same thing, or multiple competencies that apply to
one employee.

So get out pencil and paper, and write down everything that applies directly
to an employee, then write down everything that applies to training,
competencies, etc. This will give you your core tables of data. Once you
have done that, then come back here, and we will help you build the tables
that link all of this data together.

On Wed, 14 Jun 2006 13:02:02 -0700, SFC Traver
wrote:

To a certain extent you are right about treating it as if it were a
spreadsheet. Trying to track training/competency/medical/military data on
over 200+ employees (that's why sooo many fields). Everyone is using
spreadsheets now. Boss wants to combine all the data from the different wards
together (centralize the data so everyone can "see" and use) and use querie
and report capabilities that you just can't get in excel.

To do so... you MUST, no option, use Access as it was designed: a
relational database. Using it as "a big spreadsheet with better query
and report capabilities" is a quick road to grief and woe.

Read mnature's suggestions carefully. If you have 200 employees now...
you may have 220 employees next month, and you do NOT want to change
the structure of all your Tables, Queries, Reports, and Forms every
time there's a new employee! "Fields are expensive, records are
cheap"!

John W. Vinson[MVP]

Thanks for the help. I'll get it all down on paper/dry erase today. What's
the easiest way to post it?

"mnature" wrote:

Bad, bad, bad idea. But you have come to the right place for help.

Start with just one table, which will be Employees. What data do you have
that relates directly and specifically to employees? You will have name,
rank, serial number (OK, too many old war movies . . . social security
number). What won't you have in this table? You won't have anything that
should be in a separate table, such as training, competencies, medical,
military service. Those will be separate, because they are things that your
employees do or have happen to them. In addition, you can have multiple
employees who train to the same thing, or multiple competencies that apply to
one employee.

So get out pencil and paper, and write down everything that applies directly
to an employee, then write down everything that applies to training,
competencies, etc. This will give you your core tables of data. Once you
have done that, then come back here, and we will help you build the tables
that link all of this data together.