Forum Discussion
Internal Audit Database
More than two tables are required to model an audit properly in a relational database application.
You need, at a minimum:
InternalAudit -- a table listing each audit to be conducted, with fields for a description, StartDate and EndDate of the audit, auditor conducting the audit (unless there are two or more auditors, which would call for an AuditorInternalAudit table).
Auditor -- a table listing available auditors, with fields for FirstName, LastName, ActiveDate, at least.
AuditClause -- a table for the 7 currently active main audit clauses.
AuditSubClause -- a table of all of the subclauses, with a foreign Key field to relate the AuditClause to one or more AuditSubClauses.
AuditResult -- a table in which each AuditSubClause is recorded, along with the answer supplied for it, the date and time on which it was completed, and any other relevant details.
There may be other variations of these tables. I'm basing this description on the assumption that one auditor completes all of the subclauses for an audit. If that's not the case, changes are required.
In general, though, data entry in related tables works most smoothly in a main form/sub form design. The table on the one-side of a relationship is bound to the main table. The table on the many-side is bound to the subform.
More than two tables are required to model an audit properly in a relational database application.
You need, at a minimum:
InternalAudit -- a table listing each audit to be conducted, with fields for a description, StartDate and EndDate of the audit, auditor conducting the audit (unless there are two or more auditors, which would call for an AuditorInternalAudit table).
Auditor -- a table listing available auditors, with fields for FirstName, LastName, ActiveDate, at least.
AuditClause -- a table for the 7 currently active main audit clauses.
AuditSubClause -- a table of all of the subclauses, with a foreign Key field to relate the AuditClause to one or more AuditSubClauses.
AuditResult -- a table in which each AuditSubClause is recorded, along with the answer supplied for it, the date and time on which it was completed, and any other relevant details.
There may be other variations of these tables. I'm basing this description on the assumption that one auditor completes all of the subclauses for an audit. If that's not the case, changes are required.
In general, though, data entry in related tables works most smoothly in a main form/sub form design. The table on the one-side of a relationship is bound to the main table. The table on the many-side is bound to the subform.