Audit database

[Guest]

I have been asked to create an audit database. I have taken an intro to
Access course, and I have done some reading on normalizing the database, but
I am having trouble wrapping my mind around the table set up. The purpose of
the database is to track how a user filled out a worksheet (on a main frame
application). There are about 75 fields that need to be evaluated.
I am having the most trouble with how I am set up the table with the 75
audit items. My mind seems to keep reverting to spreadsheet mentality. When
evaluating the 75 fields, there will only be 4 possible answers (correct,
incorrect, FYI, & N/A). I need to be able to report on the total score for
that 1 audit based on correct & incorrect answers, but I also need to be able
to report on the total number of errors for each field.
Here is what I have so far:
Empl table
Emp ID # (PK)
Exam ID
Last Name
First Name
Supervisor Name
Mgr Name

Audit Info
Audit #(PK-autonum)
Exam ID(foreign key to Emp table)
Line of business
worksheet #
Auditor ID
date
Audit Type

Here is where is breaks down....how do I set up the table for the fields
that are audited. Do I list out all 75 fields that will be audited. This
seems like spreadsheet thinking to me, but I can't seem to get around this
thought. Anyone have any suggestions for me?

 

[Tim Ferguson]

Just to condense your tables so far:

EmplTable (*EmpID, ExamID, etc)

AuditInfo( *Audit, ExamID+, LineOfBusiness, etc)
FK ExamID references EmplTable

Here is where is breaks down....

Yes. I am not clear about these ExamID numbers. The usual practice is to
reference the PK of the table (i.e. EmpID); although what you have here
is not illegal I guess it's more complicated than it needs to be.

how do I set up the table for the
fields that are audited.

You need a "tall, thin" table that has lots of rows and few fields:

AuditAnswers(*AuditNum+, *QuestNum, Answer)
FK AuditNum references AuditInfo


It's possible to put validation rules on QuestNum to make sure it lies
within the range 1 to 75, but you can worry about that later.

Answer would be a one-char text field, restricted to C, I, F, N for
correct, incorrect, FYI and N/A.

This seems like spreadsheet thinking to me, but I can't seem
to get around this thought.

A fully worked example is available in Duane's At Your Survey:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%
20Your%20Survey%202000'



Hope that helps


Tim F

 

[Guest]

Thanks for your answer, Tim. OK, I think I am sort of beginning to get it.
Just so I understand...for the AuditAnswers table, I will have 75 rows of
answers for every 1 audit performed? So how would that play out on the form?
Sounds labor intensive on the evaluators part i.e. selecting the question
then inputting the answer (75 times). Is there a better, faster (automated
way) of doing things?

 

[Tim Ferguson]

Just so I understand...for the AuditAnswers table, I will have 75 rows
of answers for every 1 audit performed?

Yes, that's right.

So how would that play out on the form?

The GUI is a separate matter and always comes after the table design is
right. You have a number of choices:

Create a text file (paper scanner systems often produce these anyway) and
parse it;

Use a form/subform arrangement;

Ask the user to enter a string of answers: "CICCNFCCIIICCC" and parse
that it. Probably better to chop it up into pages or sets of 10 etc;

Have a form that includes the name, audit number etc; and one text box
for the answer and a button to store it; so the user would type C and
click the button. You could detect the keypress and save her having to
click the button. Remember to put on a label so the user can keep track
of which answer she is filling in;

Get the auditor to fill in the thing in real time!

Sounds labor intensive on the evaluators part i.e. selecting the
question
then inputting the answer (75 times). Is there a better, faster
(automated way) of doing things?

Have you looked at At Your Survey? -- it's a pretty similar architecture.

All the best


Tim F

 

[Guest]

When I tried to go to the At Your Survey example, I got the following message:
ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

/otherdownload.asp, line 32

I received this message both when I tried using the link and directly typing
in the address.