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?

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?

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?

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?

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.

--

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.

--

Scubadiver,

I've posted comments, and I've posted questions critiquing your design in
the hopes of maybe helping you. But you never responded. Other people have
offered viable solutions but you had been quick to reject them in favor of
your own design. So, I decided to critique your design because .. well.. I
figured if you could not see the merit of a good solution, maybe you could
see the lack of merit of your design.

As long as we're all being sincere.....

I'm one of the first ones to suggest to you that maybe you need more *dose*
on the theory side. Honestly, the reason I came to this conclusion is your
quick discarding of and rejecting viable solutions that had been offered
(i.e. by Tim Ferguson, mnature). From the progression of your threads, I
think you know enough about normalization to recognize that you have a
problem, but you do not know enough about it to recognize a viable solution
if you see one (i.e. solutions offered by other poster).

How about starting over. Forget your design just for a while and give this a
try. If this fails, you can always go back to your design.
Developing a database app is really just modelling a real life process(es)
into a database software product. Relational databases in particular make
use of concepts of entities and relationships.

Entities in your case are Employee, Department, Subdepartment, HoursWorked.
Relationships describe the, well.... relationships between those entities.
For example, if I'm an employee,... what is a department? Well.. I probably
work for one. Or it is possible that noone really works for a Department,
everyone works for a Subdepartment. So each business has its own rules.

As you would expect, there is also a relationship between Department and
Subdepartment. A department may have four Subdepts i.e. Operations-East,
Operations-West, Operations-North, etc.

The point is all the above are necessary, the entities and the relationships
between the entities have to be understood and defined. This is the only way
to successfully model the real world into your a relational database.

You made a comment that you didnt understand why Subdepartment needs to be
separate from Department. Well.. because they are two different entities,
and relational database design would implement them in two tables. I hope
this example can help you see that maybe a little more reading on relational
database design (i.e. of which normalization is a part) would be helpful to
you.



HTH,
Immanuel Sibero






"scubadiver" wrote in message
...
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.

--

"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.

DK/NF is not always achievable, so I would not worry about it too much.

And as Multivalued and Join Dependencies themselves are very rare the
question goes back to Functional Dependencies resolved by normalisation to
BCNF, the requirement for over 95% of databases in the world.

Reaching BCNF for the beginner is merely the application of a set of fairly
simple rules and tests and a good helping of common sense, together with a
clear understanding of the problem domain the absence of which makes
"Database Design by email so very dangerous".

The questioner (not necessarily referring to the current OP) is forever
pulling rabbits out of the hat when one possible solution is proposed
without regard to the new rabbit (as yet undisclosed to the viewing public)
making the answer wrong in the light of the new information.

The best and most practical advice is learn how to normalise your data and
in this case it is clear there are unresolved many to many relationships
here; how many depends upon the problem domain which is not being adequately
explained as evidenced by over 30 emails in the last 8 days.

The OP has posted more than that on other newsgroups prior to reviewing the
database design which many have advised the OP to do.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"Jamie Collins" wrote in message
oups.com...

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.

--

Craig Alexander Morrison wrote:
The best and most practical advice is learn how to normalise your data

I am of the opinion that 'Learn how to normalise your data' falls some
way short of the most practical advice. Actually, I consider it a
non-answer, right up there with 'Learn how to do DBMS design' and
'Learn how to program'.

Here are some handy phrases to make you advice more practical:

"By doing x you have violated 1NF."

"You could y and your design will be in 3NF."

etc.

Jamie.

--