[Resolved] DAO/SQL UPDATE Statement help
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.
Code:
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:
Code:
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
