This was the complete SQL view of your query? If so, I expect it should
work. Try create a new, blank query with no table selected. Change to the
SQL View and paste this into the view:
Select CAInfo.*, (SELECT Sum([Change Amount]) FROM CAInfo A WHERE A.LOANo=
CAInfo.LOANo and A.CANo<=CAInfo.CANo) as RunningSum FROM CAInfo;
--
Duane Hookom
MS Access MVP
--
omelcon said:
This is what I tried
Select *, (SELECT Sum([Change Amount]) FROM CAInfo A WHERE A.LOANo=
CAInfo.LOANo and A.CANo<=CAInfo.CANo) as RunningSum FROM CAInfo;
Duane Hookom said:
How about providing the exact SQL that you tried?
--
Duane Hookom
MS Access MVP
--
I tried the subquery you suggested however I was unable to get it to
work.
Not sure what I am doing wrong the popup dialog box I get states "Check
the
Subquery's syntax and enclose the subquery in parentheses
:
You missed the table names as well as the space in your Change Amount
field.
Try this:
Select *, (SELECT Sum([Change Amount]) FROM CAInfo A WHERE
A.LOANo= CAInfo.LOANo and
A.CANo<=CAInfo.CANo) as RunningSum
FROM CAInfo;
--
Duane Hookom
MS Access MVP
--
Below is what I had entered and I had got an error that indicated
the
Syntax
of the subquery in this expression is incorrect
Select *, (SELECT Sum(ChangeAmount) FROM tblCAInfo_1 WHERE
CAINfo_1.LOANo= tblCAInfo.LOANo and
CAInfo_1.CANo<=tblCAInfo.CANo) as RunningSum
FROM tblCAInfo;
The Table Name Name is CAInfo and the field names are as follows.
CANo LOANo and Change Amount. where (CANo is the individual
Numbers
for
each Contract Number) and (LOANo is the individual Contract No.)
The
CA
Number is an automatic Sequential Number created from a Modual based
on
the
Contract (LOA) Number.
:
Possibly use a subquery
Select *, (SELECT Sum(ChangeAmount) FROM tblYourTable A WHERE
A.ContractNumber= tblYourTable.ContractNumber and
A.CCNNO<=tblYourTable.CCNNO) as RunningSum
FROM tblYourTable;
If this doesn't work, come back with exact table and field names
and a
reason why this didn't work.
--
Duane Hookom
MS Access MVP
--
I am not sure how to group my report by CCNo however the problem I
see
with
this is when I set the running sum box on the report and I have
my
Form
set
to print current Record it only shows the current value not the
Running
sum..
What I would like it to do is to give me a total value of all the
previous
CCNo's combined for that particular ContractNumber.
:
Did you try to group your report by CCNo and set the running sum
to
"Over
Group"?
--
Duane Hookom
MS Access MVP
--
I have tried this and found that it gives me a running total of
all
of
the
contract Numbers. What I am trying to achieve is a seperate
report
for
each
CCNO but with the ChangeAmounts from the individual
ContractNumber
for
example LOA01-01would have an ChangeAmount of $500.00 and a
previouse
balance
of $0.00, then LOA01-02 would have a ChangeAmount of $200.00
and
a
previous
balance of $500.00 and LOA01-03 would have a ChangeAmount of
$200.00
and a
previous balance of $700.00. and Where a differnet
ContractNumber
fits
in
it
would start all over note that LOA02-01 could follow after
LOA-02
but
would
have to start with it's own previous balance of $0.00.
:
Have you "played" with the Running Sum property of a text box
on
a
report?
If not, please try. If so, what did or didn't work for you?
--
Duane Hookom
MS Access MVP
message
I have a number of columns in my table
CCNNO. ContractNumber ChangeAmount
LOA01-01 LOA01 500.00
LOA01-02 LOA01 250.00
LOA02-01 LOA02 750.00
I am trying to have a running sum of the Change Amount in
my
report
based
on
the Contract Number for all of LOA01, LOA02, and any future
LOA
numbers
added.
Does any one know if this is possible as I am relativly new
to
using
Access
and have been unable to find anything that would help
