Survey in Access

[tuxalot]

I've read a lot of posts about creating a survey in Access and I know I'm in for no easy task. But the hope is that I will learn a lot along the way. You all helped a lot on my previous project and I am most grateful. It's been a while since I've been away from Access so I'm rusty to say the least. Anyhow, here is what I would like to accomplish and the parameters. Any guidance you can provide would be very much appreciated.

The survey will be completed by a team of surveyors by entering data directly into an Access form. These surveyors are analyzing the safety and security aspects of businesses and the visits to the businesses take 1-3 days.

There are approx. 200 questions grouped in five or six general categories. To answer the questions the surveyor must meet with employees in several departments. So let's say we have categories A-E, departments 1-6. Employees in department 1 may have to answer questions in categories A, C & E. So I would like a way to group the unanswered survey form at the start of the visit by category or department depending on how the surveyor wants to flow through the visit.

All responses are either yes, no, or N/A.

For 'no' responses, I would like a text box on the userform to activate to allow a surveyor to enter a comment.

Some skipped questions may be completed on day two or three of the visit, and I would like to be able to generate a report showing skipped questions so a surveyor can easily navigate back to complete those questions. I suppose if I can hide the answered questions right on the original userform that would be even better.

REPORTING:
The report will be generated for a single business and should only show the questions and comments for the 'no' responses.

ANALYSIS:
I would like to provide a 'weight factor' to the questions, based on the severity of the safety risk. So the end result would be an overall score of sorts. Also, I want to be able to analyze scores within a category across multiple businesses visited. Finally, I would like to see how many businesses answered 'no' for each question.

I'm sure there will be other analyses needed but this should get us started.

I want to build this normalized, and have attached a picture of my current table structure. I don't think I need all of these tables since I only have one option group. But I got this from another post and tried to modify it to my needs. I see the resolution is poor, so I have included a mediafire link HERE to a higher-res version.

TblOhm is a list of medical consultants that manage several businesses in the network.

TblSurveyBy is a list of surveyors.

TblProperties includes names of individuals that may move around to other properties in the network so I am thinking that I should put them in separate tables. Would this be preferred?

What relationships should I establish for TblOhm and TblSurveyBy?

Thanks for looking and pointing me in the right direction.

Darin

[FishVal]

Hi, tuxalot.

Could it be so that the same survey may be answered several times by different surveyors. If so you need an additional table. If not [tblSurveys] will serve as such, in this case you need to relate [tblSurveyby] to [tblSurveys]. And, sorry, but [tblOhm] doesn't make much sense, what it is all about?

Kind regards,
Fish

[tuxalot]

Hi Fish,

There are 80 businesses to be surveyed and they will be visited annually. tblOhm is a list of names of medical consultants that the businesses have hired to manage work related injuries. Each OHM is responsible for a group of businesses in the network (maybe 5-10 each). So I assumed they would need their own table but perhaps having their contact information (I only need to capture their name) as a lookup table in tblProperties would be preferred?

Not sure - I'm very new to Access and will need to approach this step by step.

I appreciate your guidance,

Tux

[FishVal]

Hi, Tux.

It is still somewhat unclear how [tblOhm] could be related to whatever table since I could not figure out how what you've posted could define your database business logic.
Please explain what is the relation between businesses and surveys or surveyors or whatever else in your database.

Kind regards,

Fish

[tuxalot]

@FishVal
Sorry for the confusion Fish but thanks for sticking with me. Figure [tblOhm] is a list of individuals called OHM's that are independent consultants working with the businesses. Same for surveyors, which are listed in [tblSurveyBy]. I wish to track survey responses by surveyor and OHM, among others. Since the two groups, OHM's and surveyors are not directly tied to the businesses shown in [tblProperties], I thought I had to place these groups in separate tables.

[tuxalot]

Fish - any additional thoughts on this? Maybe I should simplify it, get it working then expand from there.

[tuxalot]

Hmm...still no response. Can anyone else help with this? I think I started off too complicated so maybe I need to break this down a bit.

[tuxalot]

I'll resurrect this thread to say that I have a working example of the survey database that works quite well, but would like to add some additional functionality requiring coding that is way beyond my abilities. I don't have much free time and would rather hire someone here to just get 'er done. If interested, please contact me.