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
|
|||
|
|||
I think I'm almost there...except for...
my table relations. From left to right:
"Employees" Payroll (PK: 1) Employee Work Status Rate "week" Payroll (FK: Many) month week (PK: 1) "dept" Week (FK: many) Dept Subdept Costcentre Standard timehalf double So each person has multiple weeks and each week has multiple departments. But I get the following message: "You cannot add or a change a record because a related record is required in table 'week'. Can someone enlighten me? |
#2
|
|||
|
|||
I think I'm almost there...except for...
I don't think so....
Suggest you learn about normalisation and then things will just pop into place. Access loves a normalised database design; it loves to punish a design that is anything but. -- Slainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited "scubadiver" wrote in message ... my table relations. From left to right: "Employees" Payroll (PK: 1) Employee Work Status Rate "week" Payroll (FK: Many) month week (PK: 1) "dept" Week (FK: many) Dept Subdept Costcentre Standard timehalf double So each person has multiple weeks and each week has multiple departments. But I get the following message: "You cannot add or a change a record because a related record is required in table 'week'. Can someone enlighten me? |
#3
|
|||
|
|||
I think I'm almost there...except for...
With respect, that isn't the most constructive response.
I've tried to learn about normalization as much as I can and I'm getting very bored and frustrated when I get this kind of answer. I can't see where I'm going wrong. "Craig Alexander Morrison" wrote: I don't think so.... Suggest you learn about normalisation and then things will just pop into place. Access loves a normalised database design; it loves to punish a design that is anything but. -- Slainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited "scubadiver" wrote in message ... my table relations. From left to right: "Employees" Payroll (PK: 1) Employee Work Status Rate "week" Payroll (FK: Many) month week (PK: 1) "dept" Week (FK: many) Dept Subdept Costcentre Standard timehalf double So each person has multiple weeks and each week has multiple departments. But I get the following message: "You cannot add or a change a record because a related record is required in table 'week'. Can someone enlighten me? |
#4
|
|||
|
|||
I think I'm almost there...except for...
No, you're still not even close. Tables, when properly normalized, will
contain information that is related to each other. You will then link the normalized tables in ways that will allow you to build queries, forms, and reports. If you do the tables wrong, then you will always have problems with everything else. You need a table that is JUST employee information. This will be: Name, address, phone, full-time, part-time, hourly wage, etc. Don't put anything into this table unless it relates directly to the employee. tbl_Employees EmployeeID (PK) EmployeeInfo EmployeeStatusInfo EmployeeRateInfo You need a table that is JUST payroll information. This will be: Link to employee being paid, link to subdepartment paying the employee, the week during which the employee worked that they are getting paid for, and whatever multiplier is needed for this particular amount (1.0 for straight pay, 1.5 for time-and-a-half, and 2.0 for double time). Don't put anything into this table unless it relates directly to the payroll. You might have multiple entries in this table for any particular employee during any particular week, because they could work for several different departments during that week, or they could work for one department but part is straight time and part is overtime. This is where you relate an employee, a subdepartment (which then relates to a department), and a payroll amount. You will end up with a whole lot of entries in this table. tbl_Payroll PayrollID (PK) EmployeeID (FK) SubDepartmentID (FK) WeekNumber PayrollAmount PayrollMultiplier You need a table that is JUST for information about your departments. Don't put anything into this table unless it relates directly to the main departments. tbl_Departments DepartmentID (PK) DepartmentInfo You need a table that is JUST for information about your subdepartments. This will include a link to the main department for a particular subdepartment. Don't put anything into this table unless it relates directly to the subdepartments. It is through the subdepartment that you get a link to a department. tbl_SubDepartments SubDepartmentID (PK) DepartmentID (FK) SubDepartmentInfo |
#5
|
|||
|
|||
I think I'm almost there...except for...
What table are you attempting to edit when you get the error message? It
looks like you are attempting to add a record into the table Dept without having a record in the table Week with the Week value. This is very confusing when your fields and tables share the same name (Week-Week, Dept-Dept). Have you considered using a naming convention that doesn't allow function names as Month()? Also, fields like [Standard], [timehalf], and [Double] suggest an un-normalized table structure. You may find this subject boring but it helps to understand and get it right. -- Duane Hookom MS Access MVP "scubadiver" wrote in message ... my table relations. From left to right: "Employees" Payroll (PK: 1) Employee Work Status Rate "week" Payroll (FK: Many) month week (PK: 1) "dept" Week (FK: many) Dept Subdept Costcentre Standard timehalf double So each person has multiple weeks and each week has multiple departments. But I get the following message: "You cannot add or a change a record because a related record is required in table 'week'. Can someone enlighten me? |
#6
|
|||
|
|||
I think I'm almost there...except for...
tbl_Employees
EmployeeID (PK) EmployeeInfo EmployeeStatusInfo EmployeeRateInfo tbl_Payroll PayrollID (PK) EmployeeID (FK) SubDepartmentID (FK) WeekNumber PayrollAmount PayrollMultiplier tbl_Departments DepartmentID (PK) DepartmentInfo tbl_SubDepartments SubDepartmentID (PK) DepartmentID (FK) SubDepartmentInfo Once you set up these tables, make a query. Use the query wizard, and choose your payroll table. Choose all of the fields. Now choose your employee table, and choose all fields except for EmployeeID. Now choose your subdepartment table, and choose all of the fields except for SubDepartmentID. Finally, choose your department table, and choose everything except for DepartmentID. Finish building the query. Now make a form based on that query. Use the form wizard, and choose the query you just created. At some point it will give you several options of how to present your information. Choose "by Departments" and it will give you a main form, and two subforms. Complete the wizard and view the results. This will probably not be the way you want your information to look, but illustrates how you can take the data from tables and present them. |
#7
|
|||
|
|||
I think I'm almost there...except for...
Well, thankyou for that.
I will do as you suggest, but I can't see the point of a separate table for subdepartment. "mnature" wrote: No, you're still not even close. Tables, when properly normalized, will contain information that is related to each other. You will then link the normalized tables in ways that will allow you to build queries, forms, and reports. If you do the tables wrong, then you will always have problems with everything else. You need a table that is JUST employee information. This will be: Name, address, phone, full-time, part-time, hourly wage, etc. Don't put anything into this table unless it relates directly to the employee. tbl_Employees EmployeeID (PK) EmployeeInfo EmployeeStatusInfo EmployeeRateInfo You need a table that is JUST payroll information. This will be: Link to employee being paid, link to subdepartment paying the employee, the week during which the employee worked that they are getting paid for, and whatever multiplier is needed for this particular amount (1.0 for straight pay, 1.5 for time-and-a-half, and 2.0 for double time). Don't put anything into this table unless it relates directly to the payroll. You might have multiple entries in this table for any particular employee during any particular week, because they could work for several different departments during that week, or they could work for one department but part is straight time and part is overtime. This is where you relate an employee, a subdepartment (which then relates to a department), and a payroll amount. You will end up with a whole lot of entries in this table. tbl_Payroll PayrollID (PK) EmployeeID (FK) SubDepartmentID (FK) WeekNumber PayrollAmount PayrollMultiplier You need a table that is JUST for information about your departments. Don't put anything into this table unless it relates directly to the main departments. tbl_Departments DepartmentID (PK) DepartmentInfo You need a table that is JUST for information about your subdepartments. This will include a link to the main department for a particular subdepartment. Don't put anything into this table unless it relates directly to the subdepartments. It is through the subdepartment that you get a link to a department. tbl_SubDepartments SubDepartmentID (PK) DepartmentID (FK) SubDepartmentInfo |
#8
|
|||
|
|||
I think I'm almost there...except for...
You MUST learn about normalisation that is where you a going wrong.
You have ignored at least a dozen previous responses suggesting this course of action from various people. My comments were constructive and too the point. Over 90 Posts from you in the last and who knows how many answers from people trying to help you and still you won't invest your time in learning the basics. LEARN HOW TO NORMALISE YOUR DATA. -- Slainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited "scubadiver" wrote in message ... With respect, that isn't the most constructive response. I've tried to learn about normalization as much as I can and I'm getting very bored and frustrated when I get this kind of answer. I can't see where I'm going wrong. "Craig Alexander Morrison" wrote: I don't think so.... Suggest you learn about normalisation and then things will just pop into place. Access loves a normalised database design; it loves to punish a design that is anything but. -- Slainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited "scubadiver" wrote in message ... my table relations. From left to right: "Employees" Payroll (PK: 1) Employee Work Status Rate "week" Payroll (FK: Many) month week (PK: 1) "dept" Week (FK: many) Dept Subdept Costcentre Standard timehalf double So each person has multiple weeks and each week has multiple departments. But I get the following message: "You cannot add or a change a record because a related record is required in table 'week'. Can someone enlighten me? |
#9
|
|||
|
|||
I think I'm almost there...except for...
I will do as you suggest, but I can't see the point of a separate table
for subdepartment. LEARN ABOUT NORMALISATION SO THAT YOU CAN SEE THE POINT. I must take my blood pressure medication NOW. (vbg) -- Slainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited "scubadiver" wrote in message ... Well, thankyou for that. "mnature" wrote: No, you're still not even close. Tables, when properly normalized, will contain information that is related to each other. You will then link the normalized tables in ways that will allow you to build queries, forms, and reports. If you do the tables wrong, then you will always have problems with everything else. You need a table that is JUST employee information. This will be: Name, address, phone, full-time, part-time, hourly wage, etc. Don't put anything into this table unless it relates directly to the employee. tbl_Employees EmployeeID (PK) EmployeeInfo EmployeeStatusInfo EmployeeRateInfo You need a table that is JUST payroll information. This will be: Link to employee being paid, link to subdepartment paying the employee, the week during which the employee worked that they are getting paid for, and whatever multiplier is needed for this particular amount (1.0 for straight pay, 1.5 for time-and-a-half, and 2.0 for double time). Don't put anything into this table unless it relates directly to the payroll. You might have multiple entries in this table for any particular employee during any particular week, because they could work for several different departments during that week, or they could work for one department but part is straight time and part is overtime. This is where you relate an employee, a subdepartment (which then relates to a department), and a payroll amount. You will end up with a whole lot of entries in this table. tbl_Payroll PayrollID (PK) EmployeeID (FK) SubDepartmentID (FK) WeekNumber PayrollAmount PayrollMultiplier You need a table that is JUST for information about your departments. Don't put anything into this table unless it relates directly to the main departments. tbl_Departments DepartmentID (PK) DepartmentInfo You need a table that is JUST for information about your subdepartments. This will include a link to the main department for a particular subdepartment. Don't put anything into this table unless it relates directly to the subdepartments. It is through the subdepartment that you get a link to a department. tbl_SubDepartments SubDepartmentID (PK) DepartmentID (FK) SubDepartmentInfo |
#10
|
|||
|
|||
I think I'm almost there...except for...
scubadiver wrote: Suggest you learn about normalisation and then things will just pop into place. With respect, that isn't the most constructive response. I've tried to learn about normalization as much as I can and I'm getting very bored and frustrated when I get this kind of answer. I feel the same when I see such an answer. It may make you feel better to learn this secret: not everyone posting such answers understands normalization themselves. There is a reason why people say vague things like 'properly normalized'... When was the last time someone posted to this group a 'proper' answer i.e. a complete schema in DKNF g? Jamie. -- |
Thread Tools | |
Display Modes | |
|
|