Update Query Question

[uksigma]

Hi again, I’ve hit another problem, I hope someone can help.

I have a table [tblTreatment Received] that contains numerous records for the same single record in the other table [tblAppointments]. The [tblAppointments] table is therefore related to the [tblTreatment Received] in a one-to-many relationship.

I have created an update query that I want to update a field in the main table [tblAppointments] with the sum of all the Unit Costs from the related table. At the moment, it's only updating it with one of them. E.g. there are three unit costs saved for one appointment , £200, £50 and £25; when I run the query, the field updates to £25 instead of £275. At present, the query is set to update the field [AppCost] in the [tblAppointments] table and in the ‘update to:’ I’ve simply entered ‘[tblTreatmentRec eived].[Unit Cost].

Does anyone know how to make an update query update the field with the sum of all of the fields rather than just one. I tried using Sum() but no go.

Thanks a lot.

[ADezii]

Hi again, I’ve hit another problem, I hope someone can help.

I have a table [tblTreatment Received] that contains numerous records for the same single record in the other table [tblAppointments]. The [tblAppointments] table is therefore related to the [tblTreatment Received] in a one-to-many relationship.

I have created an update query that I want to update a field in the main table [tblAppointments] with the sum of all the Unit Costs from the related table. At the moment, it's only updating it with one of them. E.g. there are three unit costs saved for one appointment , £200, £50 and £25; when I run the query, the field updates to £25 instead of £275. At present, the query is set to update the field [AppCost] in the [tblAppointments] table and in the ‘update to:’ I’ve simply entered ‘[tblTreatmentRec eived].[Unit Cost].

Does anyone know how to make an update query update the field with the sum of all of the fields rather than just one. I tried using Sum() but no go.

Thanks a lot.

Here is a solution which definately works, but I'm pretty sure the SQL Gang will come up with a better one, so be patient. First, a couple of Assumptions:

1. tblAppointments contains a Field named [Total Treatment Cost] {CURRENCY} which will contain the Total Cost of each related Treatment in tblTreatmentRec eived.
2. tblTreatmentRec eived contains a Field named [Cost] {CURRENCY} which contains indiviodual ttreatment costs related to a specific Appointment.
3. tblAppointments and tblTreatmentRec eived are related by the Field [AppID], namely: tblAppointments .[AppID]{1} ==> tblTreatmentRec eived.[AppID](MANY}.

Dim strSQL As String, MyDB As Database, MyRS As DAO.Recordset
 
strSQL = "Select * From tblAppointments;"
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
 
Do While Not MyRS.EOF
  MyRS.Edit
    MyRS![Total Treatment Cost] = DSum("[Cost]", "tblTreatmentReceived", "[AppID] = " & MyRS![AppID])
  MyRS.Update
  MyRS.MoveNext
Loop
 
MyRS.Close
Set MyRS = Nothing

[uksigma]

Thanks for that.
It seems to work okay!