Question Too many fields

[sneuberg]

I think you should consider Galaxiom's suggestions. In addition to what he said you would want to create a table of condition categories such as Cancer so that you could group these like you currently do. Perhaps a field to put a number in so you could control the order they would appear in if alphabetical wouldn't work. But you can start with the medication which is will be easy to separate out as I've done most of the work for you in the attached database. In this you will find:

1. Conversion Module - I left the module Conversions which has the code I used to create the MedicationType Table and the FieldNames Table in case you want to see how it's done.
   
2. FieldNames Table - This is a table with a record for each of the field names in the table Question 1-3 Table. I included this in case you want to implement Galaxiom's suggestions it would give you a starting point and save you some typing.
   
3. MedicationType Table - This is a table of the medications and is used to populate the Medication Table. I suggest you import this into your database.
   
4. Medication Table - This is the vertical implementation of the Question 4-6 Table and replaces it. I suggestion you import this too into your database.
   
5. Medication Subform - This is a continuous form that has the Medication Table as it's record source. You could import it or make your own.

Now to see how this works please open the Medication Table in design view. You will see that this is structured so that each medication goes in a separate record which is identified by the combination of the Subject ID and the Medication Name. If you look a the indexes you will see I made those two fields the primary key. I think most of the other forum members will tell you to use a autonumber instead of a composite key. It's just an opinion I don't share.

Now please go to DATABASE TOOLS and look at the relationships. There you will see the one to many relationship between Question 1-3 Table and the Medication Table.

In this new design the Questionaire 4-6 Table and the Questionaire 4-6 Form are not use so please just ignore them.

The Questionaire 1-3 Form is still used. I had to make some minor changes to it which you will find described in the included word document. The major change to this form is the addition of the Page5 tab.

You will find a subform on that tab named Medication Subform. Within that subform you will find a formed named Medication Subform. If you are new to subforms the first thing you need to know is that a subform is not a form. It's a control that contains a form and links the main form with the form in the subform. Subforms and the forms they contain usually have the same name. When you add a subform to a form the wizard will ask you if you want to add a form and if you do it gives the subform the name of the form. Anyway if you want to look at the properties of the subform you need to click the outer edge of the form. When you see the yellow line around the form and the gray box on the upper left you know you have it selected. The first thing to observe in these properties is the Link Master Fields and Link Child Fields in the DATA tab. Note that these are the Subject ID. That's what glues the main form to the form in the subform. The wizard normally gets them right for you. On the Event tab I want you to click on the three dots ... on the far right of the On Enter event. You should see this code.

Private Sub Medication_Subform_Enter()

'if not all ready there empty records are added to the Medication Table
If DCount("*", "[Medication Table]", "[Subject ID] = '" & Me.Subject_ID & "'") = 0 Then
    CurrentDb.Execute "INSERT INTO [Medication Table] ( [Subject ID], MedicationName ) " & _
    "SELECT " & Me.Subject_ID & " AS Expr1, [MedicationType Table].MedName " & _
    "FROM [MedicationType Table];", dbFailOnError
End If
Me.Medication_Subform.Requery


End Sub

This code populates the Medication Table with the medications in the MedicationType Table. Normally this isn't done. The user would normally select the medication names one at a time from a combo box. In this case I assumed you want them all added so that it has the appearance similar to what you already had.

You will need to make a form to maintain the MedicationType Table but this is a good thing. With this design new medication can be added easily. In the old design you would have to add fields to the table and form to do this. Also when it comes time to make a report for this you will be really glad you changed your design to this.

If you have any problems implementing this design please let me know.

 

[The_Doc_Man]

The design of this database requires knowledge of normalization.

You have a responder entry with a unique responder ID, ONE RESPONDER PER RECORD.

You have a question table with a unique question ID, ONE QUESTION PER RECORD.

Now look up the topic of JUNCTION TABLES.

You can make a form that is bound to the questions in some way, perhaps, but what I would do is this:

Make the form bound to the RESPONDER. Enter dates, questioner, etc. When you click a button, have the form open a recordset to the question table based on a query that asks the questions in the proper order. (This usually means that you design the questions so that if you visit them in QUESTIONID order, they are in the order you wanted them to be asked.)

Now for each question, you might have a list of possible answer types including YES/NO, an integer number, a date, a short text answer, select a choice from a limited list, etc. That has to be somehow conveyed to the responder so that you get good answers. Now for each question, capture the answer IN A TEXT FIELD. For each answer, store an entry in the junction table showing ResponderID, QuestionID, text answer, and anything else that ONLY applies to the responder and specific question. (For instance, you DO NOT store the type of answer - because that was already part of the question.)

The preferred format of the answer (from the Question table) can be used if you want to somehow pre-process the answer you stored for that responder. When storing the answer, I said use TEXT storage format because you want to keep this simple. You can preprocess answers to conform to a standard format and prevent completion of the answer storage if non-conformant (i.e. complain "Wrong format" and ask the question again.)

The point is that the way you are doing this is "flat-file" thinking and Access allows you to think in more than just the "flat" dimensionality of a spreadsheet. "Database Normalization" is the term you would use to describe this non-flat viewpoint. You might want to review that topic before proceeding with your database implementation.

 

[Leaden]

Thank you for all the suggestions. I will actually read up on normalization and stuff and come back if I still don't see what to do..

 

[jdraw]

There is an 8 part series of youtube videos by Dr. Daniel Soper that starts here.
Watch a few of these to get an appreciation of Normalization, Relational Database, Data Models....
It will help you with your project.

 

[Leaden]

Hello, I see you have put in a lot of work on this. Thank you.

First of all, when I try to make new record by clicking next arrow on the bottom, it gives me "runtime error 3061 too few parameters. expected 1.

Also, is your design supposed to have multiple subforms for every questions I have (~400)?

I somewhat understood your post, but it seems little bit hard haha

 

[Uncle Gizmo]

You might find this post useful:-

https://sites.google.com/site/msacc...aires/add-a-questionnaire-to-your-db---videos

 

[sneuberg]

Hello, I see you have put in a lot of work on this. Thank you.

First of all, when I try to make new record by clicking next arrow on the bottom, it gives me "runtime error 3061 too few parameters. expected 1.

The problem that my code didn't address is the the medication records can't be added until a Student ID is enter. The code needs that to add the records. So any action that would cause entry into the subform results in this error. I've added code to catch this problem. Change the code in the Questionaire 1-3 Form module to the code below and the should fix this problem. This code has been added to the attached revised database

Private Sub Medication_Subform_Enter()

If Len(Me.Subject_ID & vbNullString) = 0 Then
    MsgBox "You must enter a Respondent ID"
    Me.Subject_ID.SetFocus
    Exit Sub
End If

'if not all ready there empty records are added to the Medication Table
If DCount("*", "[Medication Table]", "[Subject ID] = '" & Me.Subject_ID & "'") = 0 Then
    CurrentDb.Execute "INSERT INTO [Medication Table] ( [Subject ID], MedicationName ) " & _
    "SELECT " & Me.Subject_ID & " AS Expr1, [MedicationType Table].MedName " & _
    "FROM [MedicationType Table];", dbFailOnError
End If
Me.Medication_Subform.Requery


End Sub

Also, is your design supposed to have multiple subforms for every questions I have (~400)?

I somewhat understood your post, but it seems little bit hard haha

No just this subform for the medications and another one for for the medical conditions if you implement Galaxiom's suggestion.