Welcome to SPN

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

Sign Up Now!

subtable relationships

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

  1. Mark Cline

    Mark Cline
    Expand Collapse
    Guest

    I think I have a situation where a series of subtables 4 deep is necessary.
    I am having trouble relating each of the tables so that data contained in a
    subtable does not repeat itself when a particular entry in the table above it
    repeats itself. I will describe what I want and any recommendations on
    better options or a solution are welcome.

    The database is going to be used to keep track of quality control audits.
    Different QA techs input the audits they complete, the location they complete
    them in, and what failures they noticed. The innermost table has product
    information, failure code, and quantity failed. The next table up has audit
    type, # inspected, and # passed. The next table up has line and shift. The
    top table identifies the date and the QA tech. I would prefer to not create
    a new user input column on any table because it would be a meaningless
    quantity.

    How can I fix the situation? 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. Sprinks

    Sprinks
    Expand Collapse
    Guest

    Mark,

    If a given audit pertains to a specific line & shift, I think you can
    simplify your structure. I see 2 one-to-many relationships--between Audits
    and AuditProducts, and AuditProducts & Failures.

    Audits
    -----------------
    AuditID AutoNumber (PK)
    AuditTypeID Integer (FK to AuditTypes)
    AuditDate Date/Time
    QATechID Integer (FK to QATechs)
    LineID Integer (FK to Lines)
    Shift Integer (1,2,3 or FK to Shifts)

    AuditProducts (many side with Audits)
    ------------------
    AuditProductID AutoNumber (PK)
    AuditID Integer (FK to Audits)
    ProductID Integer (FK to Products)
    NumberInspected Integer

    Failures (or AuditProductFailures) -- Many side of AuditProducts relationship
    -----------------------
    FailureID AutoNumber (PK)
    AuditProductID Integer (FK to AuditProducts)
    FailureType Integer (FK to FailureTypes)
    QtyFailed Integer
    Note Text or Memo

    Qty passed can be calculated from the number inspected less the total of all
    that failed.

    This structure is similar to the Customers->Orders->OrderDetails
    relationships modeled in the sample Northwind DB (Help, Sample Databases).
    To model it on a form, you need a subform within a subform placed on a main
    form based on Audits.

    Hope that helps.
    Sprinks



    Does an audit consist of one or many different product id's? In other
    words, is there a one-to-many relationship between Audits and Products?

    "Mark Cline" wrote:

    > I think I have a situation where a series of subtables 4 deep is necessary.
    > I am having trouble relating each of the tables so that data contained in a
    > subtable does not repeat itself when a particular entry in the table above it
    > repeats itself. I will describe what I want and any recommendations on
    > better options or a solution are welcome.
    >
    > The database is going to be used to keep track of quality control audits.
    > Different QA techs input the audits they complete, the location they complete
    > them in, and what failures they noticed. The innermost table has product
    > information, failure code, and quantity failed. The next table up has audit
    > type, # inspected, and # passed. The next table up has line and shift. The
    > top table identifies the date and the QA tech. I would prefer to not create
    > a new user input column on any table because it would be a meaningless
    > quantity.
    >
    > How can I fix the situation? Thanks in advance...
     
  4. Mark Cline

    Mark Cline
    Expand Collapse
    Guest

    Thank you very much, this is exactly what I needed to fix my problem.

    "Sprinks" wrote:

    > Mark,
    >
    > If a given audit pertains to a specific line & shift, I think you can
    > simplify your structure. I see 2 one-to-many relationships--between Audits
    > and AuditProducts, and AuditProducts & Failures.
    >
    > Audits
    > -----------------
    > AuditID AutoNumber (PK)
    > AuditTypeID Integer (FK to AuditTypes)
    > AuditDate Date/Time
    > QATechID Integer (FK to QATechs)
    > LineID Integer (FK to Lines)
    > Shift Integer (1,2,3 or FK to Shifts)
    >
    > AuditProducts (many side with Audits)
    > ------------------
    > AuditProductID AutoNumber (PK)
    > AuditID Integer (FK to Audits)
    > ProductID Integer (FK to Products)
    > NumberInspected Integer
    >
    > Failures (or AuditProductFailures) -- Many side of AuditProducts relationship
    > -----------------------
    > FailureID AutoNumber (PK)
    > AuditProductID Integer (FK to AuditProducts)
    > FailureType Integer (FK to FailureTypes)
    > QtyFailed Integer
    > Note Text or Memo
    >
    > Qty passed can be calculated from the number inspected less the total of all
    > that failed.
    >
    > This structure is similar to the Customers->Orders->OrderDetails
    > relationships modeled in the sample Northwind DB (Help, Sample Databases).
    > To model it on a form, you need a subform within a subform placed on a main
    > form based on Audits.
    >
    > Hope that helps.
    > Sprinks
    >
    >
    >
    > Does an audit consist of one or many different product id's? In other
    > words, is there a one-to-many relationship between Audits and Products?
    >
    > "Mark Cline" wrote:
    >
    > > I think I have a situation where a series of subtables 4 deep is necessary.
    > > I am having trouble relating each of the tables so that data contained in a
    > > subtable does not repeat itself when a particular entry in the table above it
    > > repeats itself. I will describe what I want and any recommendations on
    > > better options or a solution are welcome.
    > >
    > > The database is going to be used to keep track of quality control audits.
    > > Different QA techs input the audits they complete, the location they complete
    > > them in, and what failures they noticed. The innermost table has product
    > > information, failure code, and quantity failed. The next table up has audit
    > > type, # inspected, and # passed. The next table up has line and shift. The
    > > top table identifies the date and the QA tech. I would prefer to not create
    > > a new user input column on any table because it would be a meaningless
    > > quantity.
    > >
    > > How can I fix the situation? Thanks in advance...
     
  5. Sprinks

    Sprinks
    Expand Collapse
    Guest

    My pleasure. One additional thought--if the audit refers to only a single
    product, you could move the ProductID and NumberInspected into the Audits
    table, with a single one-to-many relationship remaining between Audits and
    Failures.

    Sprinks

    "Mark Cline" wrote:

    > Thank you very much, this is exactly what I needed to fix my problem.
    >
    > "Sprinks" wrote:
    >
    > > Mark,
    > >
    > > If a given audit pertains to a specific line & shift, I think you can
    > > simplify your structure. I see 2 one-to-many relationships--between Audits
    > > and AuditProducts, and AuditProducts & Failures.
    > >
    > > Audits
    > > -----------------
    > > AuditID AutoNumber (PK)
    > > AuditTypeID Integer (FK to AuditTypes)
    > > AuditDate Date/Time
    > > QATechID Integer (FK to QATechs)
    > > LineID Integer (FK to Lines)
    > > Shift Integer (1,2,3 or FK to Shifts)
    > >
    > > AuditProducts (many side with Audits)
    > > ------------------
    > > AuditProductID AutoNumber (PK)
    > > AuditID Integer (FK to Audits)
    > > ProductID Integer (FK to Products)
    > > NumberInspected Integer
    > >
    > > Failures (or AuditProductFailures) -- Many side of AuditProducts relationship
    > > -----------------------
    > > FailureID AutoNumber (PK)
    > > AuditProductID Integer (FK to AuditProducts)
    > > FailureType Integer (FK to FailureTypes)
    > > QtyFailed Integer
    > > Note Text or Memo
    > >
    > > Qty passed can be calculated from the number inspected less the total of all
    > > that failed.
    > >
    > > This structure is similar to the Customers->Orders->OrderDetails
    > > relationships modeled in the sample Northwind DB (Help, Sample Databases).
    > > To model it on a form, you need a subform within a subform placed on a main
    > > form based on Audits.
    > >
    > > Hope that helps.
    > > Sprinks
    > >
    > >
    > >
    > > Does an audit consist of one or many different product id's? In other
    > > words, is there a one-to-many relationship between Audits and Products?
    > >
    > > "Mark Cline" wrote:
    > >
    > > > I think I have a situation where a series of subtables 4 deep is necessary.
    > > > I am having trouble relating each of the tables so that data contained in a
    > > > subtable does not repeat itself when a particular entry in the table above it
    > > > repeats itself. I will describe what I want and any recommendations on
    > > > better options or a solution are welcome.
    > > >
    > > > The database is going to be used to keep track of quality control audits.
    > > > Different QA techs input the audits they complete, the location they complete
    > > > them in, and what failures they noticed. The innermost table has product
    > > > information, failure code, and quantity failed. The next table up has audit
    > > > type, # inspected, and # passed. The next table up has line and shift. The
    > > > top table identifies the date and the QA tech. I would prefer to not create
    > > > a new user input column on any table because it would be a meaningless
    > > > quantity.
    > > >
    > > > How can I fix the situation? Thanks in advance...
     

Share This Page