Questionnaire Logical problem


I am attempting to create a in-house questionnaire for our sales team to allow them to better quote for certain projects.  There are a number of questions with multiple choice responses.  Based on these responses i would like to generate a number which would relate to a specific multiplier which we use to add to the base figure for the job (hope this is making sense?)

Any suggestions for the best approach to this?  It's a bit like those online personality questionnaires which give you a figure at the end of the questions saying if you scored 1-50 you are "insert generic state of mind here".  If you scored 51-100, etc, etc.

I'm looking at building this initially in MS Access with the hope of upgrading to a web based system at some point).

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Don't even bother with Access for the survey; use Survey Monkey:

 or another such service.

 Will take you far longer to re-invent the wheel if you don't have anything already.  ou can do the analysis in Access, but for the survey itself, use one of these services.
anthonytrAuthor Commented:
Hi Jim,

Don't think surveymonkey is the way forward for this.  

I require an internal tool to create/update a pricing structure based on questions answered by customers during the initial quotation stage.  I'm looking at the logic behind my system which will hep our sales peeps get to a final costing based on the answers.

In basic terms, I don't need to see the answers, just the final score (based on the answers) which is then used to create a 'modifier' which is applied to the base figure of their initial quote.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

Well this is the rub:

I'm looking at building this initially in MS Access with the hope of upgrading to a web based system at some point).

 As Access simply can't do this.  Microsoft gave up on Access Web Apps.  So you'd be looking at using ASP.Net, PHP, Word Press site, etc to achieve what you'd want.   SharePoint is a possibility also. Think the others would be easier though.

 Outside of that, in terms of the survey, it seems straightforward enough.   one point per question "right", and at a specific score, do xyz.

 Most survey's I've seen in Access follow the approach that the wizards do; series of panels with forward/back button's and a finish.  One question per panel.

Four tables:

Survey Header - Name, date created, etc
Survey Questions - Question number, possible answers, correct answer.

Survey response header   - Who, what project/survey, date, etc.
Survey response details - What question they picked for an answer

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Gustav BrockCIOCommented:
Or, if you are in an organisation using Office 365:

Microsoft Forms
anthonytrAuthor Commented:

I think i have confused matters by calling it a questionnaire.
Gustav BrockCIOCommented:
Not according to your description. Seems like a questionnaire to me.
The problem is that you want to start with Access and "upgrade" to a web app.  Personally, I don't consider web apps to be an upgrade which is why I develop with Access rather than some web tool.

There is NO CONVERSION PATH from Access to a web app (and there wasn't with Access Web Apps either which led directly to their demise).  Yes, the data can be converted to a different RDBMS but you will need to completely rewrite the application itself so unless you need the practice or want to build a prototype, you should develop in the target platform.

Luckily, much of the calculation work will be done with queries and although those are also not directly convertible, the Access variant of SQL is very similar to T-SQL so as long as you don't use UDF's (User Defined Functions) or VBA functions that don't have direct SQL equivalents you wont' have a problem converting the querydefs.

I've created a number of questionnaires/surveys and and all start with the four basic tables that Jim listed.  However, most need more tables and if you want to support multiple questionnaires (why not, it's very little more work) you will need a way to assign questions to surveys using a junction table and if the survey is longer than a single page, you probably want a grouping table so the questions can be grouped to make the survey easier to work with.  As far as the answers go, if all questions are ALWAYS multiple choice, and the score for answer #1 is always x and the score for answer #2 is always y, etc. then you can get by with a simple answer table.  If the scores vary by questions, you need a more complicated answer table that will probably include a lot of duplication.  The last questionnaire I app I built was for a relocation company and there were a number of questionnaires and the answers could be multiple choice, text, Y/N, and a couple of other options.  That made the problem more challenging.  The printed questionnaires were no problem because it was easy to substitute one type of control for another on a report but the forms were more difficult.
John TsioumprisSoftware & Systems EngineerCommented:
Yes there is one more suggestion
You can use LimeSurvey which has a Community Edition and lets you host it by yourself...the installation is just as easy as configuring WAMP/XAMP...essentially a Webserver with PHP Support.
The nice thing on LimeSurvey (speaking of my experience i haven't worked with anything else) is that it has a MySQL BE(Also it can use MSSQL but again i haven't worked with this configuration) that is "way too easy" to connect and interact with Access.
You setup your questions...(this is done via the LimeSurvey admin web interface)  that can belong to groups and have sub questions and all the nice stuff you expect from a complete Questionnaire  solution and you send link to each of the employee which are machine bound each workstation 1 Questionnaire ...(i think there are other options but it has being sometime).
So you take this raw them to Access and you are ready to create all kind of neat reports in Access...i had even one report that besides the numeric data had a tiny line chart to depict the trend...
It will take some time ...but if Questionnaires is necessary it will be well worth it and...its free.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Good reference John.  I like the ability to host yourself.  I really hate giving up my data to the "cloud" with no assurance of ever getting it back or of being able to link to it and use it outside the application.  Hosting it yourself gives you a lot more options.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.