sql syntax problem

[chris davies]

hello,
I am trying to get data from one field in one table to another, depending on the value of a field in the destination table. For example I have field called treatment in an invoice table which has been populated via an append query. Now I need to have the cost of this treatment in the same invoice table. I have tried various sql insert commmands but to no avail. I can't seem to get the microsoft access sql syntax correct. I want to try something like this:

insert into invoice (cost) where invoice.treatmentname=treatment.treatmentname;

Like I said, the syntax is wrong. Any ideas please?

Chris

 

[ShiftyJaffa]

Hi

In your example you are using the INSERT command which will add a new invoice to the invoice table. I'm assuming you have the invoice record already created (since you mention the invoice.treatmentname in your example) and need to transfer the cost over. You should use the UPDATE command and joining the invoice and treatment tables with an inner join.

Try:-

UPDATE invoice INNER JOIN treatment ON invoice.TreatmentName = treatment.TreatmentName SET invoice.cost = treatment.cost

This will update all invoices with the treatment cost. To do individual invoices, use the WHERE statement on the end and specify an individual invoice number or something unique on the invoice form.

Hope that helps

Paul