Trouble with relationship

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.
 
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?
 
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.
 
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!
 
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?
Click to expand...

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.
 
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.
Click to expand...

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?
 
Okay, started on this and before I get too in depth, tell me if I am on the
right track. I've gotten 35 tables so far on the board. Most of them are
simple, 2-3 fields with a FK (i.e. tblSex). In the "main" tblEmployees, I
have only LName, FName, MI, SSN (as the PK) and DateOfBirth. I think I'm on
the right track after reading the article and more on the web last night.

Question, though. In my draft version, tblSex had only one field with two
entries (Male and Female). I used this table as a combo box for the forms. I
am thinking I should be storing each persons sex in the table instead. But if
I do that, I would end up with a combo box of ???? So do I make two tables,
one to store the data and one to "drive" the cbo?
 
One way of posting your tables is to show the name of the table, and then the
important fields, such as keys. I would post your Employees table like this:

tbl_Employees
SSN (PK)
EmployeeInfo (meaning names, birthdate. These don't usually have anything
to do with the actual relationship of the tables to each other, so I group
them under Info)

Notes inline
Okay, started on this and before I get too in depth, tell me if I am on the
right track. I've gotten 35 tables so far on the board.
Click to expand...

35 tables does seem like a lot, but you probably have a lot of peripheral
tables in that, where information can just be looked up, thus saving a lot of
typing, and making your database well normalized.
Most of them are
simple, 2-3 fields with a FK (i.e. tblSex).
Click to expand...

Another way of handling a field such as Gender (trying to be PC here), is to
simply use a field called Gender or Sex in your Employees table, and use a
Validation Rule and Validation Text to limit the choices. For a Gender
field, I would use a validation rule of: ="Male" Or ="Female" and also set
the field size to 6, and then use validation text of: Enter either Male or
Female. The reasoning behind this is, that you will probably never have to
worry about adding an additional gender to your Gender Table, so a repetition
of labels such as male and female isn't so bad. Another reason is, that once
you have put in the gender, the chances are very very slim that you would
have to change it. Especially in the military.
In the "main" tblEmployees, I
have only LName, FName, MI, SSN (as the PK) and DateOfBirth.
Click to expand...

As mentioned above, you could probably get away with putting gender into
this table. Though I now question why you have a tbl_Sex, when there is no
SexID in your Employees table. If you do create a table for repetitive data,
which will have a primary key, then you need to reference that data by using
a foreign key in the table which will reference that repetitive data. So,
your Employee table would look like this:

tbl_Employees
SSN (PK)
GenderID (FK)

tbl_Gender
GenderID (PK)
GenderText
I think I'm on
the right track after reading the article and more on the web last night.
Click to expand...

Remember, you are on a learning curve right now. We're only a few lessons
into this, so just relax and let these concepts sink in. If you can get your
tables done correctly from the start, you will do OK.
Question, though. In my draft version, tblSex had only one field with two
entries (Male and Female). I used this table as a combo box for the forms. I
am thinking I should be storing each persons sex in the table instead. But if
I do that, I would end up with a combo box of ???? So do I make two tables,
one to store the data and one to "drive" the cbo?
Click to expand...

Tables are simply for storing data. They are just a big box that all of the
data gets dumped into. A combo box is used on a form to simplify entering
data, usually by looking up a list of possible values, which are then used in
the current record. Don't worry so much about your forms just yet. Keep
trying various things, but remember that once your tables are set up, many of
the forms will set up much easier.
 
You have probably created some tables that don't seem to have a simple
relationship to each other. These will be many-to-many tables, where one
record in the first table can refer to more than one record in the second
table, and one record in the second table can refer to more than one record
in the first table. You could have people and classes, where people have
attended more than one class, and one class can be attended by many people.

When there are values in two tables that relate many-to-many, you will need
to create a linking table between those two tables. The linking table will
have its own unique primary key, and will use a foreign key that relates to a
person, and a foreign key that relates to a training class. This will enable
you to have numerous classes for each person, or numerous people for each
class.

Your tables might look like this:

tbl_Employees
EmployeeID (PK)

tbl_Classes
ClassID (PK)

tbl_EmployeeClasses
EmployeeClassID (PK)
EmployeeID (FK)
ClassID (FK)

You will probably have other information about the class in the
EmployeeClasses table, such as date, location, etc., that will help to
identify the unique class that a person takes.
 
Okay, here goes. I eliminated some uneeded fields and ended up with 21 tables.

tblPhysicalFitness
APFTID (PK)
Stores raw score and total score data for soldiers

tblAwards
AwardID (PK)
Award info (type of award and number of that type)

tblClothing
ClothingID
Seven types of clothing and thier sizes

tblPersonnel
SSN (PK)
Just like your tblEmployee

tblCompetency
CompID (PK)
30 different competency items and their date

Hope that gives you an idea of what I did and what I did WRONG :). I read
your later post about the linking tables, but how will that work later when I
need to find all the RN's who have not renewed a specific class? Also, I'm
worried since I will have to use subforms so I can get a "snapshot" of all
the data on one person, that I won't be able to perform some calculations
between two fields in different tables. Thoughts?

Thanks so much for your help! Ifeel like I am actually learning something! :)
 
A quick observation. Do you have groups of 30 fields in tblCompetency? This
would be a mistake. You should have a competency lookup table with 30
records (one per competency). Then have a junction table of Personnel
Competencies like:

tblCompetencies
CompetencyID
CompetencyTitle
CompetencyStatus

tblPersComp
SSN (shudder)
CompetencyID
CompetencyEarnedDate

You can add competency records and types without having to add fields or
change forms or reports. This solution is called "normalization".
 
mnature said:
Another way of handling a field such as Gender (trying to be PC here), is to
simply use a field called Gender or Sex in your Employees table, and use a
Validation Rule and Validation Text to limit the choices. For a Gender
field, I would use a validation rule of: ="Male" Or ="Female" and also set
the field size to 6, and then use validation text of: Enter either Male or
Female.
Click to expand...

Further suggestion: use the ISO sex codes: 0 (unknown), 1 (male), 2
(female), 9 (legal person e.g. corporate organization) then restrict
values based on context e.g. employee_sex_code = 9,
military_personnel_sex_code = 0 may be absurd values.

Jamie.

--
 
A little confused. The tblCompetency has 30 fields. Each field is a different
competency (Ventilator Managment, Intra-Aortic Ballon Pump, etc). I see your
point in doing the two tables. But why is it a bad idea to do it the way I
have? I ask this because I wanted a form to show all the competencies
available. Depending on what ward they worked ( Surgical ICU vs Medical ICU),
it would show the competencies they HAD to have and grey out (using
conditional formatting) the ones they didn't have to do (so that if they did
the extra ones we could still enter the date). Thanks for helping a novice
out. A had done some simple designs before, but nothing as complicated as
this. Dan
 
What happens when (not if) you need to add a new competency?
You must use expressions in conditional formatting to manage
ward/competencies. This should be done with data in tables.
tblWards
WardID
WardTitle

tblWardCompetencies
WardCompID
WardID
CompetencyID

Having competencies as fields is just too much to maintain in terms of
fields, controls, code, queries, forms, reports,... Maintain this
information should be done in data. If you add a new competency (or ward)
this should all be done in data without touching your table structures,
forms, code, conditional formatting,...

This is how those of us with lots of experience would create the system.
 
I get what your saying...so when the other departments see what I have done
(with EVERYONES help :) and they want to use it, all I have to do is add
their wards and competencies and not a bunch of fields.....right?

slowly but surely....
 
Back
Top