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