Need help and/or opinion on using Access for survey results.

[maddoxx]

I have an Access database that is continuosly updated through a survey I made with FrontPage 2002. The database is linked to the form on a web page where it is updated nearly every day.

It's for a customer to fill out after a stay with us at our vacation resort.

The information in the table looks something like this...

So now that I've got this information in a table, I'd like to know the best way to come up with some useful statistical information...

Where I can count the number of Execellent, Good, etc for each question and then put it in a graph of some sort. I've tried to create some queries, but from what I've seen, I may have to create a query for each question.

In the past, I've used Excel and taken the data from Access and inserted into work sheets. I would count the number of "Excellents", "Goods", "Fair", etc and then create a chart from there.

But doing it that way, I'm having to go through an extra step of having to import into Excel, which I constantly have to modify. Besides which, it is VERY time consuming. Would it be easier to keep doing it through Excel or should I try an Access solution?

And if Access is the better solution, does anybody have any solutions or links to some information on the best way to do this?

Thanks so much,

Jim

 

[RichMorrison]

Jim,

It would be easier to create summary queries and graphs if your data was more "normalized". Making columns for the various categories - Reservation, Bellhop, Overall - complicates things.


If you could make a table that contains:

Customer Number,
Category,
Rating

then you could make a "cross tab" query that would show the categories down the left, the ratings as columns, and the number for each category/rating at the intersection.


This type of query can be used as input to a report with an internal graph.

HTH,
RichM

 

[CCowley]

Possible survey solution

I have made queries to summarize survey results. There are many solutions to this problem. One I can think of right now may be a little cumbersome but it would work. you have three fields: Res, Bell, Overall; amd maybe four choices excellent, good, etc. So in a query you could have 3X4 or 12 additional calculated fields. Say for a customer that rated the overall experience as excellent there would be a 1 in that field and 0 in the other three overall experience fields. You can alter the formula below to calculate all of the customer responses.

OverallExpExcellent: iff([OverallExp] = "Excellent", 1, 0)
OverallExpGood: iff([OverallExp] = "Good", 1, 0)
etc.

Then summarize the data in a report or summary query. I don't know if this helps, I hope so

 

[maddoxx]

Survey

Thanks for the help guys.

I tried to come up with something by Cowley's suggestion, but I couldn't get it to work. I'll keep tinkering a bit and see what I can come up with.

When I first tried doing something with that table, I tried to create a query where it would count the number of "excellent", "good", etc. by using the Total field in the query. It would count the total number of entries, but when I put "excellent' for the criteria, it didn't like that and gave me an error.

Rich, what would make my data table more "normalized". I'm not sure what you mean by that. I'm not sure if I could fix that even if I could because I'm stuck with whatever format FrontPage decides to export it to.

I appreciate the time you guys took to answer me.

Regards,

Jim

 

[RichMorrison]

you wrote
<<
Rich, what would make my data table more "normalized". I'm not sure what you mean by that.
>>

I mean what I wrote yesterday
<<
If you could make a table that contains:

Customer Number,
Category,
Rating
>>

It appears that you have a table with something like
Customer Number,
Category,
Fair rating #,
Good rating #,
Excellent rating #,
etc, etc.

It is tedious to summarize this type of structure because you have to sum each named column. With one "Rating #" per record, Access will do much of the work for you.

RichM

 

[maddoxx]

survey

Hi Rich,

I really don't understand how I could have made this table any different. The table, to me, is designed like this....

Customer #,
Category 1,
Category 2,
Category 3,
Category 4,
Category 5

Each record (or customer) will have a unique response for each category....(excellent, good, fair, poor, bad)

Couldn't I just have some kind of query designed where it counts the number of times each response (excellent, good, etc) appears for each category (or question)?

And then take those numbers and compare them to how many responses there were to get percentages?

Thanks again for your help.

Jim

 

[RichMorrison]

you wrote
<<
Customer #,
Category 1,
Category 2,
Category 3,
Category 4,
Category 5

Each record (or customer) will have a unique response for each category....(excellent, good, fair, poor, bad)

Couldn't I just have some kind of query designed where it counts the number of times each response (excellent, good, etc) appears for each category (or question)?
>>

Yes you could design a query, but .....
it would be much easier with the table structure I have described in earlier posts. If each record represented exactly;
ONE customer,
ONE category,
ONE rating
then you will have an easier time summarizing by customer, category, rating or any combination.

Look in Access Help for "cross tab" queries. It will explain the idea and show examples you will find useful. Cross tab queries let you show data in a spreadsheet like style while storing the data in a "normalized" structure.

RichM

 

[maddoxx]

survey

Okay, I understand what you're saying, it would be easier to have the table structured that way, but I'm kind of stuck with the way I have it designed now.

I would have to have a seperate table for each category the way you described it, and I really can't change the way FrontPage is exporting into Access at this point.

Oh well, I'll come up with something.

Thanks,

Jim

 

[RichMorrison]

you wrote
<<
I would have to have a seperate table for each category the way you described it, and I really can't change the way FrontPage is exporting into Access at this point.
>>

OK, if that's the way it is. What you could do is make a group of queries to extract each category and append into a normalized table.

Then do analysis and reporting against the normalized table.

RichM