Internal Audit Database

Copper Contributor

Hi

 

I'm starting to build a database for recording ISO 9001 internal audits.  It is clause based where there are 7 main clauses, each of which has several sub clauses with an audit question against each one.  I have created two tables relating the sub-clause table to the clause table.  I'm now struggling to understand how to create a form which shows all of the questions and records each full audit.  I have a little knowledge of Access, but not enough to get me over this hurdle.

 

Hope you can help me understand how to do it.

 

Thanks.

2 Replies

@ChrisAKFS 

 

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.