query calculation

J

jor

Hi jeff,

This is in query, I would like to add the result in field3 in the new field
as shown in the example below. Is it posible to do it in Query?

field1 field2 field3 newfield
2 + 2 = 4 4
4 + 6 = 10 14
3 + 9 = 9 23

Thanks,

Rodrigs/jor
 
P

Pete D.

I assume you made a mistake in your sample and your looking for a running
total? Pete
 
J

jor

Dear Pete,

Yes you are right, i am looking for a formula that gives me the running
total which will be the newfield. any help for this? Thanks.

Dear Karl,

I stand corrected that 3 + 9 = 12 and not 9, im sorry i got mistake typing
the figures in my 3rd row example.
 
P

Pete D.

Simply
In the query grid under your new field name

TotalMiles: [EndingMiles]-[StartingMiles]

Or your example

NewField: [field1] + [field2]

Now before one of the experts says it, I'll say it; You really shouldn't do
it this way as you can calculate the info at any time in a query and do not
need nor should you store that data. But it is your choice. Pete
 
J

jor

Hi pete,

In excel it can simply calculate the newfield (D) by adding the cells "D" &
"C" as per below example:

A B C D=newfield for access

1 0
2 2 2 4 4 =(D1+C2)
3 4 6 10 14 =(D1+C2)
4 3 9 12 26 =(D2+C3)
5 2 5 7 33 =(D3+C4)
6
I understand that [field1]+[field2] is same in excel as A1+A2 but im very
much interested in cell C & D.

Because i have a database that needs to calculate the cell "C" which is a
pre input numbers and to be added as running total as cell "D". My question
is, can it be done in Query? Im realy new in access, so any suggestions to
practically do it in query/table/form/report or how it works?

Many thanks,







Pete D. said:
Simply
In the query grid under your new field name

TotalMiles: [EndingMiles]-[StartingMiles]

Or your example

NewField: [field1] + [field2]

Now before one of the experts says it, I'll say it; You really shouldn't do
it this way as you can calculate the info at any time in a query and do not
need nor should you store that data. But it is your choice. Pete

jor said:
Dear Pete,

Yes you are right, i am looking for a formula that gives me the running
total which will be the newfield. any help for this? Thanks.

Dear Karl,

I stand corrected that 3 + 9 = 12 and not 9, im sorry i got mistake typing
the figures in my 3rd row example.
Click to expand...
Click to expand...
 
P

Pete D.

If you understand excel look at below

Excel example
A B C D
2 2 4 4 =(D1+C2)

Access
FieldA FieldB FieldC FieldD
2 2 4 4
Queryfield:[FieldD]+[FieldC]

jor said:
Hi pete,

In excel it can simply calculate the newfield (D) by adding the cells "D"
&
"C" as per below example:

A B C D=newfield for access

1 0
2 2 2 4 4 =(D1+C2)
3 4 6 10 14 =(D1+C2)
4 3 9 12 26 =(D2+C3)
5 2 5 7 33 =(D3+C4)
6
I understand that [field1]+[field2] is same in excel as A1+A2 but im very
much interested in cell C & D.

Because i have a database that needs to calculate the cell "C" which is a
pre input numbers and to be added as running total as cell "D". My
question
is, can it be done in Query? Im realy new in access, so any suggestions to
practically do it in query/table/form/report or how it works?

Many thanks,







Pete D. said:
Simply
In the query grid under your new field name

TotalMiles: [EndingMiles]-[StartingMiles]

Or your example

NewField: [field1] + [field2]

Now before one of the experts says it, I'll say it; You really shouldn't
do
it this way as you can calculate the info at any time in a query and do
not
need nor should you store that data. But it is your choice. Pete

jor said:
Dear Pete,

Yes you are right, i am looking for a formula that gives me the running
total which will be the newfield. any help for this? Thanks.

Dear Karl,

I stand corrected that 3 + 9 = 12 and not 9, im sorry i got mistake
typing
the figures in my 3rd row example.



:

I assume you made a mistake in your sample and your looking for a
running
total? Pete
Since when does 3 + 9 = 9?
--
KARL DEWEY
Build a little - Test a little


:

Hi jeff,

This is in query, I would like to add the result in field3 in the
new
field
as shown in the example below. Is it posible to do it in Query?

field1 field2 field3 newfield
2 + 2 = 4 4
4 + 6 = 10 14
3 + 9 = 9 23

Thanks,

Rodrigs/jor

:

....insufficient information ...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Any formula or solution in the sample below:
2
2 4
2 6
3 9
5 14
6 20

Thanks,

Rodrigs
Click to expand...
Click to expand...
Click to expand...
 
J

jor

Thanks pete, but in excel i can simply calculate the second row as example
below:

A B C D
1 0 0 0 0
2 2 2 4 4 =(D1+C2)
3 6 4 10 this Cell well become 14 by adding D2+ C3

How can i do this in access? The example Queryfield:[FieldD]+[FieldC] is
giving me error wherein D is the running total.

FieldA FieldB FieldC FieldD is Running total
2 2 4 4
6 4 10 ?? this must me fieldC is 10 + fieldD
is 4


Pete D. said:
If you understand excel look at below

Excel example
A B C D
2 2 4 4 =(D1+C2)

Access
FieldA FieldB FieldC FieldD
2 2 4 4
Queryfield:[FieldD]+[FieldC]

jor said:
Hi pete,

In excel it can simply calculate the newfield (D) by adding the cells "D"
&
"C" as per below example:

A B C D=newfield in access

1 0
2 2 2 4 4 =(D1+C2)
3 4 6 10 14 =(D2+C3)
4 3 9 12 26 =(D3+C4)
5 2 5 7 33 =(D4+C5)
6
I understand that [field1]+[field2] is same in excel as A1+A2 but im very
much interested in cell C & D.

Because i have a database that needs to calculate the cell "C" which is a
pre input numbers and to be added as running total as cell "D". My
question
is, can it be done in Query? Im realy new in access, so any suggestions to
practically do it in query/table/form/report or how it works?

Many thanks,







Pete D. said:
Simply
In the query grid under your new field name

TotalMiles: [EndingMiles]-[StartingMiles]

Or your example

NewField: [field1] + [field2]

Now before one of the experts says it, I'll say it; You really shouldn't
do
it this way as you can calculate the info at any time in a query and do
not
need nor should you store that data. But it is your choice. Pete

Dear Pete,

Yes you are right, i am looking for a formula that gives me the running
total which will be the newfield. any help for this? Thanks.

Dear Karl,

I stand corrected that 3 + 9 = 12 and not 9, im sorry i got mistake
typing
the figures in my 3rd row example.



:

I assume you made a mistake in your sample and your looking for a
running
total? Pete
Since when does 3 + 9 = 9?
--
KARL DEWEY
Build a little - Test a little


:

Hi jeff,

This is in query, I would like to add the result in field3 in the
new
field
as shown in the example below. Is it posible to do it in Query?

field1 field2 field3 newfield
2 + 2 = 4 4
4 + 6 = 10 14
3 + 9 = 9 23

Thanks,

Rodrigs/jor

:

....insufficient information ...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Any formula or solution in the sample below:
2
2 4
2 6
3 9
5 14
6 20

Thanks,

Rodrigs
Click to expand...
Click to expand...
Click to expand...
 
P

Pete D.

I guess I'm not understanding your question.
jor said:
Thanks pete, but in excel i can simply calculate the second row as example
below:

A B C D
1 0 0 0 0
2 2 2 4 4 =(D1+C2)
3 6 4 10 this Cell well become 14 by adding D2+ C3

How can i do this in access? The example Queryfield:[FieldD]+[FieldC] is
giving me error wherein D is the running total.

FieldA FieldB FieldC FieldD is Running total
2 2 4 4
6 4 10 ?? this must me fieldC is 10 + fieldD
is 4


Pete D. said:
If you understand excel look at below

Excel example
A B C D
2 2 4 4 =(D1+C2)

Access
FieldA FieldB FieldC FieldD
2 2 4 4
Queryfield:[FieldD]+[FieldC]

jor said:
Hi pete,

In excel it can simply calculate the newfield (D) by adding the cells
"D"
&
"C" as per below example:

A B C D=newfield in access

1 0
2 2 2 4 4 =(D1+C2)
3 4 6 10 14 =(D2+C3)
4 3 9 12 26 =(D3+C4)
5 2 5 7 33 =(D4+C5)
6
I understand that [field1]+[field2] is same in excel as A1+A2 but im
very
much interested in cell C & D.

Because i have a database that needs to calculate the cell "C" which is
a
pre input numbers and to be added as running total as cell "D". My
question
is, can it be done in Query? Im realy new in access, so any suggestions
to
practically do it in query/table/form/report or how it works?

Many thanks,







:

Simply
In the query grid under your new field name

TotalMiles: [EndingMiles]-[StartingMiles]

Or your example

NewField: [field1] + [field2]

Now before one of the experts says it, I'll say it; You really
shouldn't
do
it this way as you can calculate the info at any time in a query and
do
not
need nor should you store that data. But it is your choice. Pete

Dear Pete,

Yes you are right, i am looking for a formula that gives me the
running
total which will be the newfield. any help for this? Thanks.

Dear Karl,

I stand corrected that 3 + 9 = 12 and not 9, im sorry i got mistake
typing
the figures in my 3rd row example.



:

I assume you made a mistake in your sample and your looking for a
running
total? Pete
Since when does 3 + 9 = 9?
--
KARL DEWEY
Build a little - Test a little


:

Hi jeff,

This is in query, I would like to add the result in field3 in
the
new
field
as shown in the example below. Is it posible to do it in Query?

field1 field2 field3 newfield
2 + 2 = 4 4
4 + 6 = 10 14
3 + 9 = 9 23

Thanks,

Rodrigs/jor

:

....insufficient information ...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Any formula or solution in the sample below:
2
2 4
2 6
3 9
5 14
6 20

Thanks,

Rodrigs
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

Thanks pete, but in excel i can simply calculate the second row as example
below:

A B C D
1 0 0 0 0
2 2 2 4 4 =(D1+C2)
3 6 4 10 this Cell well become 14 by adding D2+ C3

How can i do this in access? The example Queryfield:[FieldD]+[FieldC] is
giving me error wherein D is the running total.

FieldA FieldB FieldC FieldD is Running total
2 2 4 4
6 4 10 ?? this must me fieldC is 10 + fieldD
Click to expand...

Excel is a spreadsheet. Access isn't.

What expression are you using to calculate the running total? I guess you
could use

=DSum("[A] + ", "[queryname]", "[ID] <= " & [ID])

where ID is the (unnamed, in your example) sequentially increasing numeric
field, the leftmost in your example. Don't expect Access to provide a row
number though, there isn't one.

John W. Vinson [MVP]
 
J

jor

John, thank you very much for your help, it really solved my problem and now
i got the result sequentially increasing number. That is the answer i am
looking for.

jor

John W. Vinson said:
Thanks pete, but in excel i can simply calculate the second row as example
below:

A B C D
1 0 0 0 0
2 2 2 4 4 =(D1+C2)
3 6 4 10 this Cell well become 14 by adding D2+ C3

How can i do this in access? The example Queryfield:[FieldD]+[FieldC] is
giving me error wherein D is the running total.

FieldA FieldB FieldC FieldD is Running total
2 2 4 4
6 4 10 ?? this must me fieldC is 10 + fieldD
Click to expand...

Excel is a spreadsheet. Access isn't.

What expression are you using to calculate the running total? I guess you
could use

=DSum("[A] + ", "[queryname]", "[ID] <= " & [ID])

where ID is the (unnamed, in your example) sequentially increasing numeric
field, the leftmost in your example. Don't expect Access to provide a row
number though, there isn't one.

John W. Vinson [MVP]
Click to expand...
 
Top