Recordsource issue

[SaviorSix]

Hello

I have a database set up that is used for data entry. There are about 30 or so tables, and a seperate form for each table. Each form's recordsource corresponds to its unique table.

Some of these tables/forms are the same in design, but the data being entered into each is different, as they correspond to points in time.

for example:

there is a "Short Form 36" which data is recorded to for different points in time. Each timepoint has its own table and form:

tbl_SF36_wk00 frm_SF36_wk00
tbl_SF36_Mnth03 frm_SF36_Mnth03
tbl_SF36_Mnth06 frm_SF36_Mnth06

The user who is entering data reports errors such as the following:

when she enters data into, say , frm_SF36_wk00, the data should be recorded into tbl_SF36_wk00. Right?..... Not in this case! And the recordsource property POINTS EXACTLY TO THE CORRECT TABLE!
I have double, triple, quadruple checked this

The data went into tbl_SF36_Mnth03 instead!

Likewise, the data entered into frm_SF36_Mnth03, which should have gone to tbl_SF36_Mnth03, went into tbl_SF36_Mnth06 instead.

Once again, every single recordsource propery for every form is pointing to the exact table it should be. However these errors are still happening.
Am I going insane?

 

[boblarson]

Am I going insane?

Based on the design of the database, I would say so...(sorry, it's just that this is in no way normalized data and therefore what you are trying to do flies against all that a relational database is for).

I seriously think you may need to rethink your design (with the forum to help, of course) as you should not have repeating tables, let alone repeating fields:

http://support.microsoft.com/kb/283878

 

[SaviorSix]

Thanks for the reply. I know that the way it is designed is not the best, however I was stumped to find a way to accomplish what was needed and meet all normalization forms.

Yes, there are groups of tables that are the same HOWEVER the data within them is different for each table. This is because the study results are entered at different time points, each time point having its seperate form (due to data results being different at each)

Thinking about it now, it must be my relationships that are screwy.

 

[CraigDolphin]

Yes, there are groups of tables that are the same HOWEVER the data within them is different for each table.

Which indicates to me that you need one table with an additional field to indicate the type of record...

Perhaps if you would describe the real world situation that you're trying to model we could help you to improve and normalize your data model. It might take some back-and-forth to flesh it out but you'd at leats not be fighting to place a square peg in a round hole.

 

[SaviorSix]

Which indicates to me that you need one table with an additional field to indicate the type of record...

Perhaps if you would describe the real world situation that you're trying to model we could help you to improve and normalize your data model. It might take some back-and-forth to flesh it out but you'd at leats not be fighting to place a square peg in a round hole.

OK

There are 9 seperate questionnaires to fill out for each period in time. There are 10 points in time (designated by weeks and months)

Week 1
Questionnaire 1
Questionnaire 2
Questionnaire 3
....etc up to 9

Week 2
Questionnaire 1
Questionnaire 2
Questionnaire 3
....etc up to 9

Week3
Questionnaire 1
Questionnaire 2
Questionnaire 3
....etc up to 9

...and this continues for the remaining points in time.

 

[CraigDolphin]

So to paraphrase what I've read and inferred so far (correct me if I'm off base please).

-There are many people who must be tracked.
-A questionaire contains multiple questions.
-There are 9 questionaires given to a person on a single occasion.
-People each fill in the same 9 questionaires on 10 different occasions.
-All people answer the same group of 9 questionaires.

Some further questions:
Are the questions multiple-choice, free-form answer, rankings, or some combination?
What is the basis for determining WHEN the group of 9 questionaires are filled in?

It might help if you also explained a bit of the wider context for your surveys. For example, if these are patient responses to clinical trials for pharmaceuticals or political opnion polling or whatever. Sometimes it helps to understand the wider context because it helps us ask more intelligent questions about the real world data model.

For example, if this were a battery of questionaires to determine side effects of a new drug, I would then expect that you might need to track results by drug name. That infers another table to track drugs etc.

The better you explain your real world situation the better we can help. Obviously brand names or company names are not germane, but the real world scenario is critical to a good db design.

 

[SaviorSix]

So to paraphrase what I've read and inferred so far (correct me if I'm off base please).

-There are many people who must be tracked.
-A questionaire contains multiple questions.
-There are 9 questionaires given to a person on a single occasion.
-People each fill in the same 9 questionaires on 10 different occasions.
-All people answer the same group of 9 questionaires.

Some further questions:
Are the questions multiple-choice, free-form answer, rankings, or some combination?
What is the basis for determining WHEN the group of 9 questionaires are filled in?

It might help if you also explained a bit of the wider context for your surveys. For example, if these are patient responses to clinical trials for pharmaceuticals or political opnion polling or whatever. Sometimes it helps to understand the wider context because it helps us ask more intelligent questions about the real world data model.

For example, if this were a battery of questionaires to determine side effects of a new drug, I would then expect that you might need to track results by drug name. That infers another table to track drugs etc.

The better you explain your real world situation the better we can help. Obviously brand names or company names are not germane, but the real world scenario is critical to a good db design.

You are correct in your initial inferrences

These are medical surveys conducted over time. A researcher takes the paper surveys, and then inputs them into the corresponding form which represents that particular survey at that particular time.

Each patient has an ID number(no specific identifying information besides this arbitrary number), which is entered into each survey form.

The questions are a combination of types on each of the surveys; lookups, text boxes, and checkboxes.

When I first designed it, I created a "template" table and form for each survey, and then copied and pasted them, renaming them appropriately for each point in time, and changing the record sources appropriately.
Im thinking doing that may have been the start of the problem.

 

[CraigDolphin]

These are medical surveys conducted over time

Ok...so...there are 10 occasions during the survey when the group of 9 questionaires are filled out. Are these always answered in a predictable time sequence, or can they vary in their temporal sequence?

For example,
Occasion 1= start date
Occasion 2 = exactly 2 weeks after start date
Occasion 3 = exactly 4 weeks after start date
Occasion 4 = exactly 6 weeks after start date
Occasion 5 = exactly 6 months after start date
Occasion 6 = exactly 1 year after start date
etc

Or does the timing for each occasion vary depending on the patient?
If so, are you interested in tracking actual length of time between occasions and the initiation of the survey (as well as sequence/order)? Or do you only care about the sequence/order that the questionaires were filled in not actual time between occasions?

And I assume that each patient will have their own 'schedule' for taking the questionaires, but if all patients share the same schedule then you should correct my assumption. Which would then lead to my asking if you want to track responses for more than one 'batch' of patients...

Also do you want/need to track entities such as doctors/hospitals/insurance companies/pharmaceuticals/specific treatments (like a surgical procedure) etc to use for later analysis?

Im thinking doing that may have been the start of the problem.

I think you're right If you search this forum for 'survey databases' or similar synonyms you'll find that you're not the first to head down that path.

Generally such a db should recognise entities like:

tblSurveys (a named collection of questions...equivalent to your nine different questionaires)
-SurveyID (auto,pk)
-SurveyName

tblQuestions (questions that could be asked)
-QuestionID (auto, pk)
-QuestionText (text)

tblSurveyQuestions (a list of questions that will be asked by a specific survey)
-SurveyQuestionID (auto, pk)
-SurveyID (FK)
-QuestionID (FK)

tblAnswers (a list of pre-defined answers to questions that could be used to populate a combo or listbox control on a form)
-AnswerID(auto,pk)
-AnswerText (text)

tblRespondents (list of people taking surveys)
-RespondentID (pk) ...equiv to unique patientID

tblRespondentSurveyDates (list of which survey was taken by whom, on which date)
-RespondentSurveyDateID (auto, pk)
-RespondentID (FK)
-RespondentSurveyDate

tblQuestionAnswers (Actual answers to questions)
-QuestionAnswerID (auto, pk)
-RespondentSurveyDateID (FK)
-QuestionID (FK)
-ActualAnswer (text)

The key to making this structure work is intelligent use of forms and subforms so that the fk fields get automatically filled in (without the user even knowing they're there).

So you'd likely have a main form bound to tblRespondentSurveyDates which the user would have to select the surveyid and respondentid from a combo, and enter the date the survey was taken.

You'd then have a subform in continuous form view that is bound to tblQuestionAnswers with a master-child realtionship to the main form based on the RespondentSurveyDateID fields of both tables.

On that subform you'd have a hidden textbox control bound to the RespondentSurveyDateID fk field, a combo box for QuestionID (the row source of which looks up the relevant questionID's from tblSurveyQuestions using the SurveyID in the mainform as a parameter, but which DISPLAYS the actual question text to make it human readable), and a combobox bound to the ActualAnswer field that looks up the predefined answers for that question but which also allows the user to free-enter text that is not on the list.

You may find that you want to have a bound textbox and an unbound combobox to list pre-defined answers. This allows you to use the combo to populate the textbox control if the user selects from the list. When you move to a new record, the answer on other records then won't 'vanish' as the rowsource of the combo is requeried.

You'd also need to create forms that allow you to create new surveys (with subforms that allow you to specify which questions need to be linked to that survey) as well as to add new questions to the db, and to input pre-defined answers to questions.

Ok...sorry for the long post but hopefully you can begin to see the difference in approach here and how it allows for much greater flexibility. For example, what if, for some reason, unbeknownst now to God or man, someone filled in the 9 surveys on 11 occasions? This structure handles that very easily.

To analyze the data, you simply query however you need. To get a list of surveys by patient, in date order, you simply query the relevant fields in the relevant tables and sort by date. Your queries become much simpler and more powerful.

Hope it helps provide food for thought.

 

[boblarson]

Definitely listen to Craig. He knows his stuff when it comes to normalized design.

 

[SaviorSix]

Thanks for taking the time to give these detailed reponses!

The way you layed out the tables above definitely gave me a new route to think along on how it SHOULD be structured.

I am going to go back and try to resolve this, again thank you for the help

 

[georgedwilkinson]

Definitely listen to Craig. He knows his stuff when it comes to normalized design.

And patient. I'm not sure my ADD would allow me to get into that much detail without the poster volunteering some of the information without having to pull teeth.


By the way, seems medical companies are ramping up on this kind of survey, another poster has the exact same problem:
http://www.access-programmers.co.uk/forums/showthread.php?t=147662

Coinkidink? Probably not.

 

[CraigDolphin]

Thanks for the vote of confidence Bob.

SaviourSix, glad I was able to point you in the right direction. You should also search the forum/google for survey databases to see other examples/discussions about the topic.

Coinkidink? Probably not.

Hmm. Well, if it is a class assignment I'll be grumpy. (Actually, some would say I'm USUALLY grumpy these days...)

 

[SaviorSix]

just so you know....it is a coincidence

 

[CraigDolphin]

Good to know