Insert SQL statement

[evergrean100]

I have a table that has 150 fields in an Access 2000 database and it will have about 1000 records inserted into the database. It has simple number rating questions such as:

Question                        Choose One Rating
1-How would you rate redRock1?    1   2   3  
2-How would you rate rockFrm?  1   2   3  
3-How would you rate greRock3A?   1   2   3  
...147 more questions here

The wide database structure has alot of fields with 150 inserts..

...form part
input name='r1'  type='radio'  value='1'>1
input name='r1'  type='radio'  value='2'>2
input name='r1'  type='radio'  value='3'>3
input name='r2'  type='radio'  value='1'>1
input name='r2'  type='radio'  value='2'>2
input name='r2'  type='radio'  value='2'>2
input name='r3'  type='radio'  value='1'>1
input name='r3'  type='radio'  value='2'>2
input name='r3'  type='radio'  value='2'>2
...147 more to go...

.....insert into database
insert into tblRocks (r1,r2,r3....147 more)
values (#form.r1#, #form.r2#, #form.r3#, ....147 more)

This gives me data structure like this:
tableID   r1       r2    r3      ....147 more here
1         1        3     2       ....147 more here

I want to make the table structure more efficient like this:

tableID   RNumber   rating
1         1         1
1         2         3
1         3         2
...147 more here
2         1         3
2         2         2
2         3         2
...etc

I cant seem to figure out how I would do the more efficient longer data structure way with my form and insert statements especially the sql insert part.

Please advise.

 

[r937]

what is tableID? where do those values come from? they aren't in your "wide" insert statement

r937.com | rudy.ca

 

[evergrean100]

tableID would be the primary key autonumber in the wide table entry so I dont have it in my wide insert statement since it automatically inserts that number in the database.

 

[r937]

but then you say you want the narrow table design, so what is tableID doing there? it cannot be an auto_number because it repeats

r937.com | rudy.ca

 

[evergrean100]

Thanks,

Yes for the narrow design I see now I have to eliminate the tableID and make it a repeatable number up to 150 to cover all questions. Please advise how I would write my insert statement for this to work?

 

[r937]

so you want your new table to have only 2 columns?

insert into new table (RNumber,rating) values (1, #form.r1#)
insert into new table (RNumber,rating) values (2, #form.r2#)
insert into new table (RNumber,rating) values (3, #form.r3#)
insert into new table (RNumber,rating) values (4, #form.r4#)
...
insert into new table (RNumber,rating) values (150, #form.r150#)


r937.com | rudy.ca

 

[evergrean100]

Thanks,

I would also need to put in the person who rated the question in my insert and put it in a loop?

Is this in the right direction but not sure what the "someQuery" would look like or is this right??

<cfloop query="someQuery">
  insert into tblRocks (personID,RNumber,rating) values (#personID#,#RNumber#, #form.r150#)
</cfloop>