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 |
#11
|
|||
|
|||
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. Let's say you have four departments: Management Billing Manufacturing Shipping Each of these departments has several subdepartments: Management: Owner, Business, Advertising, Lawsuits Billing: Accounts Receivable, Accounts Payable, Payroll Manufacturing: Maintenance, Widget Production, Gadget Assembly Shipping: Supplies, Postage You could have one table that would list all of these, but you would get some duplication: DepartmentName, SubdepartmentName Management, Owner Management, Business Management, Advertising Management, Lawsuits Billing, Accounts Receivable Billing, Accounts Payable Billing, Payroll Manufacturing, Maintenance Manufacturing, Widget Production Manufacturing, Gadget Assembly Shipping, Supplies Shipping, Postage As you see, you are duplicating the names of the departments. By separating the departments and subdepartments, you will have two table which do not have duplicates. DepartmentName: Management Billing Manufacturing Shipping SubDepartmentName: Owner Business Advertising Lawsuits Accounts Receivable Accounts Payable Payroll Maintenance Widget Production Gadget Assembly Supplies Postage |
#12
|
|||
|
|||
I think I'm almost there...except for...
In my current design I do have a main form with two subforms and it is what I
want. "mnature" wrote: 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. |
#13
|
|||
|
|||
I think I'm almost there...except for...
LEARN ABOUT NORMALISATION SO THAT YOU CAN SEE THE POINT. I must take my blood pressure medication NOW. (vbg) It's not the high blood pressure, so much as banging my head against the wall . . . ** bang ** ** ow ** ** bang ** ** ow ** ** bang ** ** ow ** |
#14
|
|||
|
|||
I think I'm almost there...except for...
There's no need for sarcasm.
The fields I listed in my original thread are really what I want and no more. Need some re-organising maybe. "mnature" wrote: LEARN ABOUT NORMALISATION SO THAT YOU CAN SEE THE POINT. I must take my blood pressure medication NOW. (vbg) It's not the high blood pressure, so much as banging my head against the wall . . . ** bang ** ** ow ** ** bang ** ** ow ** ** bang ** ** ow ** |
#15
|
|||
|
|||
I think I'm almost there...except for...
In my current design I do have a main form with two subforms and it is what
I want. You may want to have several main forms, with related subforms. The reason is, that you will want to display information in several ways. You can sort by departments, subdepartments, full-time, part-time. Totals can be per week. How much was paid in overtime? How many employees are in each department/subdepartment, and how many are full-time or part-time? What part of payroll goes to full-time, and what part goes to part-time? Which department/subdepartment pays out the most overtime? The point of a database is to have a pile of data, out of which you can mine a lot of information. Once you have accumulated enough data, then you can get some very valuable information from it. For instance, if you look at where most of your overtime is being paid through a year, then you can discuss whether some new employees need to be hired. |
#16
|
|||
|
|||
I think I'm almost there...except for...
Refer to Jamie Collins response.
"Craig Alexander Morrison" wrote: 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 |
#17
|
|||
|
|||
I think I'm almost there...except for...
Well, thank you for that Jamie,
I like your sincerity (I hope you are) and you are right. I get the feeling people on this board who like to consider themselves as experts aren't particularly helpful. Reading web pages don't particularly help when it comes to understanding table relations. "Jamie Collins" wrote: 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. -- |
#18
|
|||
|
|||
I think I'm almost there...except for...
Maybe its me, but there is no way to distinguish between contracted hours and
overtime. I don't need any extra departmental information apart from name. Anyway, I did as you said with a slight change: "Employee" EmployeeID (PK) Employee Status Rate "Payroll" Payroll (PK) Employee ID (FK) Subdepartment (FK) WeekID Amount "Subdepartment" DepartmentID SubdepartmentID (PK) I created a query from this but it gives one sub-form, not two, so maybe thats my fault. Thanks for the help and I think I can see where I am going wrong but telling me what fields you think I need may not be the same as what I think are necessary. I really want to normalise the fields I listed originally because they are the ones I need. (A) I would rather use PayrollID as employee ID since that is unique (B) I would like to distinguish between contracted hours and overtime. (C) I need to be sure that the hours entered relate to the correct department. "mnature" wrote: In my current design I do have a main form with two subforms and it is what I want. You may want to have several main forms, with related subforms. The reason is, that you will want to display information in several ways. You can sort by departments, subdepartments, full-time, part-time. Totals can be per week. How much was paid in overtime? How many employees are in each department/subdepartment, and how many are full-time or part-time? What part of payroll goes to full-time, and what part goes to part-time? Which department/subdepartment pays out the most overtime? The point of a database is to have a pile of data, out of which you can mine a lot of information. Once you have accumulated enough data, then you can get some very valuable information from it. For instance, if you look at where most of your overtime is being paid through a year, then you can discuss whether some new employees need to be hired. |
#19
|
|||
|
|||
I think I'm almost there...except for...
"Jamie Collins" wrote:
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'... Jamie. OK, point taken. I'll quote from my big fat Access 2003 Inside Out book: The Four Rules of Good Table Design Rule 1: Each field in a table should represent a unique type of information. Rule 2: Each table must have a unique identifier, or primary key, that is made up of one or more fields in the table. Rule 3: For each unique primary key value, the values in the data columns must be relevant to, and must completely describe, the subject of the table. Rule 4: You must be able to make a change to the data in any field (other than to a field in the primary key) without affecting the data in any other field. Even though this doesn't describe normalization per se, they are good rules for helping you to make normalized tables. There are several reasons why people keep harping on normalization, but without giving you a concrete, this-is-what-it-looks-like answer. Normalization is a little like learning to do sums. If I ask you what 2 + 2 is, you would probably answer 4 without having to think about it. If asked for definite proof of why 2 + 2 = 4, you would have to think a few moments, and then you would probably hold up two fingers on both hands, and push them together. Once you understand normalization, and can use it easily, it becomes so natural that it is difficult to verbalize how you are doing it. This is why entire books are written on the subject, because it takes a lot of verbalizing to cover the subject. Another reason that people can't give you a definite answer, is because we don't know all of the variables or problems that you are facing. We have not spent three months interviewing people and figuring out all of the little details that need to be part of this database. Sometimes those little details are precisely what is needed to have not just a normalized table structure, but one which is truly useful for what you are trying to do. You may want to ask yourself why you are doing this as a database, and not just using an Excel worksheet. Usually people need to go to a database design because they require the extra flexibility. But there is a price for that flexibility, and that is that you have to learn how to put a database together. I can understand your frustration, and it must seem like we are all very stubborn and just not listening to you. You must have noticed that we are getting rather bored and frustrated with all of this, also. You do not seem to listen to what advice we give, you reject the ready-made templates that are available, and you keep reposting virtually the same table structures that you started with. None of this is helping you get a database written. |
#20
|
|||
|
|||
I think I'm almost there...except for...
(A) I would rather use PayrollID as employee ID since that is unique
I'm not sure what you mean by this. EmployeeID is intended to uniquely identify a single employee. PayrollID is intended to uniquely identify a payroll entry. Each PayrollID is linked to an EmployeeID to identify which employee is getting paid. (B) I would like to distinguish between contracted hours and overtime. Can you describe the difference between contracted hour and overtime hours? If you can come up with a specific definition of the two, then it can be programmed into the database. I was thinking that each payroll entry could be designated by a PayrollType, which would have entries of either "contracted" or "overtime." But each entry into your Payroll table would have to be designated as one or the other. You could have the default entry be whichever one occurs most frequently, and just change it for those times that it should be the other one. (C) I need to be sure that the hours entered relate to the correct department. In your Payroll table, you have a foreign key of Subdepartment. Each payroll entry should include a reference to the appropriate subdepartment, which then defines the department. This means, though, that if a person works for 20 hours in the business department, 10 hours in shipping, and then 10 hours in advertising, that you will have 3 entries in the payroll table for that person for that week (week 1). So, if the tables look like this: tbl_Employee EmployeeID (PK) Employee Status Rate tbl_Payroll Payroll (PK) EmployeeID (FK) [this needs to match the corresponding ID in your Employee table] SubdepartmentID (FK) [this needs to match the corresponding ID in your subdepartment table] WeekID (this is probably a number to designate which week of the year) Amount (I suppose this is the amount of time worked, so that you can multiply this by the rate) tbl_Subdepartment DepartmentName (there will be duplicates here, but probably not very many) SubdepartmentID (PK) Your tables could have these entries: Employee table EmployeeID, Employee, Status, Rate 1, John Doe, Full-time, 7.50 Subdepartment table DepartmentName, SubdepartmentID Business, 1 Shipping, 2 Advertising, 3 Payroll table Payroll, EmployeeID, SubdepartmentID, WeekID, Amount 1, 1, 1, 1, 20 2, 1, 2, 1, 10 3, 1, 3, 1, 10 You would then set up a query that would include the appropriate fields, making a field to be passed on to the form to calculate the total pay, based on rate times amount. |
Thread Tools | |
Display Modes | |
|
|