255+ Fields in One Form

[Compliance]

Hello,

I am not new to Access, but I am new to developing forms that are very large.

I am making a form that has 4 tabs. 3 of the tabs only have about 60 fields total, while the 4th tab is a very large checklist with 126 questions that the user must select Y or N, so I have 252 check-boxes. I have all the fields for the first 3 tabs in 1 table and the checklist tab fields are in a 2nd table.

In the past I would just use a query in order to use two tables in one form, but I am still limited to 255 per query.

Does anyone have an idea on how to handle such a large form?



One idea I tried to run by my client was cut the number of check-boxes in half by have the user only select Y if applicable, but they need to document that the user reviewed both options and selected the appropriate one and didn't skip it.

[Compliance]

can I query a query?

[JoeM]

The first thing I would question is the normalization of your database design (see: http://www.deeptraining.com/litwin/d...aseDesign.aspx). Really, the underlying table should not have 126 or 252 different fields, but much less (3 or 4), designed with fields like:
- QuestionNumber (1-126)
- QuestionValue
as well as some other identifying fields.

And you can easily cut your response fields in half by using a ComboBox with acceptable values of something like "Yes", "No", and "Incomplete".
You could then default the value to "Incomplete". So all the values will be "Incomplete" until they select "Yes" or "No".

The advantage to design the table according to the rules of normalization like I suggested is if you wanted to search your entries for all "Incomplete" entries, you only have to search one field instead of 126!

However

[Compliance]

Joe

Thank you for your response, and that is definitely the type of help I am looking for.

My question now is how does my QuestionValue get tied to my QuestionNumber

that is, how do I avoid having QN1, QV1, QN2, QN2...

[Compliance]

I'm going to try making a new table with ChecklistNumber as the Primary Key and relationship to the field Checklist in the main table

[Compliance]

OK

So I have huge question before I continue down this path.

In my main table, let's call it Management, I have a field called Checklist that is Number field with a relationship to my Checklist table's Primary Key called Checklist Number.

When the user selections "Yes, No, or Icomplete" for the QuestionValue it will be saved in my Checklist table, correct? Let's say for record 1 I select Yes.

Well when I have a new record in the Management table, record 2, and I select No. Will this not now set the value of QuestionValue to No and update record 1 to a No?

[JoeM]

Back in post #3, I was focusing on the fields relating to your questions. You will want some sort of record ID in that table also, relating to the person(?) that particular record is applied to.

So a simple example might look something like this:

Person ID       Question Number     Question Value
Bob                       1                Yes
Bob                       2                No
...
Bob                     126                Yes
Carol                     1                No
Carol                     2                No
...
Carol                   126                Yes

...
So, you would have 126 records in this table for EACH person.

[Compliance]

Back in post #3, I was focusing on the fields relating to your questions. You will want some sort of record ID in that table also, relating to the person(?) that particular record is applied to.

So a simple example might look something like this...
So, you would have 126 records in this table for EACH person.

So not only 126 records for EACH person, but 126 records for each person per each master record