Enter a unique code based on the answers to four variables

[David K]

Each kind of medical care we offer is specified by a unique 5-number code
(e.g. 99215). The care (and codes) are defined by four variables. These are
Patient Type (6 kinds), History (5 levels), Exam (5 levels) and Diagnosis (5
levels).

Users of the form don't understand the process. They must indicate which
variables apply to their patient encounter. The form should determine the
proper code, and enter it in the proper field.

 

[mscertified]

what is your question?
You need a kind of wizard that leads the form completer thru each step. It
looks like some of your information is dependent on other data entered (e.g.
diagnosis is determined by exam) so you need a dynamic process.
The first step is to design the tables that will hold the data for the form
choices.

-Dorian

 

[Pat Hartman]

Create a table that contains the PatientType, History, Exam, and Diagnosis
codes with the corresponding code you are looking for. You will have 6 x 5
x 5 x 3 rows = 450 in all.

You can then include that lookup table in your form's RecordSource query.
When the fourth code is filled in, the lookup value will automagically
appear.

 

[David K]

Question is how to do this. To clarify: The physician has to define the
history and exam levels based on the categories of questions they asked and
examined. They define the diagnosis level based on their perception of its
complexity, not necessarily linked to the level of history or exam (it truly
is an independent variable). I made tables for each variable, assigning
numerical values, and created fields in the table that the main form serves.
I also made option boxes with radio buttons that let the user generate the
numerical values in the table. So once the form is complete, the table has
all four variables defined. But how do I get these to:
- specify one of the 5-digit codes
- enter the code in the appropriate field

 

[Jeff Boyce]

Was there a question in there somewhere?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[David K]

Am I correct that this table will be two columns and 450 rows? If so, the
left must be each possible combination of the four variables, and the right
must be the corresponding 5-digit code. I understand how a lookup can go to
the correct 'definition' in the left column, but what assembles the four
variables into one lookup?

I guess if "Consult" is a "1" and the complexity of the other three features
on a given visit were 4, 5 and 5, then I could generate a unique number by
assinging a multiple of 1000 to the patient type, 100x to the History, 10x to
the Exam, etc. If these were added, I'd have a unique combination (1455 in
this case). Is that done with a macro?

 

[mscertified]

I don't know your application nor the business rules guiding it but the
standard way to do such things is to display a selection list that the user
chooses from. You force entry by validating the input and disallowing an
invalid update. It sounds like this may be a training issue rather than an
application issue. People do have to be trained on how to use an application.
All applications should also have a user manual.

-Dorian

 

[Pat Hartman]

No, the table has 5 columns. The first four columns are PatientType,
History, Exam, and Diagnosis. The fifth is the answer you are looking for.
The primary key of this table will be a composite one that includes the
first four fields. To make a compound primary key (Access allows up to 10
columns), use cntl-Click to highlight each key field. When the four are
highlighted, press the key icon on the tool bar. That tells Access that the
combination of these four values must be unique.

 

[David K]

I did that, but now how do I make the look-up work? In Table Design layout,
I can only direct lookup to one field. In Relationship Builder I can link
more than one, but isn't this a two way thing? I'm putting the PatientType,
History, Exam and Diagnosis in the "many" form, and asking for the Code to be
selected/sent from the "one" form. I tried to build that in Relationship
Builder, but I get an error having to do with invalid definitions.

Is a "junction form" needed? Even there, I don't understand how the
relationships work (or how to build them).

 

[Pat Hartman]

The only thing ever "sent" from the parent form to the child form is the
parent's primary key so that it can be stored in the child table where it is
referred to as a foreign key. This linkage is taken care of by the
master/child links on the subform control so as long as the links are
properly set, no coding is required to set the foreign key value.

to get the lookup value, you would join the lookup table to the main table
on the four lookup fields. In the form, after you have selected the
necessary four values, the lookup field will automagically populate.

Nothing I have described requires any code at all.

 

[David K]

For clarity, let me say I have two tables. "Main" has everything about
patients, and continually adds new encounters with same or new patients.
"Pick" is a constant list of codes. There are a couple hundred combinations
of four parameters, and more than one combination specifies the same code,
but only one code for each unique combination.

- Is the 'parent' table "Main"? Is the 'child' table "Pick"?
-- Assuming these are 'yes', do I make these links in Relationship
Builder? Or is the link merely between forms?

- If the link is between tables:
-- In the Relationship Builder, I can build links between tables, but
apparently not between forms. I can make four parameters of "Pick" into one
Compound Primary Key. And I can make one field in "Main" into a key.
--- How do I link the four with the one?
--- Am I supposed to make yet another key (in "Pick") that links to a
Foreign Key in the Main table?

If the links are between forms:
-- I opened a form built to enter most of the data into the main table.
Then I started making a sub-form. In that sub-form I can fit three
parameters, but not four.
-- In this attempt at a sub-form, when I enter the parameter data (only 3
of the 4 fields will fit) I get a portion of "Pick" that displays all the
remaining possibilities for the code. I can then copy the code into the
appropriate field, but I guess it won't pop into "Main" until I write it
there. (or is that just because I haven't narrowed the possibilities to one).

 

[Pat Hartman]

My preference is to always enforce referential integrity so I always create
relationships between tables using the relationship window. If the primary
key of a table is comprised of 4 fields, then any foreign key that links to
that table must also include all four fields and show four join lines in the
relationship window.

If you define relationships using the relationship window, Access uses that
information to make assumptions in other places. For example - the
master/child links. If you have not specifically defined a relationship in
the relationship window, you will most likely have to manually set the
master/child links between mainforms and their subforms. Be sure to include
ALL the necessary fields. If the FK is four fields, you will need all four
to make the correct link.

I'm a little confused by the last question. If it takes four fields to
define the fifth, how can you have only three in the subform? If space is
an issue, you can create a pop-up form. The pop-up form is not a subform so
you don't have the benefit of master/child links. That means that when
using a pop-up form, you must include code to populate the FK yourself or in
this case, I think you want to "push" the value back to the main form.

So the AfterUpdate event of the popup would ensure that all four fields were
filled so that the "answer" was found. it would then place the "answer"
back in the original form:

Forms!originalform!answerfield = me.answerfield