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  

I think I'm almost there...except for...



 
 
Thread Tools Display Modes
  #11  
Old May 23rd, 2006, 03:31 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 03:33 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 03:35 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 03:42 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 03:50 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 04:23 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 04:25 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 04:29 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 04:29 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 05:01 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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

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 12:09 AM.


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