Welcome to SPN

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

Sign Up Now!

Relationship question

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

  1. Garret

    Garret
    Expand Collapse
    Guest

    Alright here's the deal:

    I have three tables: tblMachines, tblMaintenance, and
    tblMaintenanceInfo

    tblMachines contains three fields, MachineID, MachineDesc, and
    MachineGroup
    tblMaintenance contains MachineID, Month, Department, and Requirements
    tblMaintenanceInfo contains MachineID, MaintenanceNo, CompletedDate,
    and CompletedBy

    I want it to work such that the Machines in both tblMachines and
    tblMaintenance are the same machine item, but there are different
    fields involved. I think is this a One-One relationship? Correct me
    if I'm wrong on anything so far.

    tblMaintenanceInfo is designed in mind to be a subform for
    tblMaintenance so there can be multiple completedDates and
    CompletedBys. One-Many relationship?

    How can I create relationships on all these tables so it works out how
    I want it? So far tblMachines and tblMaintenance seem to be dealing
    with different data.

    Thanks. I can describe further if you have any questions.
     
  2. Loading...

    Similar Threads Forum Date
    Relationship with Creator Blogs Oct 17, 2015
    I'm open-minded about romantic relationships... how do I explain to family? Love & Marriage Aug 22, 2015
    Arts/Society What are your thoughts on the interaction of male / female relationship of marriage? Language, Arts & Culture Oct 15, 2013
    Relationship advice Love & Marriage Sep 30, 2012
    Need advice...Sikhi in relationships Love & Marriage Nov 17, 2011

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 30 Jun 2006 10:38:01 -0700, "Garret" <garretdoe@hotmail.com> wrote:

    >Alright here's the deal:
    >
    >I have three tables: tblMachines, tblMaintenance, and
    >tblMaintenanceInfo
    >
    >tblMachines contains three fields, MachineID, MachineDesc, and
    >MachineGroup
    >tblMaintenance contains MachineID, Month, Department, and Requirements
    >tblMaintenanceInfo contains MachineID, MaintenanceNo, CompletedDate,
    >and CompletedBy
    >
    >I want it to work such that the Machines in both tblMachines and
    >tblMaintenance are the same machine item, but there are different
    >fields involved. I think is this a One-One relationship? Correct me
    >if I'm wrong on anything so far.


    It would only be a one to one relationship if you do maintenance on a
    machine only once, and never again. It's apparently a one (machine) to
    many (monthly?) maintenance episodes relationship, joining on
    MachineID.

    >tblMaintenanceInfo is designed in mind to be a subform for
    >tblMaintenance so there can be multiple completedDates and
    >CompletedBys. One-Many relationship?


    You probably need to use MachineID and Month as the joint, two-field
    Primary Key of tblMaintenance (so you can enter multiple months of
    maintenance for each machine, but not the same machine twice). Change
    the name of the field Month while you're at it, that's a reserved
    word. You would then need to add a matching Month field to
    tblMaintenanceInfo so you can join on both fields.

    >How can I create relationships on all these tables so it works out how
    >I want it? So far tblMachines and tblMaintenance seem to be dealing
    >with different data.


    Well... yes. Different tables, different data. That's normal. But they
    *are* related, right? you're doing maintenance on a particular
    machine?

    >Thanks. I can describe further if you have any questions.


    Post back as needed, we'll try to help!

    John W. Vinson[MVP]
     
  4. Garret

    Garret
    Expand Collapse
    Guest

    Thanks for the reply, John. I'll tell you what more I can and answer
    your questions.
    I want to point out that the fields I listed are such because thats
    what I thought I could use to join them all together. If you have
    other fields I should add/subtract to make them all joined then I'm all
    for it.

    It works like this.
    There are machines. Machines have certain characteristics like
    Description and Group.
    One Form allows the user to Add/Update/Delete machines with just these
    three categories.
    The Machine Maintenance form actually does something with the machines
    (the maintenance of them), and so here is listed the data like
    requirements(memo field), month (month of usual annual maintenance),
    and Department (which section machine belongs to).
    One idea I had originally was just to include this all on one table of
    Machines, but therein lies the problem with the third table. I want to
    have multiple maintenance dates that belong to a machine, but I thought
    this information really shouldn't be listed along with all the machine
    data, just the maintenance area of its data. Maybe it could work this
    way? I don't know.

    Well hope this clears things up. I'm on vacation next week and work
    ends for me in 10 minutes. I'll communicate with you at my house later
    or next Monday.
    Thanks.

    John Vinson wrote:
    > On 30 Jun 2006 10:38:01 -0700, "Garret" <garretdoe@hotmail.com> wrote:
    >
    > >Alright here's the deal:
    > >
    > >I have three tables: tblMachines, tblMaintenance, and
    > >tblMaintenanceInfo
    > >
    > >tblMachines contains three fields, MachineID, MachineDesc, and
    > >MachineGroup
    > >tblMaintenance contains MachineID, Month, Department, and Requirements
    > >tblMaintenanceInfo contains MachineID, MaintenanceNo, CompletedDate,
    > >and CompletedBy
    > >
    > >I want it to work such that the Machines in both tblMachines and
    > >tblMaintenance are the same machine item, but there are different
    > >fields involved. I think is this a One-One relationship? Correct me
    > >if I'm wrong on anything so far.

    >
    > It would only be a one to one relationship if you do maintenance on a
    > machine only once, and never again. It's apparently a one (machine) to
    > many (monthly?) maintenance episodes relationship, joining on
    > MachineID.
    >
    > >tblMaintenanceInfo is designed in mind to be a subform for
    > >tblMaintenance so there can be multiple completedDates and
    > >CompletedBys. One-Many relationship?

    >
    > You probably need to use MachineID and Month as the joint, two-field
    > Primary Key of tblMaintenance (so you can enter multiple months of
    > maintenance for each machine, but not the same machine twice). Change
    > the name of the field Month while you're at it, that's a reserved
    > word. You would then need to add a matching Month field to
    > tblMaintenanceInfo so you can join on both fields.
    >
    > >How can I create relationships on all these tables so it works out how
    > >I want it? So far tblMachines and tblMaintenance seem to be dealing
    > >with different data.

    >
    > Well... yes. Different tables, different data. That's normal. But they
    > *are* related, right? you're doing maintenance on a particular
    > machine?
    >
    > >Thanks. I can describe further if you have any questions.

    >
    > Post back as needed, we'll try to help!
    >
    > John W. Vinson[MVP]
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 30 Jun 2006 12:25:15 -0700, "Garret" <garretdoe@hotmail.com> wrote:

    >Thanks for the reply, John. I'll tell you what more I can and answer
    >your questions.
    >...
    >It works like this.
    >There are machines. Machines have certain characteristics like
    >Description and Group.
    >One Form allows the user to Add/Update/Delete machines with just these
    >three categories.
    >The Machine Maintenance form actually does something with the machines
    >(the maintenance of them), and so here is listed the data like
    >requirements(memo field), month (month of usual annual maintenance),
    >and Department (which section machine belongs to).


    So it sounds like these fields are actually unique attributes of a
    particular machine, if the "Month" means "Machine A312 is maintained
    every year in June"... but that's not clear to me! If that is the
    case, then I'd suggest putting these fields in the Machines table; one
    to one relationships are really rather rare, and this isn't (IMHO) a
    case where such would be required.

    >One idea I had originally was just to include this all on one table of
    >Machines, but therein lies the problem with the third table. I want to
    >have multiple maintenance dates that belong to a machine, but I thought
    >this information really shouldn't be listed along with all the machine
    >data, just the maintenance area of its data. Maybe it could work this
    >way? I don't know.


    That's exactly what one to many relationships are FOR. If you're
    making the common assumption that you must have the machine
    description and the maintenance date in the same table, you *don't* -
    that's what Queries and Subforms are for!

    John W. Vinson[MVP]
     
  6. Garret

    Garret
    Expand Collapse
    Guest

    Hello John, thanks for responding again.

    So what you're saying is that it's probably the best decision to have a
    setup with just two tables: Machines and Maintenance Info. I should
    put all the attributes(Department, Month, etc.) from the Maintenance
    table I have now into the table of Machines instead, to have a larger
    Machines table, and just have the one-many relationship with Machines
    and Maintenance Info. This does seem like the most reasonable way to
    solve this problem.


    John Vinson wrote:
    > On 30 Jun 2006 12:25:15 -0700, "Garret" <garretdoe@hotmail.com> wrote:
    >
    > >Thanks for the reply, John. I'll tell you what more I can and answer
    > >your questions.
    > >...
    > >It works like this.
    > >There are machines. Machines have certain characteristics like
    > >Description and Group.
    > >One Form allows the user to Add/Update/Delete machines with just these
    > >three categories.
    > >The Machine Maintenance form actually does something with the machines
    > >(the maintenance of them), and so here is listed the data like
    > >requirements(memo field), month (month of usual annual maintenance),
    > >and Department (which section machine belongs to).

    >
    > So it sounds like these fields are actually unique attributes of a
    > particular machine, if the "Month" means "Machine A312 is maintained
    > every year in June"... but that's not clear to me! If that is the
    > case, then I'd suggest putting these fields in the Machines table; one
    > to one relationships are really rather rare, and this isn't (IMHO) a
    > case where such would be required.
    >
    > >One idea I had originally was just to include this all on one table of
    > >Machines, but therein lies the problem with the third table. I want to
    > >have multiple maintenance dates that belong to a machine, but I thought
    > >this information really shouldn't be listed along with all the machine
    > >data, just the maintenance area of its data. Maybe it could work this
    > >way? I don't know.

    >
    > That's exactly what one to many relationships are FOR. If you're
    > making the common assumption that you must have the machine
    > description and the maintenance date in the same table, you *don't* -
    > that's what Queries and Subforms are for!
    >
    > John W. Vinson[MVP]
     

Share This Page