Pivot table - Input

[Aviram]

Hi,

I have a db with 3 tables (among others):
1. People - Including personID, name
2. Questions - Including qID, question
3. Answers - Including personID, qID, date, value

Both People and Questions tables are dynamic - records there can change with time.

I want to create an input form with a tabular look in the following scheme:

Date: xx/yy/zzzz

Person \ Question q1 q2 q3 q4 ....
p1
p2
p3


p4
...

Of course, both rows and columns (records and fields) must be taken from the relevant tables dynamically, and the recordset must be open for editting.

Initially, a pivot table came to mind, but obviously, it can't be editted by the user. I also tried to create a query on the fly (with code and loop), but it also didn't work. Tried an un-binded form with on-the-fly created textboxes, but then all records contained the same value.

Any ideas, anyone?

Thanks,
Aviram

[June7]

This would be way too complicated. Just build form to show one person record at a time.

How many questions are there? How long can question statements be?

[Aviram]

This would be way too complicated. Just build form to show one person record at a time.

How many questions are there? How long can question statements be?

June7,
Thanks for the reply.
There are quite a few questions. Currently, about 40 questions, in 5 different categories, but they are subject to change by the user.
The statements are no longer than 30 characters, and the answers are always 1,2 or 3.

The user is supposed to fill the answers for all of the People together, and there's a meaning to the online comarrison between them.

I'm not afraid of trying complicated things, if you have an idea that might work...

[June7]

What is 'online' - is this web database?

The only thing I can think of is complicated VBA code to build the form 'on-the-fly'. Then code to save new records or update existing. The code would create as many unbound controls as are needed for the number of people retrieved in a query. Each person would be a 'row' of unbound controls. So if there were 40 questions and 50 people, that would be a matrix of at least 200 controls that would be placed on form. Forms have a limit of how many controls can have. http://blogs.office.com/b/microsoft-...07-limits.aspx

You could build a form that would allow for maybe 10 people and 10 questions at a time (a matrix of 100+ controls). The form would be saved with this setup and code would then dynamically populate the form for each 'page' of people and questions.

No matter what, this is complicated.

Good luck!

[Aviram]

Thanks for the idea. I already tried half the way - wrote the code to create unbound controls for each question, but I hoped to use them in a multiple-record form, where each person is a record. When I did that, all record-duplicates of each control contained the same value. I thought of somehow binding them to a temporary table. What do you say about that?

I'll try creating the entire matrix anyway.

Thanks again,
Aviram

[June7]

Temp table won't get the data into the main table. Still need code to save records.