Audit Database? (1 Viewer)

[corrinebean]

I'm self-taught on Access over the years; I know more than the basics but most of the 'advanced' is still way over my head. I've created a few databases over the years out of needs at work to make my life easier.

I've been trying to develop a database to log audit results, and keep getting stuck. Does anyone out there have a good template they could share?

Basically - I audit records each month and log the results; I've been doing it in Excel, but would like to move it to Access so I can begin expanding it for different reporting purposes.

I have two different audit types that are done each month; both audit types store the record owner's name and the file number, and then each has a set of questions that is asked. Answers are yes, no, or NA. One of the audit tools does allow for additional information to be logged to break out compliance by individual users (Example: Q: "Was the form dated?" A: John Doe - No; Sallie Sue - No; Jane Doe - Yes).

 

[DevastatioN]

If I get this correctly, you just record the username, the file number, and answers to a series of questions.

Some questions that may help clarify:

Do both audits have the same questions?

Do both audits use the same number of questions?

Are the questions always static? Or do they vary on previous questions/answers?

I'm going to assume the questions are static and say go with this type of design:

tblAudits
AuditAuto (Prim Key)
DateofAudit
AuditType (You said you had two)
ConductedBy
Username (Dropdown from a Users table)
Filenumber
Q1
Q2
Q3
Q4... etc. (All having dropdowns of NA, Yes, No)

I argue that the Questions should be in the table as is, because they are static, you always ask the same questions and they should be displayed to you in the form, as one field in the table per question.

If they have different questions that are static, you can do A_Q1 etc. vs B_Q1 etc. but this can get sloppy. It will work if the questions are static, you aren't asking 30-50 questions for each audit type, and you don't have 10,000 audits.

If your questions vary in any way, or you wish to the last example this way, create another table called tblAuditQuestions (technically a junction table as you'll see). Which has QuestionAsked field (which links to a tblQuestions as a dropdown), and an Answer field (which links to a dropdown for Yes, No, NA).

Create a form for the main Audit table, with a subform which shows "Question Asked" and "Answer". This way you can manipulate and vary questions asked and answered.

Hope this helps,

 

[corrinebean]

Thank you - already very helpful information. I think I start getting too stuck in my own head!

The two audits have different questions on each; one is shorter than the other.

One of the audits asks that the auditor gather additional information on users on a couple of the questions- depending upon the record audited and the entries in the record, there may be 0 entries - or multiple. For example - one of the questions asks whether all entries in the record were dated. If multiple users made entries in the record, some or all of them may not have dated correctly, and I'd want to log that - and then be able to run reporting on that indicator later for a quarterly user 'report card'.

Does that make sense? Would you recommend two separate tables for the two different audit types, since they share some data (users, auditor)?

 

[DevastatioN]

Based on your clarifiction, I would recommend the table approach for the questions, especially since you brought another note to light.

Your audit isnt really for the user and the file, it's for the file, which happens to have multiple users.

If I understand this again, you could have something like this:

Audit Type A
File: 2010-2005781-4328

Subform:
Question: Did you date File?
User: Carry
Answer: no

Question: Did you date File?
User: John
Answer: Yes

It appears that the user should be brought down to the question level. Then you'd have a reference of all questions asked, and to which users, and their answers, for a single file.

You can then run reports of "Show me every file Carry got involved in, and the answers" or, "show me all files that John didn't date" etc.

Something could technically be said about the main form having a dropdown list for user, and a dropdown list for the file, but I'm gonna imagine that there's a huge number of files and they are rarely audited more than once.

So the tblQuestionsAnswers now has:
QuestionAnswerAuto (Autonum)
AuditAuto (Foreign Key, set to Number - Long)
Question (Dropdown from tblQuestions)
UserAsked (Dropdown from tblUsers)
Answered (Dropdown Yes, No, NA)