append query plus blank fields

[chris davies]

hello peeps,
I am using an append query to populate a table, the data is migrating ok, but now I want to set a value in one of the blank fields to correspond with the data from one of the appended/blank fields. For example, I am copying a treatment field (from an appointment table into the invoice table, plus other fields), now I need to set the price of this treatment which comes from the treatment table. My relationships are set correctly. I just don't know the syntax. I think its something like
insert into invoice treatmentcost where invoice.treatmentname = treatment.treatmentname;

but this isn't working for me. Any ideas? Thisis for my coursework and i realy STUCK on this one. Thanks in advance for any help.
Chris

 

[FoFa]

Don't you want an UPDATE instead?
UPDATE invoice
SET treatmentcost = (SELECT treatment.treatmentcost
from treatment where invoice.treatmentname = treatment.treatmentname);

You can use QBE to create this also.