too many fields on import, or too few brain cells?

[FrankieNap]

I am a self-taught novice so please bear with me:

I am a teacher and have created a scantron form using Adobe Live
Cycle. The form uses radio-button groups for students to enter their
answers into. I export the data as an .xml file and then try to
import the data into access which will create a new record with the
student's name, date, and test ID, whereupon I will have some
calculated fields which will generate the student's test score. My
problem:

There are just over 300 fields on the adobe form. The access limit is
255, which makes it impossible to import all of the data. My question
is two-fold, should I:

a) increase the maximum field size in the registry to make it that I
could add the roughly 50 fields to make the import work or,

b) - and this is the more tricky - the student never needs to fill in
every field on the scantron. The test sections are numbered 2-10,
with 40 multiple choice radio-buttons per section, but no single test
uses all roughly 300 fields. I could just import the necessary fields
per test, but each test differes. For example, I have numbered the
fields rb001-rb320. One test may make use of rb001-24, rb0041-052,
etc., while another test uses rb001-rb022, rb0041-rb067, etc.

I'm pretty sure that the solution for scenario b) would be better
because it would be less redundant, but it also sounds like it would
be more complicated to import only certain fields based on a test ID
into a new record.
Any advice?

thank you.

 

[John W. Vinson]

I am a self-taught novice so please bear with me:

I am a teacher and have created a scantron form using Adobe Live
Cycle. The form uses radio-button groups for students to enter their
answers into. I export the data as an .xml file and then try to
import the data into access which will create a new record with the
student's name, date, and test ID, whereupon I will have some
calculated fields which will generate the student's test score. My
problem:

There are just over 300 fields on the adobe form. The access limit is
255, which makes it impossible to import all of the data. My question
is two-fold, should I:

a) increase the maximum field size in the registry to make it that I
could add the roughly 50 fields to make the import work or,

b) - and this is the more tricky - the student never needs to fill in
every field on the scantron. The test sections are numbered 2-10,
with 40 multiple choice radio-buttons per section, but no single test
uses all roughly 300 fields. I could just import the necessary fields
per test, but each test differes. For example, I have numbered the
fields rb001-rb320. One test may make use of rb001-24, rb0041-052,
etc., while another test uses rb001-rb022, rb0041-rb067, etc.

I'm pretty sure that the solution for scenario b) would be better
because it would be less redundant, but it also sounds like it would
be more complicated to import only certain fields based on a test ID
into a new record.
Any advice?

thank you.

Well... you're in a hard place here.

An Access Form isn't a data storage medium; and an Access table cannot -
absolutely cannot, no registry hacks exist - store over 255 fields. In reality
an Access table with 50 fields is HUGE; with properly normalized data very few
tables will ever need over 15 or 20 fields. I'm sure your 300 fields contain
many repeating groups, which in Access would be stored as multiple *records*
in a related table (for a questionnaire, you would have a table of Questions
with one record per question and a table of Answers related one-to-many to the
table of questions, with fields for the question number, the identity of the
person answering, and the answer).

My guess would be that you could use the scantron form; access it
programmatically using VBA code, and extract one answer at a time to populate
a set of properly normalized tables.

John W. Vinson [MVP]