subtable relationships

#1 (**permalink**) 28-Jul-2006, 08:27 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12244-subtable-relationships.html
better options or a solution are welcome.

The database is going to be used to keep track of quality control audits.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12244
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 (**permalink**) 28-Jul-2006, 08:27 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12244
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)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12244
------------------
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...

#3 (**permalink**) 28-Jul-2006, 08:27 AM

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

"Sprinks" wrote:

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12244
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12244
>
> 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 (**permalink**) 28-Jul-2006, 08:27 AM

My pleasure. One additional thought--if the audit refers to only a single
product, you could move the ProductID and NumberInspected into the Audits
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12244
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12244
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...