Difficulty with form design

[etter34]

I'm trying to develop a form and I'm not quite sure what is the best
way to proceed.

Any guidance / help would be appreciated.

I'm working with diagnoses and problems and need to track this
information based on a case by case basis (patients). The problem is
that cases / patients have multiple diagnoses and problems that need to
be inputted into a table linked to the main patient table.

What I thought of doing was having a subform with multiple comboboxes.
The first combobox would list broad caterogies of diagnoses (ie:
trauma, toxicity, metabolic, etc.); the next combobox would contain
more specific details within those headings. So if one chose trauma in
the first combobox, the second combobox would populate with items like
"fracture, laceration, luxation, etc." And the user could select them.
However, diagnoses can include multiple problems from the broad
heading.

Example:
Patient 1:
Trauma: Fracture, Laceration
Infectious Disease: Abcess, Sepsis

Patient 2:
Degenerative: Cataracts
Metabolic: Starvation

So that now when I change the first combobox, the second combobox
reacts accordingly and I lose the data.


I'm looking to record each problem in (probably multiple) fields so
that I can later run queries using either the broad category, or the
more specific one.

THe two combobox example might not be the best idea, so thats why I am
looking for help. I thought about making the second combobox a list
that could be used to populate another list that would save all of the
problems to the patients record.

I'm not sure how else to explain this without showing the database.

 

[kingston via AccessMonster.com]

I suggest that you start by carefully examining and possibly restructuring
your data tables. Your database is probably much larger than what you
describe, but I think that you'll be able to better construct your forms and
your entire application if you have a clearer idea of how you want your data
to flow. Here are some suggestions:

tblPatient: PatientID, Name, Address,...
tblDoctor: DoctorID, Name, Specialty,...
tblDiagnoses: DiagID, Diagnosis, Description,...
tblProblem: ProbID, Problem, Description,...
Those are the tables where you store basic information as independent data.

You may want a table for possible combinations of Diagnoses and Problems so
that you can implement a cascading selection in your form. This may help
prevent incorrect/impossible records, but it could also be too restrictive in
that you won't be able to come up with all combinations initially and users
will have to have a way to create them. So I suggest you think hard about
whether to implement this.
tblDiaProb: DiagID, ProbID, Comment,...

Now here is where the real work begins. You want to have tables to capture
historical information, and this data must be related to the basic data
tables. Here is an example with a two-tiered approach. However, you'll have
to come up with something that will work for your level of complexity.
tblVisit: VisitID, PatientID, DoctorID, Date, Comment,...
tblVisitDetail: VisitID, DiagID, ProbID, Comment,...
This way, each visit may result in multiple detail records (diagnoses,
problems, resolutions, etc.).

The flow of your forms would follow this structure to a large degree. For
example, when a patient came in or called, you'd create a record in tblVisit.
If that visit resulted in a diagnosis, a detail record could be added via a
subform. HTH.

 

[ScubaSteve]

Excellent reasoning and I'm glad I think I'm on the right track there!

The pertinent tables I have are: (dealing w/ exotic animal / wildlife)
Case: caseid, finder name, address, datein
Patient: caseid w/ patient id (in case of multiple animals) species,
age, disposition, location
found.
Diagnosis: DiagnosisID, DiagnosisName, Description, DifferentialID
Differential: DifferentialID, DifferentialName

So would it be best in the patient form to have multiple sets of
comboboxes since the diagnosis would be dependent on the differential?

IE.
PatientForm (information in abstract)
Species, Age, Sex, DIsposition
cboDifferential1: cboDiagnosis1
cboDifferential2: cboDiagnosis2
cboDifferential3: cboDiagnosis3
cboDifferential4: cboDiagnosis4
etc.

That would take up alot of room in the form and might not always be
necessary. Some patients only have one diagnosis, others may have up
to 4 or even more! This is why I'm thinking there might be another
option for how to integrate this data. Previously there was a subform
with a drop down list of diagnosis (not linked to differentials) in
datasheet view.

I'm guessing I would also have to create a patientdiagnosis table that
integrates caseid, patientid, and the differential + diagnoses.

Thanks so much for your help and reasoning; I am very appreciative and
continually amazed at the help provided on this board.

 

[kingston via AccessMonster.com]

I agree that you should have a table for patient/diagnosis (basically what I
called tblVisitDetail). A subform would be used to display and record this
information for each visit. A summary could be provided for each patient for
all visits on the patient form the way you describe. If you don't need
tblVisit, you can move PatientID and other data points into tblVisitDetail.
It depends on the data that you want to record and how the information is
related.

 

[ScubaSteve]

Can you recommend an alternative to the multitude of combo boxes if
there is one for this type of data entry?

Thanks!

 

[kingston via AccessMonster.com]

You'd have two comboboxes and multiple records. So the subform would have as
many records as needed and each record would have two comboboxes.