Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-05-2020, 04:42 AM   #1
PaulD2019
Newly Registered User
 
Join Date: Nov 2019
Posts: 22
Thanks: 19
Thanked 1 Time in 1 Post
PaulD2019 is on a distinguished road
Design of a database to log site audits

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

PaulD2019 is offline   Reply With Quote
Old 01-05-2020, 05:19 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,385
Thanks: 105
Thanked 2,058 Times in 2,002 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Design of a database to log site audits

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
PaulD2019 (01-05-2020)
Old 01-05-2020, 05:53 AM   #3
PaulD2019
Newly Registered User
 
Join Date: Nov 2019
Posts: 22
Thanks: 19
Thanked 1 Time in 1 Post
PaulD2019 is on a distinguished road
Re: Design of a database to log site audits

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

PaulD2019 is offline   Reply With Quote
Old 01-05-2020, 06:10 AM   #4
vba_php
Banned
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 1,307
Thanks: 127
Thanked 313 Times in 300 Posts
vba_php is an unknown quantity at this point
Re: Design of a database to log site audits

Here's a 2-bit take on what you might want to do:
Quote:
Originally Posted by PaulD2019 View Post
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.
Quote:
Originally Posted by PaulD2019 View Post
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]
vba_php is offline   Reply With Quote
The Following User Says Thank You to vba_php For This Useful Post:
PaulD2019 (01-05-2020)
Old 01-05-2020, 06:28 AM   #5
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,529
Thanks: 594
Thanked 1,007 Times in 949 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Design of a database to log site audits

Quote:
Originally Posted by PaulD2019 View Post
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/checklis...case-examples/ see this video in particular:- Add a Questionnaire to your MS Access DB https://youtu.be/qEwjk05VARI
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|  
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
PaulD2019 (01-05-2020)
Old 01-05-2020, 07:19 AM   #6
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,529
Thanks: 594
Thanked 1,007 Times in 949 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Design of a database to log site audits

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
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|  
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)

Last edited by Uncle Gizmo; 01-05-2020 at 08:41 AM.
Uncle Gizmo is offline   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
PaulD2019 (01-05-2020)
Old 01-05-2020, 07:26 AM   #7
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 2,052
Thanks: 39
Thanked 647 Times in 594 Posts
MajP has a spectacular aura about MajP has a spectacular aura about MajP has a spectacular aura about
Re: Design of a database to log site audits

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.

MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
PaulD2019 (01-05-2020)
Old 01-05-2020, 08:01 AM   #8
PaulD2019
Newly Registered User
 
Join Date: Nov 2019
Posts: 22
Thanks: 19
Thanked 1 Time in 1 Post
PaulD2019 is on a distinguished road
Re: Design of a database to log site audits

Quote:
Originally Posted by vba_php View Post
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?

Quote:
Originally Posted by vba_php View Post
most certainly in a separate table, linked to the audit table by [audit ID]
I thought as much, just wanted to check though

Quote:
Originally Posted by Uncle Gizmo View Post
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/checklis...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.

Quote:
Originally Posted by Uncle Gizmo View Post
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

Quote:
Originally Posted by MajP View Post
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
PaulD2019 is offline   Reply With Quote
Old 01-05-2020, 08:09 AM   #9
vba_php
Banned
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 1,307
Thanks: 127
Thanked 313 Times in 300 Posts
vba_php is an unknown quantity at this point
Re: Design of a database to log site audits

Quote:
Originally Posted by PaulD2019 View Post
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.

vba_php is offline   Reply With Quote
The Following User Says Thank You to vba_php For This Useful Post:
PaulD2019 (01-05-2020)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Which forum site is the AWF of Web Design? Which Web Host is the best? ashleedawg Web Design and Development 3 05-29-2019 05:04 AM
[SOLVED] Responsive design for mobile site kieuanna96 Web Design and Development 0 03-22-2018 01:20 AM
Audits AnthonyGerrard SQL Server 4 04-08-2016 07:17 AM
Site design Graybenham Web Design and Development 5 09-14-2011 11:28 AM
Site map table design help cygnusx197 Tables 1 09-24-2006 01:53 PM




All times are GMT -8. The time now is 10:40 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World