 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / June 2004Add new tables to database
Hi, I have a database with a table of employees. tblEmployees numEmployeeID (autonumber) PK strEmpLastName We need to keep track of total hours worked in 5 different work areas. My plan is to add 2 more tables. tblWorkArea numWorkAreaID(autonumberPK) strWorkAreaName tblAssignments numAssignmentID(autonumberPK) numEmployeeID numWorkAreaID dttAssignmentDate numAssignmentHours Is this right? Employees can work in several areas on the same day. The point is to keep track of total hours worked in each area so we can assign them equally when possible. Thanks, Linda Linda, Looks good to me.  SignatureSteve Schapel, Microsoft Access MVP > Hi, > [quoted text clipped - 25 lines] > Thanks, > Linda Hmm. After staring at the tables in the relationships view for a while, I thought I needed to add Employee ID to the tblWorkedAreas or will the Employee ID in the tblAssignments link the employees with worked areas?....I am so confused about how these relationships work. My final goal is to have a report that will look like a spreadsheet with all employees names showing Vertically and the worked areas across the top. Then the total hours that employee has worked in an area will be totaled up by their name under the work area. CV ER NICU Linda 12 36 0 Steve 6 54 12 If Steve and Linda are working today, I would send Linda to NICU, and Steve to CV to even up the hours. Thanks, Linda > Linda, > [quoted text clipped - 29 lines] > > Thanks, > > Linda Lida, No, it is not correct to put Employee ID in tblWorkAreas table. TblAssignments table is where you see which employee works in which work area. Your original design is correct. There is a one-to-many relationship between Employee and Assignment, and a one-to-many relationship between WorkArea and Assignment. The Employees table is where you store employee-specific information. THe WorkAreas table is where you store information specific to the work areas (which has got nothing to do with employees). And the Assignment table is where you store information about each "event" where a particular employee works in a particular work area. When it comes to the point of getting the report as you require, you will use a Crosstab Query as the basis of the report. SignatureSteve Schapel, Microsoft Access MVP > Hmm. After staring at the tables in the relationships view for a while, I > thought I needed to add Employee ID to the tblWorkedAreas or will the [quoted text clipped - 15 lines] > Thanks, > Linda Ok. I'll keep the faith. I have been reading a few books, they all keep saying the same thing when it comes to normalization and table structure and I keep getting the feeling that I don't understand, then I read the next book and it says the same thing but I still can't grasp it so I guess I sort of know what they are telling me to do and I'll go to the next step and post on the relationships board, then I'll post on the forms board because I think I need to make this Assignment table a subform on the Employees form. I'll trudge on.... Thanks a million Linda > Lida, > [quoted text clipped - 31 lines] > > Thanks, > > Linda > I keep getting the feeling that I don't understand, then I read the next Hi Linda, It takes time to understand normalization, so don't doubt yourself. Keep at it until third normal form clicks in your head and then give it a rest. If you can get your db into 3NF, you're in good shape Your task might be easier if you first spend time learning how to make entity relationship models. Jay Linda, Yes, it would be good to have a form based on the Assignment table as a subform on your Empoyees form. You could also have a form based on the Assignments form as a subform on the WorkAreas form. You could have both, that would probably be a good idea. I *imagine* in practice entering the Assignment data via the WorkAreas form would prove to be the most useful. Happy trudging! SignatureSteve Schapel, Microsoft Access MVP > Ok. I'll keep the faith. I have been reading a few books, they all keep > saying the same thing when it comes to normalization and table structure and [quoted text clipped - 9 lines] > > Linda There can be 2 or more employees assigned to 1 work area. Does that make any difference? Thanks, Linda > Lida, > [quoted text clipped - 31 lines] > > Thanks, > > Linda Linda, I knew that :-) No, it doesn't make any difference, in fact that's the whole point... your proposed table structure allows for this, as it reflects the real-life relationships between the real-life data elements. The data in the Assignments table will look something like this: AssignmentID EmployeeID WorkArea AssDate Hours 1 1 CV 28/6/04 12 2 2 ER 28/6/04 8 3 1 ER 29/6/04 8 4 2 NICU 29/6/04 6 5 2 CV 29/6/04 6 6 1 NICU 30/6/04 12 7 2 NICU 30/6/04 12 ... so you see, it allows for any given Work Area to have more than one Employee working there on any given day, and it allows for any given Employee to work in more than one Work Area, including on any given day. As I understand your requirements, this model should be ideal for your purposes. And you will be able to easily use queries to obtain summaries, per Employee, or per Work Area, or per date. SignatureSteve Schapel, Microsoft Access MVP > There can be 2 or more employees assigned to 1 work area. Does that make > any difference? > > Thanks, > Linda Faith...I need to have faith...Thanks > Linda, > [quoted text clipped - 23 lines] > > Thanks, > > Linda Hi LMB, If an employee can work in more than 1 area in the same day, then they can probably work in an area more than once in the same day. To account for this possiblity, I'd remove tblAssignments.numAssignmentHours and replace it with columns for StartTime and EndTime. You can calculate the total hours at runtime. Jay They are assigned an area for the entire time they work. Most are 12 hour blocks but sometimes it may be 4 or 6 hours and that's about it. I thought about including start time and end time but I think the supervisors would rather just type in 1 number per area instead of 2 for each area worked. Thanks! Linda > Hi LMB, > > If an employee can work in more than 1 area in the same day, then they can probably work in an area more than once in the same day. > > To account for this possiblity, I'd remove tblAssignments.numAssignmentHours and replace it with columns for StartTime and EndTime. You can calculate the total hours at runtime. > Jay |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.