Crosstab query help (A2k)

Topic

New Reply

I want to use fields as row headings, and field values as column headings. There are only 5 possible column headings. The database is set up for a survey, of which there are 18 questions, and only 5 possible answers for each question (the numbers 1-5). I have the table set up with 18 fields (Q1-Q18), and each field uses combo box selection. I want my crosstab to look like this:

	1	2	3	4	5	Total
Question 1	10	0	3	7	2	22
Question 2	5	5	7	3	2	22

And so on…

Reply | Quote

Replies

Nothing?

Reply | Quote

It’s an unfortunate design to use a separate field for each question. It would have been much better to use a separate table in which each answer is a separate record:

SurveyID	QuestionID	Reply
1	1	5
1	2	5
1	3	2
1	4	3
1	5	1
1	6	3
…	…	…

It would then have been a simple crosstab query. If possible at all, you should switch to this design.

Reply | Quote

Okay, I’m still in the development stage, so it’s not a huge shot to my motivation. I’ll run with that. Thanks Hans.

Reply | Quote

New issue: after creating a table for each question, I’m stuck with 55 tables! When I try to base my form off a query, it says that the query is too complex.

(Query SQL attached).

Additional thought: would creating a separate query for each question be feasible, and would it be an effective design?

Reply | Quote

Noooo….!

You shouldn’t create a separate table for each question! See for example post 607,991.

Reply | Quote

I suppose my question now is, (since seemingly that table will grow by 50+records each time the survey is taken), when do I have to worry about bloat?

Reply | Quote

The records will be very small – each will take up only a few bytes. So bloat shouldn’t be a problem.

As always, it is a good idea to compact and repair the database from time to time.

Reply | Quote

Hans,

Any idea of an ‘easy’ way to transform the data from the ‘one table’ design into the multiple table design?

Reply | Quote

If you search this forum for normalize you should find some examples. Also see Normalize/Denormalize/url] on Roger’s Access Library site. You’ll have to fine tune any code you find for your situation.

Reply | Quote

OK, now for my tu’pence worth. I did a survey database some moons ago where I used option groups to ascertain the correct value 1 through 5 for your example. You just need 18 fields corresponding to the 18 questions you want. As each survey is created then a new record is created.

In Excel I connected to the database and then extracted/refreshed the data into a worksheet and then created pivot tables against the imported data. This Excel workbook was connected through the network to the people needing the report and each time they opened it the data refreshed.

I have done similar active datasets for management information via my SQL servers sessions tables to extrapolate data so that I can graph them. By far easier than this somewhat horrendous SQL statement

Reply | Quote

Any ideas to keep it as an Access report? Am I going to run into issues with the amount of data queried? Also; there are some fields that are for comments…I’m going to have to find a way to include those in the report.

Reply | Quote

No sorry, I would still do it in Excel….expedience of service I’m afraid…needs must when the Devil drives…

Reply | Quote

I understand your “dirty work” and have succumbed to the needs of the devil; however, this time is different…I’ve got the time to set it up as I please, and on my own schedule.

Aside: my own schedule = until the single data entry clerk who is *not* a touch typist completes inputting the data in to my first revision of the database. I will parse the data into my final scheme later on. The clerk has about 2000 paper questionnaires to input, and averages (at best) 100 a day.

Reply | Quote