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

    update table based on data in 2nd (A2k XP)

    I have a table with medication data in it. I have another table with a listing of all formulary medications in it. Now I am adding the medication by name in the first table but was advised to add instead a number, so far so good. It even makes sense to reduce the number of errors and space.

    tblMedication has the following fields: ID (Patients number) , IDMedicine (New field to provide the link) , Med (text field with the name of the medication spelled out) , Dose/Frequency of delivery/Start & Stop Date/etc.

    tblFormulary has the following fields IDMedicine (autonumber primary key) , Med (Text field as above) , IDCondition (a link field for different medical conditions) and Formulary (Yes/No field to indicate funding responsability)

    What I need to do is to copy tblFormulary!IDMedicine number to the tblMedication!IDMedication field based on the Med (Text) field.

    I considered doing it by hand but there are over 3000 individual records and that would be impossible.

    I tried using an update query but I obviously screwed it up because nothing was updated.

    What do I do?

    Jail Administrator Medical
    JAM

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update table based on data in 2nd (A2k XP)

    Make a copy of your database first. Then try running a make table query. Then you could go back and delete any tables that are now of no use.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: update table based on data in 2nd (A2k XP)

    You should be able to do this with an update query, so can you post the one you tried, so that others might be able to tell you what is wrong with it.
    Regards
    John



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

    Re: update table based on data in 2nd (A2k XP)

    Assuming you don't have any spelling mismatches between the two tables, create a query and join the two tables on the text field that contains the name of the medication. Make it an update query something like this:

    UPDATE DISTINCTROW tblMedication INNER JOIN tblFormulary ON tblMedication.Med = tblFormulary.Med SET tblMedication.IDMedicine = tblFormulary.IDMedicine;
    Charlotte

Posting Permissions

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