Question Too many fields

[Leaden]

Hello, I am new here (and to access). I would like to get some help on what I am working on right now...

I am currently making access database on a questionnaire that is very, very long, which needs more than 250 fields (questions).

So First I tried to put them all into one table, which did not work due to 250 fields restriction, so I tried making 2~3 tables (right now 2, might need 1 more table) and have 150~ fields each table and make one to one relationship to every tables for ParticipantID. I have done that, and made 1 form per table, and tried the data entry with the form, but it gives me the "you cannot add or change a record because a related record is required in table" error when I try to save (click the next record). I am not sure if this is small error or if there is fundamental problem with my design... any help would be very much appreciated

 

[Galaxiom]

Make each question a separate record in the same table.

This table could be related to the Respondent table which would be the RecordSource for the main form. The results would be displayed in subforms.

 

[Leaden]

I am not sure what you mean by separate record?

 

[sneuberg]

If you have already added all of those fields I suggest you upload the database. I can write some code to automate the process of getting it in the form suggested by Galaxiom and get it back to you.

 

[Leaden]

Here it is...

 

[Leaden]

Why is it giving me this "you cannot add or change a record because a related record is required in table" error?

 

[sneuberg]

Why is it giving me this "you cannot add or change a record because a related record is required in table" error?

You need to enter the same subject id in the next form. You probably want to have this copied over automatically. I've never done this so I can't advise you except to google "one to one relationships". You will certainly find some Youtube videos that will tell you what to you.

I hope to show you how you can avoid this by restructuring your data. But I need to study this for a bit to see what's possible. I'll try to get you something tomorrow morning.

 

[sneuberg]

Make each question a separate record in the same table.

This table could be related to the Respondent table which would be the RecordSource for the main form. The results would be displayed in subforms.

@Galaxiom Please take a look at his database. It doesn't look like all these questions should be in separate records. I not sure at the moment how this should be done. What do you think?

 

[Leaden]

Thank you. But I kind of need to get this working before I go to sleep tonight though... Well ugh...

But the thing is, when I just bluntly types in the same subject ID (such as 1234) into both forms, it still will give me "you cannot add or change a record because a related record is required in table" error..

 

[jdraw]

Leaden,

You need to get your tables and relationships designed to meet your requirements.
I'm not sure what your business is, but if you are dealing with Questions and Respondents,you will need a Table for Respondents, a table for Questions and probably a table for ResponsesByRespondent.
I may have guessed incorrectly at your requirement, but if so, we need a little more info.

As I see it, these could be your tables. The ---> represents 1 to Many

Questions------->ResponsesByRespondent<----Respondent


You have 2 tables in a 1 to 1 relationships???

Questionnaires1-3--------Questionnaires 4-6 [Please tell us what this represents]

Good luck with your project.

 

[Leaden]

I see what you mean.. So I need questions ----> responses by respondent <--- respondent.

however, same question still remains, as my questions are definitely more than 255 fields, which means I would need more than 1 table for the "questions" table. How do I link them?

Also, what I am trying to achieve here is basically just linking subjects (which has unique subject ID) with their question responses. (1 subject to 300~400 question answers)

 

[jdraw]

Take a step back, and tell us in plain English --6-7 lines
--what exactly are you trying to do (plain english no jargon)
--Give us a sample of the input and the expected output.

Readers do not know what you're trying to achieve, nor what the pieces are.

I can't remember seeing a well designed table with more than 40-50 fields, and even that is rare. So there is something to be gained by seeing your description.

 

[Leaden]

First, I will be manually interviewing participants with long questionnaire. Then, wit those responses I have got, I need to enter those data into the database I am creating right now. However, the problem is, the questionnaire is super long, so that I am running out of fields to have them in one table (255 limit I think?) So I tried to divide those 400~ fields into two tables and link them one on one... which seems like not working / bad design.

 

[sneuberg]

Take a step back, and tell us in plain English --6-7 lines
--what exactly are you trying to do (plain english no jargon)
--Give us a sample of the input and the expected output.

Readers do not know what you're trying to achieve, nor what the pieces are.

I can't remember seeing a well designed table with more than 40-50 fields, and even that is rare. So there is something to be gained by seeing your description.

I suggest taking a look at the database he posted. This is more than it seems.

 

[sneuberg]

I have a two suggestions that might get you moving ahead.

In relationships I'd allow cascaded updates and deletes.
Change the Data Entry property on both forms to No.

This way you you at least see the records that have been added and you will be able to add data in the second form if you enter a Subject ID if it is in the other table.

 

[Leaden]

could you elaborate for me? how would I make a use of the data that I have entered into the form if i turn off the data entry to no?

 

[sneuberg]

Right now when you open the forms all you are getting is a new record which is ok if you want separate forms for just adding records. With Data Entry set to Yes that's all you can do. If you set Data Entry to no you'll be able to scroll through the existing records and if you want to add a new record just click that in the record navigation below. I suggest you do this at least for now so that you can get things working. Try it, you'll like it.

 

[Leaden]

Oh wow. Yes I see what you mean now. I get to make some progress haha. Thank you very much!!

But one thing is, After I type in the subject ID into form 1-3, and then move onto next form (4-6), the subject ID section on that form is blank. Is there any way to link them so that it would automatically show up on it (as well as on 2nd table)?

Also, In order for the tables to be updated after I enter some data into the forms, I need to close all the tabs and then re-open the tables in order to see the changes. Is this normal?

Also, again, thank you very much!

 

[sneuberg]

But one thing is, After I type in the subject ID into form 1-3, and then move onto next form (4-6), the subject ID section on that form is blank. Is there any way to link them so that it would automatically show up on it (as well as on 2nd table)?!

You can pass stuff like that from one form to the next with OpenArgs https://msdn.microsoft.com/en-us/library/office/ff836583.aspx or tempvars https://msdn.microsoft.com/en-us/library/office/ff193475.aspx but it's near my bedtime so that's all I can give you today. Will check in tomorrow.

Gee, where did everybody else go? Must be thinking about this.

 

[Galaxiom]

The ordinary text based information for the respondent (Name Address, Phone etc)should be in one table. It will be displayed on the main form in the usual way.

The Conditions should be held one record per condition in a related table with fields for PatientID, ConditionID, ConditonPresent (Boolean) plus fields for the associated information. These will be displayed in a subform with the Master/Child LinkFields as PatientID. The subform should be set up as Continuous Forms.

ConditionalFormatting can be used enable the associated controls as required depending on the Condition.

When a new questionnaire is filled out, use code to generate the records in the related table from the information held in a Conditions table. Then Requery the subform.

It is quite a complex structure to set up properly but has huge advantages. There are other problems with the current structure aside from the problems you have already encountered with the limit to the number of field and the massive amount of work to build the forms.

Currently, adding one more condition requires the redevelopment of the Tables, Reports and Forms. The one condition per record structure only requires a new entry into the ConditionsMaster table. No redesigning anything so it can be done in Runtime by any appropriately empowered user.

Moreover this structure can be used to record multiple dated responses from the same patient for the same question so there is a historical record of changes.