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 AuditsReference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12244
and AuditProducts, and AuditProducts & Failures.
AuditID AutoNumber (PK)
AuditTypeID Integer (FK to AuditTypes)
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)
Failures (or AuditProductFailures) -- Many side of AuditProducts relationship
FailureID AutoNumber (PK)
AuditProductID Integer (FK to AuditProducts)
FailureType Integer (FK to FailureTypes)
Note Text or Memo
Qty passed can be calculated from the number inspected less the total of all
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.
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
> How can I fix the situation? Thanks in advance...