Help, too many fields for Access

[daver56]

I have problem. I need to enter into a database data collected on paper forms and at the end of the month I need to export that data to a fixed field length file and forward it to a state agency. The problem is that there are over 290 fields of information totaling over 400 characters of information. There is very little, if any reduntant information. Most of the fields are a yes or no type, or ask for "A" or "B" or "C", and etc. I have broken it into two tables so far but that is that not going to require two seperate forms to collect the data, I can live with that but I am looking for a better way to do this task. I was thinking if I could collect the y/n fields in one table and then store them in the main table as a large text field. For example if I have 50 questions that are y/n and I store them in a table and then export them to my primary table as a text type field with a length of 50. then Access would only look at this as one field and not 50.

The agency requires that the y/n text fields have the character "y" or "n". The file sent to them is strictly text only, and each field having a set number of characters or spaces.

Am I on the right track...

I am very open to suggestions.

 

[maxhugen]

There should be no problem establishing 2 tables to hold the data. You'll need a unique Primary Key in the 'first' table, which you probably already have. This could be an autonumber field, but doesn't need to be.

In the second table, define it's Primary Key as a field with the same name as the PK in the first table. However, don't make it an Autonumber! Create a one-to-one relationship between the 2 tables. You'll probably want to set the relationship to Cascade Updates and Deletes.

If you can fit all the questions onto one form - which it sounds like you could do - create a main form plus a subform. In the main form, show all the fields from table1 only. In the subform, which must be linked to the main form using the PK field, add all the fields from table2. You will need to set the subform's PK field to Default to the PK field on the main form.

If it's designed OK, you should be able to continue from the last field on the main form to the first field of the subform and finish entering the rest of the data.

HTH

Max Hugen

http://www.hugen.com.au

Australia

 

[JoeAtWork]

I have an idea that you may want to develop. I get the impression that this is some sort of paper form that you are trying to transcribe to a database. A form is basically a set of questions with corresponding answers, right?

So instead of thinking of every question as being a field in a table, perhaps you can think of it as a record in a questions table. Consider the following tables:

Table_Questions
QuestionID
Question

Table_Form
FormID
DateCreated
FullName

Table_Form_Answers
FormID
QuestionID
Answer

Table_Form is a kind of header record, Table_Form_Answers are the answers to each question for one filled-in form. I'm not sure how to display the questions to your users, perhaps some sort of outer join between Table_Questions and Table_Form_Answers. If I had time to play with it, I could probably figure it out.

The nice thing about this design is if you ever need to add more questions, you just need to add more records to Table_Questions, rather than add fields to a table.

 

[maxhugen]

G'day JoeAtWork!

MUCH better solution than mine!

On re-reading daver56's question, I see that a text entry is required for Y/N fields as well, so providing that ALL answers can be text - which looks the case - your solution is the way to go.

Cheers

Max Hugen

http://www.hugen.com.au

Australia