Access Query to Simulate Excel Formula

  • Thread starter Thread starter NeedAnAnswer
  • Start date Start date
N

NeedAnAnswer

In Excel, in column C row 2, you can add the product of
column A row 2 multiplied by column B row 2 to the
product of column A row 1 multiplied by column B row 1
through the calculation entry "=(A1*B1)+(A2*B2)" for a
running total. In an Access query, how do you identify
the product of (A1*B1) to be added to the product of
(A2*B2)to produce the same running total? In the query I
started to set up: "C:(A*C) -- but there is no way to
identify rows 1 and 2 to complete the same formula as in
Axcel to obtain a running total. I have the Access rows
auto-numbered, but using that ID# as an identifying key
does not work. I have read 3 texts and still can't find
the answer. Feel free to email me and any assistance
would be greatly appreciated.
 
Hi,
Here is a sample sql statement that gives a running balance of two fields:
CashIn and CashOut which are in tblBalance. Id is my autonumber primary key:

SELECT t1.CashIn, t1.CashOut, ((Select Sum(tblBalance.CashIn - tblBalance.CashOut) From tblBalance Where tblBalance.Id <= t1.Id)) AS
Balance
FROM tblBalance AS t1;

now, that's not quite what you want. If I understand you correctly, you
would want to sum the product of CashIn and CashOut.

So this might work for you (*untested*):
SELECT t1.CashIn, t1.CashOut, ((Select Sum(tblBalance.CashIn * tblBalance.CashOut) From tblBalance Where tblBalance.Id <= t1.Id)) AS
Balance
FROM tblBalance AS t1;

now just substitute your table name for tblBalance and your two field names for
CashIn and CashOut and your autonumber field name for Id
 
In Excel, in column C row 2, you can add the product of
column A row 2 multiplied by column B row 2 to the
product of column A row 1 multiplied by column B row 1
through the calculation entry "=(A1*B1)+(A2*B2)" for a
running total.
Click to expand...

Excel is a spreadsheet, a good one.

Access is a relational database.

THEY ARE DIFFERENT. You can drive nails with a crescent wrench, but
that doesn't make it a hammer!

Despite the appearance of a table datasheet, *it is not a spreadsheet*
and it has no "rows" or "columns"! A Table is a mathematical Set
consisting of "records" - more properly though obscurely called
"relations", consisting of fields.

You can construct complicated queries to do what you ask... but if you
want a running sum, you may want to simply calculate it on the fly in
a Report. Include a calculated field in a Query multiplying FieldA by
FieldB, by typing

Prod: [FieldA] * [FieldB]

in an empty field cell; display this field on a Report in a textbox,
and set the textbox's RunningSum property to True.
 
Access tables have no inherent order -- they're just a bucket o' bits. How
will Access know which is the "previous" row?

This sounds like a problem that Excel will handle quite well -- is there a
pressing business reason why you aren't doing this in Excel?

Good luck

Jeff Boyce
<Access MVP>
 
Thanks I guess I will stick with Excel said:
-----Original Message-----
Access tables have no inherent order -- they're just a bucket o' bits. How
will Access know which is the "previous" row?

This sounds like a problem that Excel will handle quite well -- is there a
pressing business reason why you aren't doing this in Excel?

Good luck

Jeff Boyce
<Access MVP>

.
Click to expand...
 
Thanks - I guess I will stick with Excel.
-----Original Message-----
In Excel, in column C row 2, you can add the product of
column A row 2 multiplied by column B row 2 to the
product of column A row 1 multiplied by column B row 1
through the calculation entry "=(A1*B1)+(A2*B2)" for a
running total.
Click to expand...

Excel is a spreadsheet, a good one.

Access is a relational database.

THEY ARE DIFFERENT. You can drive nails with a crescent wrench, but
that doesn't make it a hammer!

Despite the appearance of a table datasheet, *it is not a spreadsheet*
and it has no "rows" or "columns"! A Table is a mathematical Set
consisting of "records" - more properly though obscurely called
"relations", consisting of fields.

You can construct complicated queries to do what you ask... but if you
want a running sum, you may want to simply calculate it on the fly in
a Report. Include a calculated field in a Query multiplying FieldA by
FieldB, by typing

Prod: [FieldA] * [FieldB]

in an empty field cell; display this field on a Report in a textbox,
and set the textbox's RunningSum property to True.


.
Click to expand...
 
Thanks for your time - I will stick with the other
experts and continue on with my Excel spreadsheet.
-----Original Message-----
Hi,
Here is a sample sql statement that gives a running balance of two fields:
CashIn and CashOut which are in tblBalance. Id is my autonumber primary key:

SELECT t1.CashIn, t1.CashOut, ((Select Sum
Click to expand...
(tblBalance.CashIn - tblBalance.CashOut) From tblBalance
Where tblBalance.Id <= t1.Id)) AS
Balance
FROM tblBalance AS t1;

now, that's not quite what you want. If I understand you correctly, you
would want to sum the product of CashIn and CashOut.

So this might work for you (*untested*):
SELECT t1.CashIn, t1.CashOut, ((Select Sum
Click to expand...
(tblBalance.CashIn * tblBalance.CashOut) From tblBalance
Where tblBalance.Id <= t1.Id)) AS
 
Back
Top