Relationship question

[Spider]

Hi all,
I am having difficulty with a database I am creating for surveying physician practices for compliance with standards of care and payment incentives.

It seems simple enough – but I obviously have the relationships wrong since the form is misbehaving. I have tried several things over the last week.

The survey includes the physician practice site and date of the survey.
It also includes Standards, Elements and Factors.

Factors belong to Elements
Elements belong to Standards
A Survey has many Standards (6)

Example: The survey will include all 6 Standards; each Standard has several unique Elements. Each Element has several unique Factors.

Survey Date / Location
>Standard #1
>Element #1
>Factor #1
>Factor #2
>Factor #3

>Standard #2
>Element #2
>Factor #4
>Factor #5
>Factor #6

What I am trying to do is create the main form with the location and date,
A subform with the Standards
A sub- subform with the Elements
A sub-sub subform with the Factors

On the form, when choosing the Standard, I need the Elements that belong to that Standard to populate the dropdown box of Elements.
And when choosing the Element, the Factors that belong to that Element populate the dropdown for the Factors.

I appreciate any help with this. I’ve attached a stripped down version in case that is helpful.
Vicky

[HansV]

I'll start with a very short reply; we can go into details later on if necessary.

You have a many-to-many relationship between surveys and standards: each survey has multiple standards, and each standard occurs in multiple (in fact all) surveys.
To implement this relationship, you need three tables:

tblSurvey - like you have now.

tblStandards - contains generic information about standards, such as their names, but nothing about surveys. In particular, SurveyID is not included in this table.

tblSurveyStandards - contains fields SurveyID and StandardID, plus any fields necessary to describe the combination of a specific survey and standard. This table will contain a record for each survey-standard combination, so if each survey has 6 standards, this table will contain 6 records per SurveyID.

tblSurvey is linked to tblSurveyStandards by SurveyID.
tblStandards is linked to tblSurveyStandards by StandardID.
There is NO direct link between tblSurvey and tblStandards.

The main form will still be based on tblSurvey, but the first subform will be based on tblSurveyStandards or on a query based on tblSurveyStandards and tblStandards.

The next level (elements) will be linked to tblSurveyStandards, NOT directly to tblSurvey, but we'll come to that later. Please try to work out the survey/standard part first. Feel free to ask any questions.

[Spider]

Thank you so much for your time. I will work on this piece.

[Spider]

Okay - I believe I have the tblSurvey and tblSurveyStandards conquered and it makes sence to me now.

[HansV]

Great! Feel free to post back if you need help with the other levels.

[Spider]

I'm afraid I would like your advice on the other levels - if you could just point me in the right direction as before.

[HansV]

From your database, I get the impression that an element can belong to a single standard only. Is that correct, or could an element such as "Access after Hours" or "Manage Medications" belong to multiple standards?

[Spider]

Correct - an Element can belong to a single Standard only.

[HansV]

Here is a possible structure:

x823.png

I have attached the database with the modified table structure. Warning: I haven't modified any of the queries, forms or reports. Those won't work any more as they are now.

PCMH_modified.zip

[Spider]

Thank you so much - I will work on this...

[Spider]

I worked on this last night and this morning trying different variations of queries for the data entry form.
It will not lent me add the associated Factors to the Element.

Each of the 6 Standards has has the unique associated Elements which in turn - each Element has unique associated Factors.
I get an error that an index or primary key cannot be null.

Ideally, I would like to pick a Standard which triggers the associated Elements to be available in the Element subform combo box. Then when picking the Element, it triggers the next subform combobox to populate the Factors associated with the Element.

[HansV]

I'll try to get back to you later on.

[HansV]

See the attached version; I edited the forms, their record sources and the links between them.
I added a bit of code to update the combo boxes when necessary.

PCMH_modified.zip

[Spider]

Thank you again!
I will look these over to understand what you did.
I really appreciate your help.
Vicky