Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Oklahoma City, Oklahoma, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Qry table to find records listed in other table (A2K XP)

    I want to list in tables a set of parameters, (medications), and have the query look in the patient's medication table to see if there is a match. I just need to know it the person is taking one of the medications in the list and if so flag the person.

    I can do this from the query by setting the criteria, but can I set the criteria to look-up from another table? How?

    I am checking to see if someone uses medications for conditions, i.e., high blood pressure, asthma, etc., and I list the medications used for those conditions in table CHighBloodPressure, CAsthma, etc., and I want to check if the medication they are taking is listed in one of the table and if so flag their chart. I have a Yes/No field in the patient table which is inked to the medication table.

    Jail Administrator Medical
    JAM

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Qry table to find records listed in other table (A2K XP)

    I'm confused about what you're trying to do.
    <hr>I want to list in tables a set of parameters, (medications), and have the query look in the patient's medication table to see if there is a match. I just need to know it the person is taking one of the medications in the list and if so flag the person.<hr>
    What do you mean by the patient's "medication table"? The logical way to track patient medications is to have a table that contains the patient's ID and a medication for each medication that each patient takes, and maybe a field to indicate the condition that the medication treats. Are you trying to set a flag in the patient's main record to indicate they take medication for one of a group of special conditions? It sounds like you have a table like CHighBloodPressure listing all the medications that might be prescribed for that condition. Is that what you meant by medications table? Then what does this mean?
    <hr> I have a Yes/No field in the patient table which is inked to the medication table.<hr>
    How would you link a yes/no field to a list of medications?
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    Oklahoma City, Oklahoma, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Qry table to find records listed in other table (A2K XP)

    I am sorry, I wasn't clear. Yes I have a table listing all the medications that would be used for a particullar condition Casthma, Cpsych, CHighBloodPressure etc. I need the query to look at these tables, one table per query, and to "flag" the patients chart if they are taking one of the medication listed.

    An example: Mr. Jones takes haldol, an antipsychotic, haldol is listed in Cpsych. Now i use a query with the condition set to "Haldol" or "Trilifon" or "Zyprexa" or ....; I want the query to look at medications and select individuals who have any one of the medications listed in CPsych with a match in the medication table.

    What I am trying to do, since the condition pushes the limit to the complexity allowed, i.e., it gets LARGE, is to put all the medicaions for the condition in a table and let the query search the table, if the medication is listed in the "CXXX" table AND the "Medication" table set a Yes/No in their main table. (I do have the table that tracks their demographic data with a relationship to their medications using an ID field as you suggest.

    Jail Administrator Medical
    JAM

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Qry table to find records listed in other table (A2K XP)

    You've created a maintenance problem of the first order by breaking things up into individual tables to keep their size small, and I'm still not clear on what you're trying to accomplish with it. If you have a table that relates the patient to a medication (do you have the condition in that table as well?), then why are you trying to "flag" anything? You could easily display the medications the patient takes on a subform when you look at the patient"s primary record in a form. It sounds like you're trying to populate fields in the main table instead of using a relational method to view and maintain the data. The more copying of data between tables you do, the more likely that the data in any given table is going to be wrong or at least out of sync with the other tables. Then someone will have to spot that difference and research it and correct it. Avoiding any possible mismatch in the first place by NOT "flagging" data in one table based on another is not only faster, it's safer.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Qry table to find records listed in other table (A2K XP)

    Like Charlotte says, having separate tables for particular conditions is inefficient. I think you should use three basic tables: Patients, Medicines and Conditions, and two intermediate tables to establish the many-to-many links between them. See the attached picture. Using such a structure it would be easy to set up queries that display all patients who use an asthma medicine, or ...
    Attached Images Attached Images

  6. #6
    Star Lounger
    Join Date
    Apr 2001
    Location
    Oklahoma City, Oklahoma, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Qry table to find records listed in other table (A2K XP)

    What I do now with the query is to look-up everyone who takes certain medications, if they take the psych medications, I print a report quarterly listing their personal information and the medications they are taking. The same for the other reportable classes of medications. (Asthma, pregnancy, high blood pressure, psych, diabetes, seizure, stomach and medical are the reportable conditions.)

    My present query looks for the medications and if found it flags a Yes/No field on the patients main information table. I have a report that gathers all the information and prints the reports. Then sets the flag back to No. This is all the Yes/No field does is to act as a flag for printing reports.

    What I am trying to do is to list the medications that are now in the condition field of the query in a table instead. This way I could update the table as medications for the different conditions are added or deleted without going into the query and editing the condition field. If a new diabetic medication is approved and added to our formulary, which happens frequently, I could just add a medication to the bottom of the Cdiabetic table and it would then be included in those medications we must report on and the query would be updated easily. Now I must edit the condition field of the query and I have run into very long conditions that Access is saying are too complex, (too long I think as some conditions have very many medicaions used to treat them.)

    Is there a simpler way to do this? I am trying to make it as simple as possible and thought setting a list in a table would be the easiest way to list all the medications.

    Some medications are used in the treatment of many different conditions, an example would be Depakote which is used to treat seizures, depression, Bi-Polar (Manic Depressive) and anger. It would then fall into two different reportable categories.

    I hope this is clear and if I am on the wrong track please point me in the right direction.

    Jail Administrator Medical
    JAM

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Qry table to find records listed in other table (A2K XP)

    Take a look at Hans's post. What seems obvious to "simplify" the database design can, in fact, make it difficult to maintain over time.
    Charlotte

  8. #8
    Star Lounger
    Join Date
    Apr 2001
    Location
    Oklahoma City, Oklahoma, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Qry table to find records listed in other table (A2K XP)

    As I see this it would obliviate the need for the queries I now use. I think.

    What I have now are the following tables, I have simplified the previous for clairties sake.

    tblInmates (it is a jail) which have Autonumber, Name, Location, Date of Birth, Date of Arrest, Allergies, Doctor, Arresting agency and Flag field.

    tblMed which have ID (link to inmate, Med Name, Dose, Frequency of administration, Start Date, length of order, Stop Date, Discontinue and refill flags.

    Do I need to make a tblCondition for the eight reportable conditions and give them an ID? Looks easy enough as there are only the eight.

    How does the tblConditionMedicaine come in? Do I list the individual medications with the conditions they are used to treat? Would this just be two numbers in a table (ConditionID and MedicaineID) side by side? Would there be duplicates in the medicationID field for the multiple uses for the medications?

    tblMedicaine I already have (called tblFormulary). How do I link the tables to generate the report?

    Could you send a small example if possible? I am a visual person and the descriptions are sometimes difficult to follow.

    Thank You for your time and effort, I know I am fairly new at this but there is no one here that even knows how to get started with Access and I am it by default. The jail has over 2400 inmates and about half of them are on one or more medications so it is important to us here to get the information as automated as possible.

    JAM

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Qry table to find records listed in other table (A2K XP)

    I have attached a zipped Access 97 database. You'll have to unzip and convert it.

    You can have a look at the tables and the relations between them. The contents are for illustration purposes only. There is one query and a report based on that query.

    Note: As you noted, one medicine could be listed in more than one category. The tblInmatesFormulary doesn't record for what condition a medicine has been prescibed. So the query and report return ALL inmates who get a medicine listed in a category, whether they take it because of that category or not. If you want to be more precise, you'd have to register the category (condition) too in tblInmatesFormulary.

    HTH,
    Hans
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •