update record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

there are 3 columns in a table, col1, col2 and total, try to update the
total to sum of col1, col2 and previous total, every time a new row has been
added. Any help will be appreciated.
 
hi Jennifer,
there are 3 columns in a table, col1, col2 and total, try to update the
total to sum of col1, col2 and previous total, every time a new row has been
added.
Click to expand...
You need to a query, which you need to call in the after update event:

UPDATE

SET [total] = [col1] + [col2]
WHERE IsNull([total])

But normally you wouldn't store calculated values in a table. Is a query
as datasource, e.g.

SELECT *, [col1]+[col2] As CalcTotal
FROM


not sufficient for your needs?


mfG
--> stefan <--
 
Create a function in a module, in that example the seperator between the
names will be ~

Function MySplit(MyStr As String, MyLocation As Integer)
On Error goto MySplit_Err
MySplit = Split(MyStr, "~")(MyLocation)

Exit Function
MySplit_Err:
MySplit = ""
End Function

In the Query you can use this spit function to split the field

Select MySplit([FieldName],0) As FirstName, MySplit([FieldName],1) As
SecondName, MySplit([FieldName],2) As ThirdName From TableNAme
 
Stefan,

Thank you for your response, I try to make total col as:

col1 col2 total
10 20 30
11 12 53
21 22 96

total 53=30+11+12
96=53+21+22

wondering how? Jennifer






Stefan Hoffmann said:
hi Jennifer,
there are 3 columns in a table, col1, col2 and total, try to update the
total to sum of col1, col2 and previous total, every time a new row has been
added.
Click to expand...
You need to a query, which you need to call in the after update event:

UPDATE

SET [total] = [col1] + [col2]
WHERE IsNull([total])

But normally you wouldn't store calculated values in a table. Is a query
as datasource, e.g.

SELECT *, [col1]+[col2] As CalcTotal
FROM


not sufficient for your needs?


mfG
--> stefan <--
Click to expand...
 
hi Jennifer,
col1 col2 total
10 20 30
11 12 53
21 22 96
total 53=30+11+12
96=53+21+22
Click to expand...
Where do you like to use this?

On a report you only need on TextBox. Set its ControlSource to
"=[col1]+[col2]" and its RunningSum property to overall.


mfG
--> stefan <--
 
Sorry wrong place
Please ignore this post
--
Good Luck
BS"D


Ofer Cohen said:
Create a function in a module, in that example the seperator between the
names will be ~

Function MySplit(MyStr As String, MyLocation As Integer)
On Error goto MySplit_Err
MySplit = Split(MyStr, "~")(MyLocation)

Exit Function
MySplit_Err:
MySplit = ""
End Function

In the Query you can use this spit function to split the field

Select MySplit([FieldName],0) As FirstName, MySplit([FieldName],1) As
SecondName, MySplit([FieldName],2) As ThirdName From TableNAme

--
Good Luck
BS"D


Jennifer said:
Hello,

there are 3 columns in a table, col1, col2 and total, try to update the
total to sum of col1, col2 and previous total, every time a new row has been
added. Any help will be appreciated.
Click to expand...
Click to expand...
 
Hi,

the catch is TOTAL itself needs to be included as a denominate for total.
Thanks.

Stefan Hoffmann said:
hi Jennifer,
col1 col2 total
10 20 30
11 12 53
21 22 96
total 53=30+11+12
96=53+21+22
Click to expand...
Where do you like to use this?

On a report you only need on TextBox. Set its ControlSource to
"=[col1]+[col2]" and its RunningSum property to overall.


mfG
--> stefan <--
Click to expand...
 
hi Jennifer,
the catch is TOTAL itself needs to be included as a denominate for total.
Click to expand...
First of all, generate a row total and calculate it in the after update
event:

UPDATE

SET [row_total] = [col1] + [col2]

Then you need a candidate key to order your data, a autoincrement value
would do it:

UPDATE

SET [running_total] = DSum("[row_total]","
", "ID < " & [ID])


mfG
--> stefan <--
 
Option Compare Database

Function Copy_of_Update_Petty_Cash_Balances_One_Day()
On Error GoTo Copy_of_Update_Petty_Cash_Balances_One_Day_Err

DoCmd.SetWarnings False

Dim i As Integer
Dim c As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset

'DoCmd.OpenQuery "Identify_PCBal_NoFlag_Records", acViewNormal, acEdit
'Set rs = db.OpenRecordset(identify_PCBal_NoFlag_Records, acViewNormal,
acEdit)

Set db = Application.CurrentDb

Set qy = db.CreateQueryDef("", "SELECT
count(Tbl_Petty_Cash_Balances.Flag)as c FROM Tbl_Petty_Cash_Balances WHERE
Tbl_Petty_Cash_Balances.Flag=false;")


Set rs = qy.OpenRecordset
'rs.Edit

'Set c = rs.Fields(0)
MsgBox c, vbOKOnly, "value of"





If c > 0 Then
For i = 0 To c

DoCmd.OpenQuery "Create_Last_Balance", acViewNormal, acEdit
DoCmd.OpenQuery "Create_Next_Balance", acViewNormal, acEdit
DoCmd.OpenQuery "Update_Last_Balance_Primary_Key", acViewNormal,
acEdit
DoCmd.OpenQuery "Update_Petty_Cash_Balances_Next_Day", acViewNormal,
acEdit
Beep
MsgBox "Updated one day", vbOKOnly, "Running Balance for Petty Cash"

Next i
End If



Copy_of_Update_Petty_Cash_Balances_One_Day_Exit:
Exit Function

Copy_of_Update_Petty_Cash_Balances_One_Day_Err:
MsgBox Error$
Resume Copy_of_Update_Petty_Cash_Balances_One_Day_Exit

End Function



Stefan,

Could you help me up to line of MsgBox c, vbOKOnly, "value of", to store
count value in c. Thank you very much.






Stefan Hoffmann said:
hi Jennifer,
the catch is TOTAL itself needs to be included as a denominate for total.
Click to expand...
First of all, generate a row total and calculate it in the after update
event:

UPDATE

SET [row_total] = [col1] + [col2]

Then you need a candidate key to order your data, a autoincrement value
would do it:

UPDATE

SET [running_total] = DSum("[row_total]","
", "ID < " & [ID])


mfG
--> stefan <--
Click to expand...
 
Stefan,

This works, just need to move the total one row up. please help. Thanks.



Stefan Hoffmann said:
hi Jennifer,
the catch is TOTAL itself needs to be included as a denominate for total.
Click to expand...
First of all, generate a row total and calculate it in the after update
event:

UPDATE

SET [row_total] = [col1] + [col2]

Then you need a candidate key to order your data, a autoincrement value
would do it:

UPDATE

SET [running_total] = DSum("[row_total]","
", "ID < " & [ID])


mfG
--> stefan <--
Click to expand...
 
I put them in two queries:

UPDATE Tbl_Petty_Cash_Balances SET Account_Balance =
((Amount_DR)-(Amount_CR));

UPDATE Tbl_Petty_Cash_Balances SET Account_Balance =
DSum("[Account_Balance]","[Tbl_Petty_Cash_Balances]","ID <" & [ID]);
 
hi Jennifer,
This works, just need to move the total one row up.

UPDATE Tbl_Petty_Cash_Balances SET Account_Balance =
((Amount_DR)-(Amount_CR));

UPDATE Tbl_Petty_Cash_Balances SET Account_Balance =
DSum("[Account_Balance]","[Tbl_Petty_Cash_Balances]","ID <" & [ID]);
Click to expand...
You need to address the correct lines, as it seems that the condition in
the DSum() is off by one.

Can you give us a complete and short example, including some data?


mfG
--> stefan <--
 
hi:

by using the following two queries, in the table, data should be as:
col1 col2 total
10 20 30
11 12 53
21 22 96
total 53=30+11+12
96=53+21+22
Click to expand...

instead it is:
10 20
11 12 30
21 22 53

so, need another queries to move total column one row up?

thanks.


Stefan Hoffmann said:
hi Jennifer,
This works, just need to move the total one row up.

UPDATE Tbl_Petty_Cash_Balances SET Account_Balance =
((Amount_DR)-(Amount_CR));

UPDATE Tbl_Petty_Cash_Balances SET Account_Balance =
DSum("[Account_Balance]","[Tbl_Petty_Cash_Balances]","ID <" & [ID]);
Click to expand...
You need to address the correct lines, as it seems that the condition in
the DSum() is off by one.

Can you give us a complete and short example, including some data?


mfG
--> stefan <--
Click to expand...
 
hi Jennifer,
so, need another queries to move total column one row up?
Click to expand...
Sorry, I was off by one. The correct DSum() must be:

DSum('[RowTotal]', '
', '[ID] <= ' & [ID])

See http:\\ste5an.de\access\jennifer.zip


mfG
--> stefan <--
 
Back
Top