Sum Two Columns With Query

[SUN]

Hi,

I'm a newbie to Access. I have created a query showing the result base on other calculation. The returned results are in numeric. I would like to know how to sum up the returned results on row to a new column.

A b new column
100 200 300
200 150 350

Your kind help will be highly appreciated

 

[stopher]

Welcome to the forum.

Take a look here

There are many tutorials about queries available on the web which will help you understand what they can be used for and how to do it. It's worth doing a quick search (access query tutorial). Here's a couple of random ones:

http://www.fmsinc.com/tpapers/queries/index.html

http://www.databasedev.co.uk/queries.html

hth
Chris

 

[SUN]

Thanks stopher, the information is very useful. But I still cannot get the return I want. I would like 100(column A) + 200 (column B) = 300 (new column). I have tried to C: [A]+, but the return result is 100200. I have formatted [A] & in numeric. Is there something wrong with my query, please advise.

Thank you

 

[stopher]

Most likely the data type of [A] and is Text. Simply formatting the fields as Numeric won't do. You need to change the data type in the table design. Open the table in design view and change the fields to data type Numeric.

hth
Chris

 

[SUN]

But my result of [A] & comes from the result of query. I have tried to change the data type to numeric, but it doesn't work.

 

[stopher]

You can try:
C: Cdbl([A])+Cdbl()

Cdbl() converts the given value to a double data type.

However, this is a crude fix and you should get to the bottom of why A and B are acting like text. Perhaps you can post your d/b.

Chris

 

[SUN]

Thanks, I've got some idea. The cause of the problem is due to the result of [A] & , it should be value, not text.

Thank you for your kind help

 

[Manat]

Hello,
I have a similar problem. After trying different ways to perform the sum, I always get C = "AB" instead of A+B.
I realized I have tu use Nz for [A] since many times it is Null, which doesn't let me perform the sum. But anyway, my problem is that the table of origing is linked to an outside source (txt). This makes that I cannot change the type of data for [A] and to numeric since it is text by default.
Does anyone know if there is any function to change the format of a field (to numeric)? Or does anyone can kindly help me with any suggestion?
Thanks!

 

[VilaRestal]

As stopher said:
CInt(Nz([A],0)) + CInt(Nz(,0))
or
CDbl(Nz([A],0.0)) + CDbl(Nz(,0.0))

or perhaps

CInt(Nz([A],"0")) + CInt(Nz(,"0"))
or
CDbl(Nz([A],"0.0")) + CDbl(Nz(,"0.0"))

 

[Manat]

Thank you very much!!! It works!!!