Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

one-to-many relationships

Discussion in 'Information Technology' started by scubadiver, Jul 28, 2006.

  1. scubadiver

    scubadiver
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    I'm open-minded about romantic relationships... how do I explain to family? Love & Marriage Aug 22, 2015
    Need advice...Sikhi in relationships Love & Marriage Nov 17, 2011
    Peer Pressure, Western Traits and Relationships Announcements Aug 23, 2010
    Peer pressure, western traits and relationships Sikh Youth Aug 23, 2010
    What is the Commitment in Relationships? Relationships Jan 24, 2010

  3. pvdg42

    pvdg42
    Expand Collapse
    Guest

    "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    news:B5928FB3-B372-44FB-8672-37898436B20D@microsoft.com...
    >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
     
  4. scubadiver

    scubadiver
    Expand Collapse
    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!

    "pvdg42" wrote:

    >
    > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > news:B5928FB3-B372-44FB-8672-37898436B20D@microsoft.com...
    > >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
    >
    >
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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.


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    news:0F1775D5-8538-4D78-84C3-1D1B1CD43B66@microsoft.com...
    > 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!
    >
    > "pvdg42" wrote:
    >
    > >
    > > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > > news:B5928FB3-B372-44FB-8672-37898436B20D@microsoft.com...
    > > >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
    > >
    > >
    > >
     
  6. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    scubadiver wrote:
    > 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.

    --
    Joseph Meehan

    Dia duit
     
  7. scubadiver

    scubadiver
    Expand Collapse
    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?




    "Douglas J Steele" wrote:

    > 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.
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > news:0F1775D5-8538-4D78-84C3-1D1B1CD43B66@microsoft.com...
    > > 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!
    > >
    > > "pvdg42" wrote:
    > >
    > > >
    > > > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > > > news:B5928FB3-B372-44FB-8672-37898436B20D@microsoft.com...
    > > > >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
    > > >
    > > >
    > > >

    >
    >
    >
     
  8. scubadiver

    scubadiver
    Expand Collapse
    Guest

    See my reply to Douglas

    "Joseph Meehan" wrote:

    > scubadiver wrote:
    > > 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.
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     
  9. scubadiver

    scubadiver
    Expand Collapse
    Guest

    Moral of the lesson: start with pencil and paper!

    "Joseph Meehan" wrote:

    > scubadiver wrote:
    > > 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.
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     
  10. scubadiver

    scubadiver
    Expand Collapse
    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" wrote:

    > 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.
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > news:0F1775D5-8538-4D78-84C3-1D1B1CD43B66@microsoft.com...
    > > 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!
    > >
    > > "pvdg42" wrote:
    > >
    > > >
    > > > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > > > news:B5928FB3-B372-44FB-8672-37898436B20D@microsoft.com...
    > > > >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
    > > >
    > > >
    > > >

    >
    >
    >
     
  11. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    news:5A9C570E-493D-44D9-A392-48F1EB8D5AED@microsoft.com...
    > 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?
    >
    >
    >
    >
    > "Douglas J Steele" wrote:
    >
    > > 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.
    > >
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > > news:0F1775D5-8538-4D78-84C3-1D1B1CD43B66@microsoft.com...
    > > > 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!
    > > >
    > > > "pvdg42" wrote:
    > > >
    > > > >
    > > > > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > > > > news:B5928FB3-B372-44FB-8672-37898436B20D@microsoft.com...
    > > > > >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
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     
  12. scubadiver

    scubadiver
    Expand Collapse
    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.

    "Douglas J Steele" wrote:

    > 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.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > news:5A9C570E-493D-44D9-A392-48F1EB8D5AED@microsoft.com...
    > > 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?
    > >
    > >
    > >
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > 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.
    > > >
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > > > news:0F1775D5-8538-4D78-84C3-1D1B1CD43B66@microsoft.com...
    > > > > 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!
    > > > >
    > > > > "pvdg42" wrote:
    > > > >
    > > > > >
    > > > > > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > > > > > news:B5928FB3-B372-44FB-8672-37898436B20D@microsoft.com...
    > > > > > >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
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >
     
  13. scubadiver

    scubadiver
    Expand Collapse
    Guest

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

    "scubadiver" wrote:

    > 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.
    >
    > "Douglas J Steele" wrote:
    >
    > > 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.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > > news:5A9C570E-493D-44D9-A392-48F1EB8D5AED@microsoft.com...
    > > > 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?
    > > >
    > > >
    > > >
    > > >
    > > > "Douglas J Steele" wrote:
    > > >
    > > > > 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.
    > > > >
    > > > >
    > > > > --
    > > > > Doug Steele, Microsoft Access MVP
    > > > > http://I.Am/DougSteele
    > > > > (no e-mails, please!)
    > > > >
    > > > >
    > > > > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > > > > news:0F1775D5-8538-4D78-84C3-1D1B1CD43B66@microsoft.com...
    > > > > > 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!
    > > > > >
    > > > > > "pvdg42" wrote:
    > > > > >
    > > > > > >
    > > > > > > "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message
    > > > > > > news:B5928FB3-B372-44FB-8672-37898436B20D@microsoft.com...
    > > > > > > >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
    > > > > > >
    > > > > > >
    > > > > > >
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     
  14. norwedsh@gmail.com

    norwedsh@gmail.com
    Expand Collapse
    Guest

    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
     

Share This Page