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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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;

Reply | Quote