Trouble with relationship

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?

"mnature" 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.

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.

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.

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).

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.

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.

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?

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!

"mnature" wrote:

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.

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).

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.

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.

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?

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.

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".

--
Duane Hookom
MS Access MVP

"SFC Traver" wrote in message
...
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!


"mnature" wrote:

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.

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).

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.

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.

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?

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.

mnature wrote:
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.

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

"Duane Hookom" wrote:

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".

--
Duane Hookom
MS Access MVP

"SFC Traver" wrote in message
...
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!


"mnature" wrote:

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.

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).

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.

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.

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?

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.

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.

--
Duane Hookom
MS Access MVP

"SFC Traver" wrote in message
news
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

"Duane Hookom" wrote:

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".

--
Duane Hookom
MS Access MVP

"SFC Traver" wrote in message
...
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!


"mnature" wrote:

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.

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).

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.

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.

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?

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.

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....

"SFC Traver" wrote:

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

"Duane Hookom" wrote:

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".

--
Duane Hookom
MS Access MVP

"SFC Traver" wrote in message
...
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!


"mnature" wrote:

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.

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).

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.

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.

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?

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 are beginning to understand.
--
Duane Hookom
MS Access MVP

"SFC Traver" wrote in message
...
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....

"SFC Traver" wrote:

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

"Duane Hookom" wrote:

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".

--
Duane Hookom
MS Access MVP

"SFC Traver" wrote in message
...
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!


"mnature" wrote:

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.

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).

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.

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.

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?

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.