Need to tally the answers to a questionnaire in a combo box 1

[webcats2]

My database is a list of questions with yes/no/na box. There are 18 sections and approx. 100 questions. The main tables look like this:

tblCustomer:
customer#
CustomerLastName
Customer1stName
Date
Reviewer Name

tblQuestion:
AQ1
AQ2
AQ3
AQComments
BQ1
BQ2
BQ3
BQComments
etc....

The question table uses a lookup table for the yes/no/an answers

1 = yes
2 = no
3 = n/a

Now I need to create a report that totals the number of yes/no/na's.

Tally Report

AQ1.Do you live in a flood zone? Yes= 10; No = 150; N/A= 0
AQ2.Do you have flood insurance? Yes= 5; No= 5; N/A= 150

I need the Summary for each section and then grand totals.

I have no idea how to build the query to get this to work.

The actual questions are in the forms and reports. I am thinking of putting them into a table but that might be too big to query on.

 

[hneal98]

is there something to link the tblCustomer and the tblQuestions?

 

[webcats2]

Yes, the customer #. Oops, I forgot to put that in That is how the questions are linked to the customer information.

Thanks

 

[dhookom]

webcats,
This would be much easier if you had normalized your table structure. There is a complete survey application "At Your Survey" at

http://www.rogersaccesslibrary.com/OtherLibraries.asp.

There is a report in the app that matches your request. You won't have to change a form, table, query, report,...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[webcats2]

Hi Duane,

Sorry it tooks so long to get back to you.

Thanks! What a great site.

 

[dhookom]

I agree with your response to the site. Roger Carlson has done a great job with providing a ton of samples.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.