Adding Fields in Database

Hi, My question is a little away from the eCart, what I want to really accomplish here is I want to be able to add all values in 6 database columns and store it to another column (total) passing a query into the UPDATE form. Is it possible?

Here is a scenario:

UPDATE operating_expenses SET Total = SUM(Fuel + Grease_Oil + Tire_Repais + Parts + Insurance + License_Fees)

Or perhaps this scenario:

SELECT (Fuel + Grease_Oil + Tire_Repais + Parts + Insurance + License_Fees) AS Total FROM operating_expenses

CAN THIS BE DONE?


Thanks,

You could use the SUM function to add the different columns as long as your query only returns one result. If your query returns more than one result, it will add all values from that column from each of the results before adding the different columns.

Ok here is what I got, it works, but it has a delay when I press the Update record, that is: I have to press twice the update to make changes. Or should I say, I have to repeat the update process again in order for it to take effect into the database.

Do you know why this happens?

The other thing is, where can I place this same code into the insert record since the insert page does not have a server behavior.

==================================================================
SELECT OE_ID, Date_, Trip_No, Fuel, Oil_Change, Tire_Repairs, Parts, Insurance, License, Toll,
SUM(Fuel+Oil_Change+Tire_Repairs+Parts+Insurance+License+Toll) AS Total
FROM operating_expenses
WHERE OE_ID = colname
==================================================================

many thanks,

That looks like its just selecting values from the database, not making any updates. You would want to use an update sql statement to update the value in the database.

Are the pages you are working with generated by eCart? It seems like insert and update pages are generated by DataAssist, not eCart.

==================================================================
That looks like its just selecting values from the database, not making any updates. You would want to use an update sql statement to update the value in the database.

Are the pages you are working with generated by eCart? It seems like insert and update pages are generated by DataAssist, not eCart.
==================================================================

No, I´m not using eCart, it´s just a simple update page created with DataAssist and I want to be able to change totals as I update with new changes, but also would like to create totals as I insert a new record.
Like I said this sql statement has been placed inside of Server Behavior, it works, but I have to repead the process twice in order to make the changes to the database.

Am I doing this the wrong way, any ideas on how to resolve this???

Thanks again.

Ok, I was just checking since you posted in the eCart forum.

Do other values get updated and only the total not getting updated or do none of the values get updated? If you could revert back to the original query, do the values get updated?