one-to-many relationships

[Guest]

I am a newbie and need to know how this works in basic language I can
understand. I have an employee information table and three other tables that
need to be linked.

I have been trying to design the database so that employee information acts
as a look up table for a cascading combo box in a main form but I am not sure
whether that is such a good idea because I am finding it difficult to record
the information in a table.

I will not provide any more information unless pressed because I am confused
enough as it is.

I need an answer to two questions:

1) How do I establish a one-to-many relationship linking these four tables.
I haven't yet established any primary keys

and

2) How do I establish ONE form where I can enter data into these four table.

Thanks in advance

 

[pvdg42]

I am a newbie and need to know how this works in basic language I can
understand. I have an employee information table and three other tables
that
need to be linked.

I have been trying to design the database so that employee information
acts
as a look up table for a cascading combo box in a main form but I am not
sure
whether that is such a good idea because I am finding it difficult to
record
the information in a table.

I will not provide any more information unless pressed because I am
confused
enough as it is.

I need an answer to two questions:

1) How do I establish a one-to-many relationship linking these four
tables.
I haven't yet established any primary keys

and

2) How do I establish ONE form where I can enter data into these four
table.

Thanks in advance

You really need to learn about relational database design. I say that not to
belittle you, but in response to your own words:
<quote>
I will not provide any more information unless pressed because I am confused
enough as it is.
</quote>

Here are links to free tutorials that should be helpful to you:

http://www.geekgirls.com/databasics_01.htm

http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials.html

http://www.bcschools.net/staff/AccessHelp.htm#Top

http://www.fgcu.edu/support/office2000/access/

http://www.oit.duke.edu/ats/training/docs/access1/

http://cisnet.baruch.cuny.edu/holowczak/classes/2200/access/accessall.html#sec_intro

http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

 

[Guest]

Hi,

I know all about tables, forms, queries and reports. This is my first
attempt at creating a reasonably complex database but I am determined to do
it!!

To clarify, the employee information table is on the "one" side and the
three other tables are on the "many" side.

I want to use "Employee Name" as the primary key because I don't want to get
involved with automated numbering. I have got the idea that "Employee name"
needs to be present in all the tables for them to be linked. Is that correct?

Is it better to have the relationships set up serially like so:

Employee -> table1 -> table2 -> table3

Or this way:

table1
|
Table2 <- Employee -> table3

So then am I correct in saying that the fields for all the tables is then
put into a query which can then be used to create a form?

cheers!

 

[Douglas J Steele]

It would help if you provided some details about the tables, rather than
strictly speaking generically. It's not a case of "which is better".

Employee -> table1 -> table2 -> table3

only makes sense if table3 is related to table2 and table2 is related to
table1.

table1
|
Table2 <- Employee -> table3

only makes sense if the 3 tables are only related to Employee and not to
each other.

Normally, yes, one approach would be to create a query that joins your
tables and base the form on that query. Unfortunately, you're seldom able to
create an updatable query that joins 4 tables, which means that you'll
probably have to consider using subforms.

 

[Joseph Meehan]

Hi,

I know all about tables, forms, queries and reports. This is my first
attempt at creating a reasonably complex database but I am determined
to do it!!

I think pvdg42 did offer you some very good advice. It would appear
that you need some additional information on relational database theory. He
offered some resources.

Based on the information you presented, no one will be able to provide a
sure fire suggestion. You will need to provide a lot more information about
your specific application needs or you will need to better understand
relational database theory.

I suggest you will be better off in the long run by improving your
understanding of the theory. If you understand the theory then you are
likely to be able to better produce the best solution than we can.

 

[Guest]

remember I haven't established any primary keys or relations. Also remember
that these are all fields that I need.

Okay, deep breath now....

"Table1" fields:

Employee (Employee name)
Current (are they currently working for the company; there is a high temp
staff turnover)
Status (are they permanent or temporary)
Rate (hourly rate earned by employee)
Total (=rate*1.26 for temp staff paid to the agency)
Costcentre (each department has their own)

"Table2" fields:

Department
Subdepartment (I have done this using a cascading combo and it works fine)
permhrs (contracted hours (defaulted to 36hrs))
permTH (overtime: time and a half)
permDB (overtime: double time)
paidhol (paid holiday)
sickness
Training
medical
Special
complve

For the temp staff there are 25 departments, each with standard hours, time
and a half and double time (temp staff don't get paid holiday so this has
been excluded). Since this gives a total of 75 fields I have divided these up
into "table3" and "table4".

The extra catch is that the total hours will be recorded weekly but it would
be nice to have a summary at the end of the month.

Ideas?

 

[Guest]

See my reply to Douglas

I think pvdg42 did offer you some very good advice. It would appear
that you need some additional information on relational database theory. He
offered some resources.

Based on the information you presented, no one will be able to provide a
sure fire suggestion. You will need to provide a lot more information about
your specific application needs or you will need to better understand
relational database theory.

I suggest you will be better off in the long run by improving your
understanding of the theory. If you understand the theory then you are
likely to be able to better produce the best solution than we can.

 

[Guest]

Moral of the lesson: start with pencil and paper!

 

[Guest]

Having thought about it, I could make it

Table2 <- Table1 -> Table3 -> Table4

since permanent staff (table2) are separate to temp staff (Table3 and 4)

 

[Douglas J Steele]

Bells and sirens are going off with your statement of "Since this gives a
total of 75 fields I have divided these up into "table3" and "table4"."
That's NEVER a reason for adding new tables. When we say that it's rare for
a table to require as many as 25 fields, it's not because we like that
number: it's that a properly normalized table seldom requires that many
fields.

The various fields you've listed for table2 look to me as though they should
be rows in another table, not fields in the current table.

I'm afraid I have to add my voice to the others: I think you need to revisit
your table design.

 

[Guest]

slap on the hand!

There is a second catch (apart from recording total hours on a weekly basis):

temporary staff may not necessarily work in the same department all the
time. Due to demands of work, they may be asked to work in other areas and
the Performance manager needs to know the total number of hours each temp
employee worked in each department each week (its all about productivity and
performance).


To summarize:

Each member of staff is

1) either temporary or permanent
2) permanent staff have a "one-to-many" relationship (the "many" being
weekly totals)
3) temp staff have two "one-to-many" relationships

a) the first is weekly totals
b) the second is the number of departments they can work in.

 

[Guest]

I have put this message in "database design" under "fancy a challenge?"

 

[norwedsh]

I created a DB similar to what you displayed, I primary table with
three child tables linked to it. You may or may not know referetial
integrity (RI) controls data redundancy, image being listed twice in
the IRS database. With experimentation if found I could get similar
results using a unique index. I'm not saying this is the way to go,
just giving options. Hopefully not confusing or wrong.

Eric