Staff teaches multiple programs

[Cindy]

I'm trying to set up a database in Access for our Instructors but am running
into a problem. Some instructors teach multiple programs which in turn has
different Licenses and Permit codes. NEXT Problem: ALSO, we keep track of
our TIME SHEET hrs. We get paid every two weeks and keep track of hrs wrkd
per person/program. Example: Payday 1-AUG-2008, Debi taught 15 hrs in Nurse
Aide, 8 hrs in Patient Care Tech, 24 hrs in Practical Nursing. How do I set
up my table.

 

[BruceM]

You need to set up multiple tables, not one table. Each table contains
information about a single entity. From what I can tell you will need at a
minimum tables for Instructor, Program, and HoursWorked. If a program may
be taught by more than one instructor, and/or if a program may be in several
locations or at several different times, and/or if you want a record of past
programs and/or if you want to keep track of which students are taking which
programs you will need additional tables.

The point here is that it is really not possible to describe a table
structure without knowing a lot more information about the real-world
situation you are trying to address. Even knowing that, talking you through
creating a database may be more help than you will be able to find in a
volunteer newsgroup. You have not mentioned anything about your experience
with Access, but if you are just starting out you have a steep learning
curve ahead of you, as you have outlined a rather complex beginning project.

For an excellent introduction to Access:
http://allenbrowne.com/casu-22.html

There are further links on that page.

If I have misunderstood your experience level, I suggest a more thorough
description of the specific problems you are trying to address through use
of the database. In any case, breaking it down into one or other of the
project's separate components (programs taught or hours worked) may be a
good starting point.

 

[Cindy]

My table consists of: First Name, Last Name, Middle Name, Address, City,
State, Zip, Country, E-mail Work, E-mail Personal, Date of Birth, Title (Drop
down list of Coord, Admin, Instructor, Secretary), Active/Purged (Drop down
list Active, Purged), Home Phone, Mobile Phone, Program Name (Drop down list
of programs we have), Term Name, Term Length, Certification/Licensure/Degree
(Drop down list: HS Diploma, Associate, Bach, Mast, PhD), College/Univ, Date
of Completion, Date License Rcvd, Type of Permit (Drop down list of Permits
used), Permit Code, Type of License (Drop down list: Post-Secondary &
Secondary), License #, Year License Expires, Date of Hire, Step, Class,
Current Pay Rate, SS#, Emergency Contact Name, Phone #1, Phone #2,
Relationship, Physican Name, Physician Phone Number, Hospital
Name.............NOW including all the above for each Staff Member, we need
to keep track of Yearly dates of Fingerprinting, Evaluations and who they
were completed by, Pay Rate as of (DATE) and (AMT), Time Sheet Hrs Wrkd by
listing of Paydays and hours wrkd for each Payday such as 4-JUL-2008 25 hrs,
18-JUL-2008 36 hrs, 1-AUG-2008 34 hrs, 15-AUG-2008 29 hrs,
etc............then we run into some staff works in more than one Program so
therefore we have multiple time sheets (per program) for the same person.
Example: Tracy teaches computer classes to PN program, Elect program, NA
program, Diesel program. Debi teaches CPR to all programs plus teaches NA
full time so some paydays Debi may have only one time sheet and others she
may have 3 or 4 time sheets per payday........
FYI: It has been at least 4 yrs since I have worked with Access, however, I
have picked up on creating this table, doing drop down list, calendars,
attachments, etc, on this data base I'm trying to create,,,,,just having
problems on the multiple(s).......I feel like I'm picking it up like riding a
bike....but I'm falling quite a bit.....need your help. Actually any help
would be greatly appreciated. Thanks, Cindy
I hope this explains what I'm trying to do. If I need to send more let me
know.

 

[Piet Linden]

CREATE TABLE Person(
First Name,
Last Name,
Middle Name,
Address,
City,
State,
Zip, Country,
E-mail Work,
E-mail Personal,
Date of Birth,
Title (Drop down list of Coord, Admin, Instructor, Secretary),
Active/Purged (Drop down list Active, Purged),
Home Phone,
Mobile Phone,
Program Name (Drop down list of programs we have),
Term Name, Term Length, Certification/Licensure/Degree

(Drop down list: HS Diploma, Associate, Bach, Mast, PhD), College/Univ, Date
of Completion, Date License Rcvd, Type of Permit (Drop down list of Permits
used), Permit Code, Type of License (Drop down list: Post-Secondary &
Secondary), License #, Year License Expires, Date of Hire, Step, Class,
Current Pay Rate, SS#, Emergency Contact Name, Phone #1, Phone #2,
Relationship, Physican Name, Physician Phone Number, Hospital
Name.............NOW including all the above for each Staff Member, we need
to keep track of Yearly dates of Fingerprinting, Evaluations and who they
were completed by, Pay Rate as of (DATE) and (AMT), Time Sheet Hrs Wrkd by
listing of Paydays and hours wrkd for each Payday such as 4-JUL-2008 25 hrs,
18-JUL-2008 36 hrs, 1-AUG-2008 34 hrs, 15-AUG-2008 29 hrs,
etc............then we run into some staff works in more than one Programso
therefore we have multiple time sheets (per program) for the same person. 
Example: Tracy teaches computer classes to PN program, Elect program, NA
program, Diesel program.  Debi teaches CPR to all programs plus teachesNA
full time so some paydays Debi may have only one time sheet and others she
may have 3 or 4 time sheets per payday........
FYI: It has been at least 4 yrs since I have worked with Access, however,I
have picked up on creating this table, doing drop down list, calendars,
attachments, etc, on this data base I'm trying to create,,,,,just having
problems on the multiple(s).......I feel like I'm picking it up like riding a
bike....but I'm falling quite a bit.....need your help.  Actually any help
would be greatly appreciated.  Thanks, Cindy
I hope this explains what I'm trying to do.  If I need to send more letme
know.

First of all, you need to normalize. In plain English, each table
should describe ONE real world entity (or thing, like Person,
Course,... things about which you want to store multiple facts.)
Putting everything in one table is a spreadsheet. The problem you
will run into is trying to query this if you leave it as one table.

Looks a fair amount like the standard Students-Classes database.
Student (StudentID*, etc)
SectionRoster (StudentID*, SectionID*, Grade)
Section(SectionID*, CourseID, InstructorID)
Course(CourseID*, CourseTitle)

I think the first thing I would do is pare down the description of
what you're modeling to just nouns and verbs, because then you can
focus on the entities in your problem. Then model each sentence...
Each {Subject} can <verbs> One/Many {Object}
then switch Subject and object. If this is also true, it's a many-to-
many relationship and you need another table. ...
Normalization is a PITA, but it's critical to solid database design.
You might want to read Michael Hernandez's 'Database Design For Mere
Mortals.'.. he covers normalization extensively.