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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|