subtable relationships

[Mark Cline]

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...

 

[Sprinks]

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]

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

 

[Sprinks]

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