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
  #1  
Old May 23rd, 2006, 12:35 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 12:48 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 02:00 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 02:47 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 02:48 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 03:05 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 03:09 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 03:09 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old May 23rd, 2006, 03:13 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.


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  
Old May 23rd, 2006, 03:28 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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

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:56 PM.


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