Re: Multiple If Then statements

Tech-Archive recommends: Speed Up your PC by fixing your registry

I'd try and do an sql statement, which matches he medication against the
allergy. if it returns more than one record you know you've got a 'hit'

That way you don't have to hassle over all the permutations of the data to
match & test



"uroron via AccessMonster.com" <u37161@uwe> wrote in message
news:78ad24bea9688@xxxxxx
Thank you very much for your well thought and cogent response.
I apologize for the senario that I presented. Actually, the database that
I
have constructed has many tables and relationships and is very similiar
layout to that which you describe.
I presented the simplistic (and ridiculous - in a design sense ) senario
more
as a programming question than a design question.
In the tables which you describe, the question would be:
How do you have a message box Caution pop up when an entry in the 'MedID"
table for a particular patient is the same as one listed in his
"AllergyID"
table?

Thank you very much
Ron

John W. Vinson wrote:
I am using Access 2007
I have a Main form and a subform.
[quoted text clipped - 14 lines]
Thank you in advance
Ron

It sounds like you're making a very common beginner's mistake: going to
Forms
design too early.

Date is NOT stored in forms. It's stored in Tables. A Form is *just a
window*,
a tool to manage data stored in tables. Where in your tables is this
information stored?

My guess is that you modeled your tables after your forms (which were
probably
in turn modeled after paper forms). This pretty much guarantees a flawed,
non-normalized design!

If you have ten Allergies fields... your table IS WRONG.
If a patient is limited to nine medicines because you have nine Meds
fields...
Your table is WRONG.

You need two many to many relationships here, using *five* tables, not
one:

Patients
PatientID
LastName
FirstName
<other appropriate biographical data>

Note: *nothing* in this table about either meds or allergies!!!

Allergies
AllergyID <perhaps a standard medical industry code>
Allergen <e.g. "cephalosporins">

PatientAllergies <a table linking Patients to their Allergies>
PatientID <link to Patients, who is allergic...>
AllergyID <link to Allergies, what they're allergic to>
<other fields about this patient's allergy to this agent, e.g.
severity,
date of onset, etc.>

Medications
MedID <Merck Index or insurance company unique ID>
MedicationName <other general info about this medication>

PatientMeds
PatientID <who's taking it>
MedID <what they're taking>

You will surely need other tables as well... but I'll be surprised if any
of
them have more than eight or ten fields.

Do note that any database containing identifiable patient information is
subject to the very stringent HIPAA patient-privacy regulations. You can
get
in *real* trouble if you're not careful with these. Some would argue that
ACCESS is incapable of providing the security required under the law.

John W. Vinson [MVP]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200709/1



.

Quantcast