Design of a database to log site audits (1 Viewer)

PaulD2019

Registered User.
Local time
Today, 09:42
Joined
Nov 23, 2019
Messages
75
Hi all,


I need to build a database to log the results of the audits our managers do on site, I haven't ever built anything like that before & I was wondering of the best way to go about it.


Each audit would have the following fields updated on a table


AuditID (pk)
AuditDate (date/time)

Client (short text)

SiteAddress (short text)

SiteSupervisor (combo box to select from another table with list of supervisors)
ManagerCarryingOutAudit (combo box to select from another table with list of managers)


After the above data has completed whoever is filling in the data will need to fill in the results of the audit taken on site which comprises of 20 questions, each question would have Pass, Fail & N/A as an answer & to make up the audit of upto 100%



Pass = 5
Fail = 0
N/A = 5


As a start for the database would it be better to house the 20 questions in a separate table or have them in them in the main table?
What would be the best way to build the audit result? Moving forward I would like to make the databse track the results by date & by the supervisor who was audited.


Any help would be appriciated


Thanks
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:42
Joined
Jan 23, 2006
Messages
15,361
Hi Paul,

Do you have a document listing the requirements? My recommendation(s) is to NOT guess what a log for audit info might mean. Either get some sample outputs from the proponents of the database, or get those proponents to supply some broad requirements that you and the proponents can collaboratively flesh out the details of the requirement(s). Make sure there is agreement on scope and expectations.
Build a data model and confirm that model with others. Create some mock-ups of inputs/outputs and get feedback to ensure the project is on target. Do NOT be too quick to jump into physical Access.
There are links to a variety of articles on Database Planning and Design in my signature.
The "knowledge nuggets" by BA-Experts are quite humorous and helpful.
 

PaulD2019

Registered User.
Local time
Today, 09:42
Joined
Nov 23, 2019
Messages
75
Hi jdraw,


The requirements are basically listed in my post, we have a set of 20 questions on a sheet that is taken to the sites we are auditing & the database needs to be able to store the results of the audits with the same questions answered to calculate the result.


I will have a look at your links & see if it helps me in any way


Thanks
 

vba_php

Forum Troll
Local time
Today, 04:42
Joined
Oct 6, 2019
Messages
2,884
Here's a 2-bit take on what you might want to do:
SiteSupervisor (combo box to select from another table with list of supervisors)
ManagerCarryingOutAudit (combo box to select from another table with list of managers)
if you're talking about including combos in your actual *table fields* instead of on forms, that's not a good idea. everybody else around here will say the same thing. but I personally use lookup tables all the time, but the data is looked up by combos that are on forms, not by way of *lookup fields* that are available to be chosen as a field type in table design view. I don't think some of the experts here agree with my view on this though.
As a start for the database would it be better to house the 20 questions in a separate table or have them in them in the main table?
most certainly in a separate table, linked to the audit table by [audit ID]
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:42
Joined
Jul 9, 2003
Messages
16,243
whoever is filling in the data will need to fill in the results of the audit taken on site which comprises of 20 questions, each question would have Pass, Fail & N/A as an answer & to make up the audit of upto 100%

From the gist of your question I am assuming you may benefit from my blog on "How to build a Checklist in MS Access".

I have created several playlists on YouTube explaining how to create a checklist, and in my Nifty Access Blog, I demonstrate how you can easily change the checklist from using a checkbox to using a textbox to gather answers to questions. https://www.niftyaccess.com/checklist-user-case-examples/ see this video in particular:- Add a Questionnaire to your MS Access DB https://youtu.be/qEwjk05VARI
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:42
Joined
Jul 9, 2003
Messages
16,243
After reading your question again, I think this video might be more appropriate as it shows you how to add a pre-existing list stored in a table to the subform.

https://youtu.be/CrFcaS7Uc3w

Sent from Newbury UK
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:42
Joined
May 21, 2018
Messages
8,463
First step is to talk through your tables. To start

I will assume your answers are as you say, always pass, fail, NA. If not it will get more involved

Code:
tblQuestion
  questionID 'PK
  questionText ' short text or memo

tblAuditInfo
 AuditInfoID
 AuditDate 
 Client_FK 'foreign key to personnel table
 SiteAddress
 Manager_FK 'foreign key to personnel table
 SiteSupervisor_ FK ' fk to pt 
 other specific to that audit

tblAnswers
 answerID
 answerText
 answerValue

tblPersonnel
 personnelID
 'personnel fields first, last, mi, phone, email......
 personnelRole ' manager, supervisor, client

Junction table for the actual audit answers

tblAuditAnswers
 auditInfoID_FK ' foreignKey to audit info
 questonID_FK
 answerID_FK

When you start a new audit after filling out the audit info, you should have a button to do an insert query into audit answers. It will insert 20 question IDs and the auditInfo key into the junction table.
 

PaulD2019

Registered User.
Local time
Today, 09:42
Joined
Nov 23, 2019
Messages
75
Here's a 2-bit take on what you might want to do:if you're talking about including combos in your actual *table fields* instead of on forms, that's not a good idea. everybody else around here will say the same thing. but I personally use lookup tables all the time, but the data is looked up by combos that are on forms, not by way of *lookup fields* that are available to be chosen as a field type in table design view. I don't think some of the experts here agree with my view on this though.
Sorry, I have seen that posted before, I should've put that the combo box would be on the form, just as part of m learning curve, how come you don't like the lookup fields that are available when setting up forms but others think they are ok?

most certainly in a separate table, linked to the audit table by [audit ID]
I thought as much, just wanted to check though :)

From the gist of your question I am assuming you may benefit from my blog on "How to build a Checklist in MS Access".

I have created several playlists on YouTube explaining how to create a checklist, and in my Nifty Access Blog, I demonstrate how you can easily change the checklist from using a checkbox to using a textbox to gather answers to questions. https://www.niftyaccess.com/checklist-user-case-examples/ see this video in particular:- Add a Questionnaire to your MS Access DB https://youtu.be/qEwjk05VARI
I was looking online before I posted this thread for something similar & looked as some checklist & questionnaire ones but wasn't sure that they were enough like & need because as they are just checklists they aren't totaling up the data etc.

After reading your question again,I think this video might be more appropriate as it shows you how to add a pre-existing list stored in a table to the subform.

https://youtu.be/CrFcaS7Uc3w

Sent from Newbury UK
Not sure this is anything like what I need either, thank you though

First step is to talk through your tables. To start

I will assume your answers are as you say, always pass, fail, NA. If not it will get more involved

Code:
tblQuestion
  questionID 'PK
  questionText ' short text or memo

tblAuditInfo
 AuditInfoID
 AuditDate 
 Client_FK 'foreign key to personnel table
 SiteAddress
 Manager_FK 'foreign key to personnel table
 SiteSupervisor_ FK ' fk to pt 
 other specific to that audit

tblAnswers
 answerID
 answerText
 answerValue

tblPersonnel
 personnelID
 'personnel fields first, last, mi, phone, email......
 personnelRole ' manager, supervisor, client

Junction table for the actual audit answers

tblAuditAnswers
 auditInfoID_FK ' foreignKey to audit info
 questonID_FK
 answerID_FK

When you start a new audit after filling out the audit info, you should have a button to do an insert query into audit answers. It will insert 20 question IDs and the auditInfo key into the junction table.
Thanks MajP, I think this may be more along the lines of what I am looking for, I'm going to have a look at that as a set up & see where I get from there
 

vba_php

Forum Troll
Local time
Today, 04:42
Joined
Oct 6, 2019
Messages
2,884
Show come you don't like the lookup fields that are available when setting up forms but others think they are ok?
I was referring to the evils of using lookup *fields* in tables. the real experts around here have always advocated against them. this is why, I suspect:

The evils of using lookup fields in tables

You'll notice that a lot of people who respond to you here have the title "Access MVP" user their username. I would assume that all of them are a fan of that page.
 

Users who are viewing this thread

Top Bottom