Forum Discussion
Indexing and Duplicates
I found a couple of hours finally to take a closer look. I am probably not completely right about some things because I'm not familiar enough with the work involved. That said, this diagram
represents my best understanding.
I know that you indicated the Auditor and Auditee are both related to the Audit itself. However, in the tables you had in the previous screenshot, it appeared to be the case that each subclause can be handled by a different auditor. That's how I implemented it here. If the Auditor is, indeed, assigned to an audit and must individually complete each of the subclauses personally, you can move the AuditorID up into TBLAudit.
It also looked to me to be the case that the same Evidence is applied to each subclause, regardless of the audit. If that's not the case, i.e. if each audit can attach different evidence to subclauses for different auditees, that would have to be moved down in the Audit_AuditSubClause table.
I do appreciate your guidance on this George and apologies for monopolising your time. I'll spend more time learning more about normalising as when I think I've got it, you (quite rightly) point out that haven't quite got it.
This is an ISO9001 audit whose flow is like this:
Each audit consists of 7 main clauses. These will be the same for every audit.
Each main clause requires and auditor, auditee, date and comments fields. The data for these fields will be different for each audit.
Each main clause has between 3 and 5 subclauses. Again, these will be the same for every audit.
The subclauses do not require auditor, auditee, date and comments fields as these are captured by the appropriate main clause.
Each subclause requires proof or lack of evidence as a long text field.
Each subclause also requires a fixed status as described earlier - Yes: Meets the standard, No: Minor non-conformance, etc.
Hope that helps.
I'm sure that someone will be looking in again soon. I will try to do so later or tomorrow. It's a bit of a frantic day for me, unfortunately.
- I understand that it's a big transition to a relational database application. It can take time to consolidate the concepts.
With regard to data entry forms. Each form or subform should be bound to one table, or to a query using one table. There are performance reasons for that as well as logical reasons. The main audit form, for example, is bound to the audit table or to a query based on that audit table.
A clauses subform is bound to the clauses table, or to a query based on that clauses table only.
And so on.
You'll see joins in reporting situations where you need to bring together elements of different components for display only. - Thanks very much George. I've been working with that and now just need to figure out how to bring it all together into one Main Audit form with subform for the subclauses. I've tried a few things but keep getting that dreaded "ambiguous outer joins" error message. I know it's just me needing to understand how to bring them together so I'll keep working on that. Your time has been invaluable George and I thank you.
I found a couple of hours finally to take a closer look. I am probably not completely right about some things because I'm not familiar enough with the work involved. That said, this diagram
represents my best understanding.
I know that you indicated the Auditor and Auditee are both related to the Audit itself. However, in the tables you had in the previous screenshot, it appeared to be the case that each subclause can be handled by a different auditor. That's how I implemented it here. If the Auditor is, indeed, assigned to an audit and must individually complete each of the subclauses personally, you can move the AuditorID up into TBLAudit.
It also looked to me to be the case that the same Evidence is applied to each subclause, regardless of the audit. If that's not the case, i.e. if each audit can attach different evidence to subclauses for different auditees, that would have to be moved down in the Audit_AuditSubClause table.