A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Staff teaches multiple programs



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2009, 02:50 PM posted to microsoft.public.access.tablesdbdesign
Cindy
external usenet poster
 
Posts: 385
Default Staff teaches multiple programs

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.
  #2  
Old May 11th, 2009, 04:25 PM posted to microsoft.public.access.tablesdbdesign
BruceM[_4_]
external usenet poster
 
Posts: 558
Default Staff teaches multiple programs

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



  #3  
Old May 11th, 2009, 08:24 PM posted to microsoft.public.access.tablesdbdesign
Cindy
external usenet poster
 
Posts: 385
Default Staff teaches multiple programs

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.
  #4  
Old May 12th, 2009, 03:55 AM posted to microsoft.public.access.tablesdbdesign
Piet Linden[_2_]
external usenet poster
 
Posts: 280
Default Staff teaches multiple programs

On May 11, 2:24*pm, Cindy wrote:
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 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.


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.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:52 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.