Querying a column name made dynamically

[achick]

I have a table where I need to calculate changes based on time period and some conditions .
I made two new columns time1 and time2. time1 and time 2 have values of 04 , 05, 06 based on certain rules.

Here is the new table structure
id target04 target05 target08 time1 time2 change
1 25.6 34.6 56.7 04 05
2 20.6 34.6 56.7 04 08
change for id 1 = target05-target04
change for id 2 = target08-target04

I need to calculate change column between target columns based on time columns
update table x set change = "target"&time2-"target"&time1 ;

I get an error. Is there a special syntax?

 

[Golom]

You would need to consruct the SQL in code.

One of the rules of SQL is that the names of fields in a SELECT statement cannot be variables, parameters or computed values.

 

[achick]

Thank yo ufor replying

update table x set change = "target"&time2&"-"&"target"&time1

Should I update it through a form with sql code at back

 

[Golom]

Possibly ... although it depends on your application.

I would do something like

Dim db                          As DAO.Database
Dim rs                          As DAO.Recordset
Dim SQL                         As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From table")

Do Until rs.EOF
    SQL = "UPDATE table SET change = " & _
          "[Target" & rs![time2] & "]" & " & '-' & " & _
          "[Target" & rs![time1] & "]"
    db.Execute SQL
    rs.MoveNext
Loop

 

[lespaul]

and why put it in a table when you can calculate it on the fly every time in a query? Storing a calculated value breaks the rules of database design.....

Leslie

Have you met Hardy Heron?

 

[MajP]

As Leslie said you could calculate this dynamically using the below function

Public Function calcChange(time1 As Variant, time2 As Variant, ParamArray varNum() As Variant) As Single
  time1 = Val(time1) - 1
  time2 = Val(Time) - 1
  calcChange = varNum(time2) - varNum(time1)
End Function

Your time periods look like text.

to call this in sql just pass your field names in

Select somefield, calcChange(time1,time2,target04,target05,target...,target08) as TargetChange from someTable

 

[Golom]

Leslie, MajP

I don't think that's what the OP is trying to do.

It looks to me like he has a few fields like target05, target04, target08, etc. and a couple of other fields, time1 and time2 with values "04", "05", "08", etc.

He is attempting to build a field name of the form

"target" & time1

to yield a field in his SQL that looks like "Target05" (for example.)

As I said, field names cannot be variables or computed values so the SQL is failing and that's why I suggested that the only option is to construct the SQL in code.

Aside: This is really a normalization failure. It looks like the time1 and time2 fields are not attributes of just the primary key and the field names (or parts of them) have a hidden function as data. Of course, target## look like repeating fields which is also a normalization problem.

 

[achick]

Thanks all for replies
As Golom said I was trying to build variable names on fly,
I tried this code

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From table")

Do Until rs.EOF
SQL = "UPDATE table SET change = " & _
"[Target" & rs![time2] & "]" & "-" & _
"[Target" & rs![time1] & "]"
db.Execute SQL
rs.MoveNext
Loop

The cose gives no error but what happens is the change is not getting calculated correctly.
checked through some data and what might be happening is
the update statement updates all the rows for time2 and time1 instead of one by one

id target04 target05 target08 time1 time2 change
1 25.6 34.6 46.7 04 05 9
2 20.6 32.6 56.7 04 08 36.1
change for id 1 = target05-target04 = 34.6-25.6
change for id 2 = target08-target04=56.7-20.6

But when code is ran these are the values I get
id target04 target05 target08 time1 time2 change
1 25.6 34.6 46.7 04 05 21.1(46.7-25.6)
2 20.6 32.6 56.7 04 08 36.1

whatever are the last rows time1 and time2 it uses that to update all the rows. the code is updating all rows for each update statement and when it comes to the last one it updates all and thats what we see updated in the table.

Is there anyway we can just update record by record and not whole table may be using where clause and referring by id? I am not sure how to use record sets on it.

 

[achick]

just made a little code change and it seems to be working so far
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String
dim st as string
'I have a string id
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From table")

Do Until rs.EOF
st = rs![idstr]
SQL = "UPDATE table SET change = " & _
"[Target" & rs![time2] & "]" & "-" & _
"[Target" & rs![time1] & "]" & " where idstr= '" & st & "'"
db.Execute SQL
rs.MoveNext
Loop