Convert Measurement in Access VBA and update the same Access table 2

[wuju]

Howdy,

I need to convert some records (not all) in my access table to match with same unit.

I have a access table with mismatch in unit: some in kilowatt and some in megawatt.

I want to unify unit in megawatt and update the table.
The following is the access table I have with fields pID, Date, 1, 2,,3, ....24 (each number stands for hour).

AS you see, pID 1 has unit in kilowatt and pID 2 has unit in megawatt for each hour field. I want to right VBA to convert kilowatt of each hour(1 through 24) in pID = 1 to megawatt: such as [1]/[1]/1000, [2]=[2]/1000,.... [24]=[24]/1000.

The following is part of my Access table.

pID Date 1 2 3 4 ......... 24
1 1/1/2010 2500 2200 1800 2300 ..........2200
1 1/2/2010 1800 1200 1700 1300 ..........2100
.......

1 11/28/2010 2300 2100 2800 2600 ..........2500
2 1/1/2010 2.7 2.2 1.8 1.3 ............ 2.9
2 1/2/2010 2.4 2.8 1.9 1.7 ............ 2.4
........


So far, I wrote the VBA below to work out as what I want to see, but it is not working. Please help me out.

Regards,

Wuju




FYI, the following is my incomplete VBA code.

Private Sub convertKWh2MWh()

Dim pID As Integer

For EOF 'I am not sure what I need to put here (may use DO UNTIL EOF)

Select Case pID
Case 1
CurrentDb.Execute "update importMeter_t set [1]='" & [1] / 1000 & ""
CurrentDb.Execute "update importMeter_t set [2]='" & [2] / 1000 & ""
.....
CurrentDb.Execute "update importMeter_t set [24]='" & [24] / 1000 & ""

Case 3
CurrentDb.Execute "update importMeter_t set [1]='" & [1] / 1000 & ""
...

End Select

Next

End Sub

 

[MajP]

Public Sub covertMtoK()
'your name here
Const tblName = "tblData"
Dim i As Integer
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(tblName, dbOpenDynaset)
'If your fields are really named 1 to 24
Do While Not rs.EOF
For i = 1 To 24
If rs.Fields(CStr(i)) > 1000 Then
rs.Edit
rs.Fields(CStr(i)) = rs.Fields(CStr(i)) / 1000
rs.Update
End If
Next i
rs.MoveNext
Loop
End Sub

 

[dhookom]

I would change the structure so it was normalized and didn't require repeating similar expressions to maintain. However, you might consider something like:

Dim strSQL as String
strSQL = "UPDATE importMeter_t set [1]=[1]/1000, " & _
  "[2]=[2]/1000, [3]=[3]/1000, ...etc.. [24]=[24]/1000 " & _
  "WHERE PID IN (1,3,...)"

Duane
Hook'D on Access
MS Access MVP

 

[wuju]

Thanks majP for quick respond.

Some of data are in MWh even though numbers are more than 1000. I would like sort and convert data in KWh to MWh based on pID. I know which pID are in KWh(pDI= 1~22, 51~57, and 63) and which are in MWh(pID=58~62, and 64~66). So could you give example of coding with conditioning of pID number?

thank you very much.

Wuju

 

[MajP]

I would use the same code, to shorten writing the long sql.
Build tblSelected populated with the id that you want to convert. Do a inner join to the data table to return only the desired records. Call that qrySelected.

Then change the code to
Const tblName = "qrySelected"


tblSelected
selectedID

 

[MajP]

Your query could be a simple where, but that could get really long. "Where pid = 1 OR pid = 15 ..... OR pid = 204". The simple table would make this a lot faster to populate.

 

[wuju]

Hi MajP,

Thank you for your advise.
It seems like you have very good idea, and I really want to follow your direction.
Since I am new to the database, sql and VBA (I just started to learn database two month ago), I kinda understant your advise, but not quite whole things especially making sql and inner join.


My sql code is below and please let me know if this is correct especially INNER JOIN .... ON .....
By the way, my existing table name is importMeter_t and sql is qrySelected (as you suggested)


SELECT *
FROM qrySelected INNER JOIN importMeter_t ON qrySelected.pID = importMeter_t.pID AND qrySelected.Date = importMeter_t.Date

WHERE
(pID >= 1 AND pID <=22) OR
(pID >= 51 AND pID <=57) OR
pID = 63



How do I change this conditional statement in VBA?
If rs.Fields(pID) > 1000 Then

Do I just change to
If rs.Fields(pID) <> 0 Then


Public Sub covertMtoK()
'your name here
Const tblName = "qrySelected"
Dim i As Integer
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(tblName, dbOpenDynaset)
'If your fields are really named 1 to 24
Do While Not rs.EOF
For i = 1 To 24
If rs.Fields(pID) > 1000 Then
rs.Edit
rs.Fields(CStr(i)) = rs.Fields(CStr(i)) / 1000
rs.Update
End If
Next i
rs.MoveNext
Loop
End Sub

 

[wuju]

Hi MajP,

Can I just conditioning with pID as below?
If rs.Fields("pID") <= 22 Or (rs.Fields("pID") >= 51 And rs.Fields("pID") <= 57) Or rs.Fields("pID") = 63 Then


Option Compare Database
Option Explicit
Public Sub covertKtoM()
'your name here
Const tblName = "importMeter_t"
Dim i As Integer
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(tblName, dbOpenDynaset)
'If your fields are really named 1 to 24
Do While Not rs.EOF
For i = 1 To 24
'If rs.Fields(CStr(i)) > 1000 Then
If rs.Fields("pID") <= 22 Or (rs.Fields("pID") >= 51 And rs.Fields("pID") <= 57) Or rs.Fields("pID") = 63 Then
rs.Edit
rs.Fields(CStr(i)) = rs.Fields(CStr(i)) / 1000
rs.Update
End If
Next i
rs.MoveNext
Loop
End Sub

 

[MajP]

Since every record in your query is to be updated the conditional statement just goes away.

Do While Not rs.EOF
For i = 1 To 24
rs.Edit
rs.Fields(CStr(i)) = rs.Fields(CStr(i)) / 1000
rs.Update
Next i
rs.MoveNex

 

[MajP]

FYI, several people have mentioned using an update query which you may want to consider. Since you have 24 fields it was a lot shorter for me to write code, but the udpate is simple and basically a cut and paste.

 

[wuju]

hi MajP,

Thank you for your great help.
Even though I am new to the VBA, I would like to coding, so your help has been great.

Method to having sql to select the desired records to convert in MWh is good, but original MWh data in importMeter_t is removed since sql only select the data which are in KWh.

So I want to ask you if I change your origianl code of conditional statement
If rs.Fields(CStr(i)) > 1000 Then
to
If rs.Fields("pID") <= 22 Or (rs.Fields("pID") >= 51 And rs.Fields("pID") <= 57) Or rs.Fields("pID") = 63 Then

When I run it, I have Run-time error '3502':
File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

Do you know what this error is for and how to fix it?

regards,

WuJu

 

[wuju]

Hi Dhoocom,

I coded as below as you suggested and ran it.
Nothing happended in my importMeter_t.
Could you help me out why this is not working?

Regards,

Wuju

FYI, the following is my coding.

Option Compare Database
Option Explicit

Private Sub convertKWh2MWh()

Dim strSQL As String

strSQL = "UPDATE importMeter_t set [1]=[1]/1000,[2]=[2]/1000, [3]=[3]/1000,[4]=[4]/1000, " & _
"[5]=[5]/1000,[6]=[6]/1000, [7]=[7]/1000,[8]=[8]/1000, " & _
"[9]=[9]/1000,[10]=[10]/1000, [11]=[11]/1000,[12]=[12]/1000, " & _
"[13]=[13]/1000,[14]=[14]/1000, [15]=[15]/1000,[16]=[16]/1000, " & _
"[17]=[17]/1000,[18]=[18]/1000, [19]=[19]/1000,[20]=[20]/1000, " & _
"[21]=[21]/1000,[22]=[22]/1000, [23]=[23]/1000,[24]=[24]/1000, [25]=[25]/1000 " & _
"WHERE PID IN (1,2)"

End Sub

 

[dhookom]

How did you get 25 fields? I thought you stated there were 24. You need to execute the SQL much like your first post:

debug.Print strSQL  'display the sql in the debug window
CurrentDb.Execute strSQL, dbFailOnError

Do you have a table where the PID is the primary key? If not, you should create one and have a field that can be used as the multiplier/divisor.

Duane
Hook'D on Access
MS Access MVP

 

[wuju]

Sorry. It is upto 24.
Are you suggesting for me to run on sql or VBA?

 

[dhookom]

I don't generally use VBA when I can use SQL.

Are you going answer my question about a table with PID as the primary key?

Duane
Hook'D on Access
MS Access MVP

 

[wuju]

Yes, dhookom.

My coding below is working.

UPDATE importMeter_t SET 1 = [1]/1000, 2 = [2]/1000, 3 = [3]/1000, 4 = [4]/1000, 5 = [5]/1000, 6 = [6]/1000, 7 = [7]/1000, 8 = [8]/1000, 9 = [9]/1000, 10 = [10]/1000, 11 = [11]/1000, 12 = [12]/1000, 13 = [13]/1000, 14 = [14]/1000, 15 = [15]/1000, 16 = [16]/1000, 17 = [17]/1000, 18 = [18]/1000, 19 = [19]/1000, 20 = [20]/1000, 21 = [21]/1000, 22 = [22]/1000, 23 = [23]/1000, 24 = [24]/1000
WHERE PID IN (1,2,3,4,5,6,10,51,100);

 

[dhookom]

I don't care for hard-coding numbers into SQL or code or anywhere else.

I would have a table of every unique PID and a field [ConversionMultiplier] that would be the multiplier. In this case, PIDs 1, 2, 3, 4, 5, 6, 10, 51 ,and 100 would have a field value of 0.001 and others would have a value of 1. You would add this table to your update query and multiply the field [ConversionMultiplier] times the imported value. This would get rid of 24 "1000"s.

Duane
Hook'D on Access
MS Access MVP