[Resolved] DAO/SQL UPDATE Statement help

[A441OTA]

I guess it really helps to type out your own issues... I had a working solution within 5 minutes of posting my thread.... I created a recordset then pulled the value from the "N" field and set the TotalCount variable to this value.

Following is the code that worked.

Dim rsDemo As DAO.Recordset
Dim strDemo As String

'create a temp recordset that pulls the "Total population" record
strDemo = "SELECT * FROM Temp_DemoData WHERE Temp_DemoData.Label_Order = 1"

Set rsDemo = db.OpenRecordset(strDemo)
     With rsDemo
          .movefirst
          TotalCount = rsDemo.Fields("N").Value
     End With

     rsDemo.Close     'close recordset
     Set rsDemo = Nothing     'release memory

sql = "UPDATE Temp_DemoData SET Temp_DemoData.Total_Count = " & TotalCount
db.Execute sql

Thanks to everyone again!!











***************Original post*************
Good afternoon everyone!

I am in DAO, Access 2002 (very convoluted data file), VB 6 -- I'm trying to update a field in a table to equal the value of a different field of one particular record in the same table.

I have other update, select, and alter statements working that make changes to the database so it shouldn't be a permissions issue.

I have to set the values of a field for all records to equal the "total" value to perform a calculation a bit further in the code. So I have a field called "N" (don't ask I didn't name it and I can't change it) which contains the number of total respondents for a particular subgroup of the entire group. I need to later calculate a percentage based on the "N" for the entire population. I added a field "Total_Count" that should be the same for every record. Total_Count should equal the value of the "N" field of the Total population record (WHERE Label_Order = 1)

I tried:

sql = "UPDATE Temp_DemoData SET Temp_DemoData.Total_Count = Temp_DemoData.N WHERE Temp_DemoData.Label_Order = 1"
db.execute sql

Which of course only updated the record where Label_Order = 1 (the record for the total population), but I need all records to have this same value...

At the point that I am doing this I only have the database open, not a recordset. I tried opening the recordset to get the value and set a variable equal to this then use that in the above statement, but it didn't work because I don't know what I'm doing in DAO!!

Hope that makes sense, and that somebody has a suggestion... my mind is coming up with a blank and when I do a search for UPDATE, SET, DAO, etc. I get a multitude of results (which I'll continue trying to weed through for now).

Thanks,
Mary

[ralph]

sql = "UPDATE Temp_DemoData SET Temp_DemoData.Total_Count =
(select Temp_DemoData.N From Temp_DemoData WHERE Temp_DemoData.Label_Order = 1)"

[A441OTA]

Thanks Ralph!

I think that was a solution I tried earlier but couldn't get it to work (of course in my delirium it could have been a typo issue, but it makes me feel good that I went that route at least once!!! I'm sure it was something I did that caused it not to work!!)... however, in between posting the question and trying one more feable attempt I got something to work!!!!!! My data is still running, so if something does crash I will go back to this solution!

Appreciate your quick response -- have a great day!!