Enabling/disabling controls based on multiple records?

[Squircle]

Hi everyone, I'm new here, so please be patient if my question is trivial, or not very clear.


I have a table tblCC, which stores data about the chief complaint of patients when they go to the physician, as well as details about that complaint (ie. was fever present, did the patient have a cough, etc)

A patient can have more than one chief complaint per visit, so tblCC has a double primary key on the fields [VISITID] and [CCID].


The table tblICD9 contains all the possible complaints a person can have, as well as the type of complaint (respiratory, neurological, cardiovascular, etc). tblICD9 also has a double primary key, on fields [CCID] and [TYPE], because some complaints fall under more than one type.

For example, if the CCID for a stroke is "567", I would have two records

[CCID]     [TYPE]
567        NEUROLOGICAL
567        VASCULAR

I have a query that is based on both tables, called qryCC. I then have a form based on qryCC for data entry (I am only modifying the data in tblCC).

Here's the thing: some questions are only relevant to certain types of complaints. To make things easier for the interviewer, I want to be able to enable the relevant details fields for the type of chief complaint, and disable the others.

I know how to code this for one-to-one relationships, but I don't know how to handle the fact that a [CCID] is linked to more than one [TYPE].


A) Is my table structure correct for what I am trying to do (particularly the structure of tblICD9)

B) How do I enable/disable certain fields based on the [CCID]'s corresponding [TYPE]s, considering that some have only one record in tblICD9, but that others have many?

 

[georgedwilkinson]

Hi everyone, I'm new here, so please be patient if my question is trivial, or not very clear.

We're always patient here!

A) Is my table structure correct for what I am trying to do (particularly the structure of tblICD9)

Maybe, I lost focus about half way through the explanation (is there a CC for that?). It sounded good to that point.

B) How do I enable/disable certain fields based on the [CCID]'s corresponding [TYPE]s, considering that some have only one record in tblICD9, but that others have many?

I always use a design based (as opposed to analysis based) table to hold information about what should show up in combo boxes. It makes the query to populate the boxes much easier.

I must say I was a little concerned about a couple of things:
1. composite keys...a good thing. Making them the primary key can become problematic (I'm ready for the flames, go ahead). Go into the "indexes" design tool to make them "unique" and create an artificial (aka autonumber) primary key. This will facilitate use of design based table tools.
2. Storing/associating a CC to a type. This is at about the point I lost focus. I'm almost positive it needs to be done, though I don't know enough about the problem domain to say if the way you did it is the optimum way. Best way to tell is, does it work? Walk through all the scenarios of a user sitting at the keyboard recording this stuff. Then walk through the scenarios of someone else sitting at the keyboard trying to retrieve the data.

 

[The_Doc_Man]

I would create tables like this:

1. tblVisit, listing the individual visits. Foreign Key (FK) to patient. Contains date. If you have more than one doctor in the clinic, FK to doctor. I'm going to presume you have more than one doctor. This means that the visit table is a junction table between the doctor and the patient. The date of the visit is in this table. The visit has an autonumber prime key ONLY.

2. tblComplaints, listing each compliant. Autonumber PK (OR) if there is a standard set of symptom codes, they are suitable for the PK.

3. tblDoctor, listing who accepts visits. Autonumber PK (OR) if there is some medical society number or special healthcare professional number that is unique, such would be suitable.

4. tblPatient, listing who visits doctors. Autonumber PK.

5. tblVisCmpl, a junction table that has one entry per complaint noted on a visit.

So to find out what patient John Q Public complained to urologist Dr. I. P. Freely, you search tblVisit for John Q Public's ID and Dr. I. P. Freely's doctor ID. Then pick the date and search the VisCmpl table for the visit's ID.

This is one way (certainly not the only way) to normalize what you described.

 

[Squircle]

Thanks for the normalization pointers!

I decided to scrap having separate records for each complaint. Instead of having the "type" field, I just put in a yes/no field for each type (there were only 5 in the end) and will evaluate each one separately.

Thanks again! I can barely understand what I wrote before!