I got a simple question. Can I calculate different columns in a table that have numbers in currency?
My table looks something like this (debit1, debit2,debit3,debit4,debit5,debit6 and the same for credit1 true credit6) and the fields for totals are tdebit and tcredit. If this posible please help and if not tell me what I got to do.

Thanks

You can add the columns together, (debit1+debit2+debit3) etc, but seriously,
I think that you should redesign the table structure.

radzi
"luisantonio" wrote in message
news:2430164.1043360091@dbforums.com...
  > I got a simple question. Can I calculate different columns in a table
  > that have numbers in currency?
  > My table looks something like this (debit1,
  > debit2,debit3,debit4,debit5,debit6 and the same for credit1 true
  > credit6) and the fields for totals are tdebit and tcredit. If this
  > posible please help and if not tell me what I got to do.
  > Thanks
  > --
  > Posted via http://dbforums.com

Hello,

do you you mean you want to summarize the different columns ?
Use: Select SUM(debit1), SUM(debit2) from table where ...
If you want to update the tdebit field use:

UPDATE table SET tdebit = SUM(debit1)+SUM(debit2) ....

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

I'll try that.

The sum work, but I could not make the totals for tdebit.
This is what I write
SELECT
SUM(debit1),SUM(debit2),SUM(debit3),SUM(debit4),SU M(debit5),SUM(debit6)
FORM TABLE

UPDATE table SET tdebit = SUM(debit1)+SUM(debit2)+SUM(debit3)+SUM(debit4)+SU M(debit5)+SUM(debit6)

What did I miss?

Thanks for all your Help.

I suppose the statement should be:

UPDATE table SET tdebit = debit1+debit2+....+debit6

In this case tdebit is updated in the whole table. If you want to update a specific record you'll want to do something like

UPDATE table SET tdebit = debit1+debit2+....+debit6
WHERE <primary key>=<some value>

BTW, I agree with Radzi that you might want to restructure your tables at some point in time. What do you do when you need debit7?!

Ad Dieleman.

Hello,

all the others are right. Thing about a new design and reorganise your table - but, this would answer your question ?!

Which error do you get and what do you want to save exactly in tdebit ?
Is the SUM really a value, that you want to save ? Or is it better to calculate it every tim you need the value ?
If you save the SUM of all debit field in tdebit you have a lot of redundat datas ? Do you know, what I mean ?

Best regards

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

Im working with only 6 fields for debit and credit. In tdebit I want the the total sum of debit1...debit6 the exact calculation of those fields, this is like a receipt application for enter the payments of the suppliers. The user will enter this information true a web page.
I want to make all the calculation work so then I can redesign my tables. This is where I am a little stuck.
I try the (UPDATE table SET tdebit = debit1+debit2+....+debit6) to get my totals for debit but I keep getting a (syntax error in from clause).
I am new to sql, I just know a little so if you guys can guide whith your help and knowledge I will be very happy and thankfull to all of you.

Please help!!!


Luis Antonio

Please state exactly what SQL statement you are tryimg, and what error message you get. The example you just gave is perferctly valid, and should work:

UPDATE table SET tdebit = debit1+debit2+....+debit6;

SELECT SUM (debit1+debit2+debit3+debit4+debit5+debit6)
FROM suplidores
UPDATE suplidores SET tdebito=debit1+debit2+debit3+debit4+debit5+debit6

This is what I get, the error is:
[Microsoft][ODBC Microsoft Access Driver]Syntax error in FROM Clause.

I am working this with Dreamweaver MX.
I need that when the users enters a value for debit or credit it automatically change my totals for tdebit and tcredit in the page and that total to be stored in my DB, can this be possible? I am working in my DB, but first I will like to make this to run so then I can concentrate more in the redesign of the DB.

Hello,

you can not use the prompted statement in one command.
Just use


UPDATE suplidores SET tdebito=debit1+debit2+debit3+debit4+debit5+debit6


Then you get a SUM of every record in your tdebito field.

IF you need SUM of all tdebito fields then use
SELECT SUM(tdebito) from suplidores

Hope that helps ?

Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com