Max amount fields in one table

[evergreean43]

I am creating an Access 2000 database with a Web Front end that will have around 150 questions with 148 questions having just one number input (which will either be a 1 or a 2 or a 3 or a 4 or a 5). All 148 questions will be short sentences such as:

Question                                   Choose One Number Rating
1-How would you rate Red Rock1?                  1   2   3   4   5
2-How would you rate Red Rock2?                  1   2   3   4   5
3-How would you rate Red Rock3?                  1   2   3   4   5
4-How would you rate Blue Rock1?                 1   2   3   4   5
5-How would you rate Blue Rock2?                 1   2   3   4   5
6-How would you rate Blue Rock3?                 1   2   3   4   5
etc...

The 6 above examples show just 6 (redRock1, redRock2, redRock3, blueRock1, blueRock2, blueRock3) out of the 148 fields.

The other two fields will have a 100 character input and 250 character max input.

There will be around 800 inserts into this questionaire survey.

Please advise what is maximum field allowed per table for Access and can it handle what I want to do?

 

[dhookom]

I would figure out how to use a normalized table structure like found in [blue]At Your Survey[/blue] at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

An Access table can contain 255 fields but this is generally considered about 225 too many for most applications.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Golom]

As Duane says ... long is usually better than wide ... and you definitely should check out his link where he has developed a robust and fully functional questionairre application.

Generally table structures like
[tt]tblQuestions
StudentID QNumber Response
Joe 1 Yes
Joe 2 No
Joe 3 Only on Wednesday
etc.
[/tt]
are much better than

[tt]tblQuestions
StudentID Q1 Q2 Q3 ....
Joe Yes No Only on Wednesday
[/tt]

 

[evergrean100]

Thanks it looks great the longer way but how do I do my inserts?

Currently I am using the wide way where I have a simple insert:

insert into tblQuestions (studentid,q1,q2,q3)
values (form.studentid, form.q1, form.q2, form.q3);

How would the insert statement look using the long way so I can get my table to look like this:

StudentID   QNumber      Response
  Joe          1            Yes
  Joe          2            No
  Joe          3            Only on Wednesday
 etc.

 

[dhookom]

You insert would depend on your web application. You would need to have code to loop through your form <input>s and create multiple inserts. You would have a better chance getting a specific answer to this in a web programming forum.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[evergrean100]

Thanks!