Audit Database- Not Sure Where to Start (1 Viewer)

moneal75 · Aug 3, 2012

Hi All,

I'm brand new to Access and am hoping that someone can guide me to where to start. I conduct audits and record findings on forms. The audits are independent of one another and I have 3 teammates that I would want be able to utilze a database to record findings. We all have the same questions but the teammates would be specific independent users.

Can someone please give me advice of where I should start? I'm listing some examples of the questions from my 2 forms below.

Thank you in advance for humoring my newbie basic questions, but this project is overwhelming and I don't know where to start.

The secondary purpose of this database is to track my metrics based on these questions.

The first form is where we record findings:

Free Text Field Requirements examples:
Auditor Name
Project #
Department
Date Range Audited

OR
Information request fields:
Date:
Name of Person that Information was requested from:
Type of Information Requested

Some Questions on the form have a drop down menu:

Audit Type:
Risk Based
For Cause

or

Project Status:
Open
Closed
On Hold

There is also the field work section that has
QuestionXXXX
Check box of Yes, No, NA
Free text for findings (this free text box is on each question)

The second form is based on employee interviews:

Some of the questions I can do via a drop down menu.

Example:
What is your role on this project?

Choices are:
Manager
Finance
Data

Others require free text responses.

Examples of Free Text:
Audit #
Department
Interviewee Name
Interviewee Title

Then there are some that we have as a drop down menu as first choice then free text :

Example
Does your department keep copies of XXXX?

Choices
Yes
No
Unable to Answer Question

Sub Questions
Free text required
If yes, where are they located?
If Unable to Answer Question, Explain

MarkK · Aug 3, 2012

What I recommend first of all is read up on the subject of 'database normalization.' Use those as search terms in Google or use the search function on here.

A database keeps track of the dimensions of objects and how they are related to each other. So tables you have will likely include Audit or Project. One audit object is a single record in the Audit table, and the fields in that table will be the dimensions of that object, so an Audit with have Type, Opened, Closed, Status, and perhaps IsForCause dimensions, or fields.

You will need a table for QuestionXXXXs, since these exist as discrete objects independent of any Audit. Each QuestionXXXX is a unique record in that table, and it too will have it's own dimensions, including question text.

You will need a third table that links questions to audits, since the questions are discrete, and the audits are discrete, the answer to a question needs to be related to an audit on one side, and the question on the other. In this respect an Audit and a Question have a Many-To-Many relationship with each other. One Audit might contain many questions. One question might appear in many audits. This is a three table solution.

I'm not trying to scare you, but if you get this kind of stuff wrong starting out writing a database, it will never really become the useful tool you were hoping for when you started the job. If you get the table structure right, on the other hand, this database can scale up and continue to grow with your expanding business or organization or whatever.

Hope that helps,
Mark

spikepl · Aug 3, 2012

The structure of information in your audit seems pretty similar to a survey. Google

survey site:www.access-programmers.co.uk

some members have uploades sample db's in some threads.

Also, for data structure, check out http://www.databaseanswers.org/data_models/ where there are data models for surveys

moneal75 · Aug 6, 2012

Yes, this helps a lot. I was getting very overwhelmed with even the terminology, so giving me a starting point helps. Thanks so much for taking the time to respond!

lagbolt said:
What I recommend first of all is read up on the subject of 'database normalization.' Use those as search terms in Google or use the search function on here.

A database keeps track of the dimensions of objects and how they are related to each other. So tables you have will likely include Audit or Project. One audit object is a single record in the Audit table, and the fields in that table will be the dimensions of that object, so an Audit with have Type, Opened, Closed, Status, and perhaps IsForCause dimensions, or fields.

You will need a table for QuestionXXXXs, since these exist as discrete objects independent of any Audit. Each QuestionXXXX is a unique record in that table, and it too will have it's own dimensions, including question text.

You will need a third table that links questions to audits, since the questions are discrete, and the audits are discrete, the answer to a question needs to be related to an audit on one side, and the question on the other. In this respect an Audit and a Question have a Many-To-Many relationship with each other. One Audit might contain many questions. One question might appear in many audits. This is a three table solution.

I'm not trying to scare you, but if you get this kind of stuff wrong starting out writing a database, it will never really become the useful tool you were hoping for when you started the job. If you get the table structure right, on the other hand, this database can scale up and continue to grow with your expanding business or organization or whatever.

Hope that helps,
Mark

moneal75 · Aug 6, 2012

hmmm I replied but it's not showing up. Thanks so much for the links. That second one led me to some tutorials that he puts on. I plan on checking those out. Thanks so much~!

Audit Database- Not Sure Where to Start (1 Viewer)

moneal75

New member

MarkK

bit cruncher

spikepl

Eledittingent Beliped

moneal75

New member

moneal75

New member

Similar threads

Users who are viewing this thread