PreviousBalance

  • Thread starter Thread starter SP
  • Start date Start date
S

SP

PreviousBalance Deposit Balance
0 1 1
1 5 6
6 2 8


Hi

I'm looking for help on creating a query that can generate the
PreviousBalance column above. The PreviousBalance is basically getting its
values from the Balance column and 1 record above.

Please help.
Thanks
Steve
 
Dear Steve:

Perhaps, but there HAS to be another column you don't show which puts these
in the order you show. If the rows are not ordered uniquely, then none of
this actually makes sense (well, not to me).

Please write a Query that produces what you show with the exception of the
Balance column, and which sorts the results uniquely. I can then add the
code for you to show the Balance column.

Tom Ellison
 
Thank you very much for the quick reply. Well, the missing column is
likely the Date column. Right now, all I have is a simple table with 2
fields (Date and Deposit). From that table, it would be great to have a
query (or code as you mentioned) that generate the Balance and
PreviousBalance.

Date PreviousBalance Deposit Balance
7/06 0 1 1
8/06 1 5 6
9/06 6 2 8

Steve
 
Dear Steve:

Without the query code for which I asked you, I will just make some guesses:

SELECT [Date],
(SELECT SUM(Deposit)
FROM YourTable T1
WHERE T1.[Date] < T.[Date])
AS PreviousBalance,
Deposit,
(SELECT SUM(Deposit)
FROM YourTable T1
WHERE T1.[Date] < T.[Date])
AS Balance
FROM YourTable T
ORDER BY [Date]

This will work well enough as long as there are not two deposits on the same
date. It will not be able to differentiate between two simultaneous
deposits, and they will be added into the Balance simultaneously. Unless
you have something else in the table that, when taken with [Date], can order
the rows uniquely, there may not be a solution to this, except perhaps to
add the Deposits together so there is only one shown per date.

In the query, you must replace YourTable with the actual name of your table,
or query. Initially, do not change anything else. Let's see what this
does.

Tom Ellison
 
I think I just clicked on Reply by mistake (instead of Reply Group). I'm
resending my result here.

===============================
Hi Tom

With the table I got, I change its name to "YourTable" so that I can run
your code without modification. Here is the result.

Date PreviousBalance Deposit Balance
8/1/2006 1
9/1/2006 1 5 1
10/1/2006 6 2 6

I figured you could easily replicate my table as got the same result, and
I'm hoping that you can tweak it.

Thank you
Steve


Tom Ellison said:
Dear Steve:

Without the query code for which I asked you, I will just make some
guesses:

SELECT [Date],
(SELECT SUM(Deposit)
FROM YourTable T1
WHERE T1.[Date] < T.[Date])
AS PreviousBalance,
Deposit,
(SELECT SUM(Deposit)
FROM YourTable T1
WHERE T1.[Date] < T.[Date])
AS Balance
FROM YourTable T
ORDER BY [Date]

This will work well enough as long as there are not two deposits on the
same date. It will not be able to differentiate between two simultaneous
deposits, and they will be added into the Balance simultaneously. Unless
you have something else in the table that, when taken with [Date], can
order the rows uniquely, there may not be a solution to this, except
perhaps to add the Deposits together so there is only one shown per date.

In the query, you must replace YourTable with the actual name of your
table, or query. Initially, do not change anything else. Let's see what
this does.

Tom Ellison


SP said:
Thank you very much for the quick reply. Well, the missing column is
likely the Date column. Right now, all I have is a simple table with 2
fields (Date and Deposit). From that table, it would be great to have a
query (or code as you mentioned) that generate the Balance and
PreviousBalance.

Date PreviousBalance Deposit Balance
7/06 0 1 1
8/06 1 5 6
9/06 6 2 8

Steve
Click to expand...
Click to expand...
 
Dear SP:

I made a mistake. That last < must be <=.

SELECT [Date],
(SELECT SUM(Deposit)
FROM YourTable T1
WHERE T1.[Date] < T.[Date])
AS PreviousBalance,
Deposit,
(SELECT SUM(Deposit)
FROM YourTable T1
WHERE T1.[Date] <= T.[Date])
AS Balance
FROM YourTable T
ORDER BY [Date]

Tom Ellison


SP said:
I think I just clicked on Reply by mistake (instead of Reply Group). I'm
resending my result here.

===============================
Hi Tom

With the table I got, I change its name to "YourTable" so that I can run
your code without modification. Here is the result.

Date PreviousBalance Deposit Balance
8/1/2006 1
9/1/2006 1 5 1
10/1/2006 6 2 6

I figured you could easily replicate my table as got the same result, and
I'm hoping that you can tweak it.

Thank you
Steve


Tom Ellison said:
Dear Steve:

Without the query code for which I asked you, I will just make some
guesses:

SELECT [Date],
(SELECT SUM(Deposit)
FROM YourTable T1
WHERE T1.[Date] < T.[Date])
AS PreviousBalance,
Deposit,
(SELECT SUM(Deposit)
FROM YourTable T1
WHERE T1.[Date] < T.[Date])
AS Balance
FROM YourTable T
ORDER BY [Date]

This will work well enough as long as there are not two deposits on the
same date. It will not be able to differentiate between two simultaneous
deposits, and they will be added into the Balance simultaneously. Unless
you have something else in the table that, when taken with [Date], can
order the rows uniquely, there may not be a solution to this, except
perhaps to add the Deposits together so there is only one shown per date.

In the query, you must replace YourTable with the actual name of your
table, or query. Initially, do not change anything else. Let's see what
this does.

Tom Ellison


SP said:
Thank you very much for the quick reply. Well, the missing column is
likely the Date column. Right now, all I have is a simple table with 2
fields (Date and Deposit). From that table, it would be great to have a
query (or code as you mentioned) that generate the Balance and
PreviousBalance.

Date PreviousBalance Deposit Balance
7/06 0 1 1
8/06 1 5 6
9/06 6 2 8

Steve


Dear Steve:

Perhaps, but there HAS to be another column you don't show which puts
these in the order you show. If the rows are not ordered uniquely,
then none of this actually makes sense (well, not to me).

Please write a Query that produces what you show with the exception of
the Balance column, and which sorts the results uniquely. I can then
add the code for you to show the Balance column.

Tom Ellison


PreviousBalance Deposit Balance
0 1 1
1 5 6
6 2 8


Hi

I'm looking for help on creating a query that can generate the
PreviousBalance column above. The PreviousBalance is basically
getting its
values from the Balance column and 1 record above.

Please help.
Thanks
Steve
Click to expand...
Click to expand...
Click to expand...
 
You are the man! It's exactly what I needed.

Thanks a million
Steve

Tom Ellison said:
Dear SP:

I made a mistake. That last < must be <=.

SELECT [Date],
(SELECT SUM(Deposit)
FROM YourTable T1
WHERE T1.[Date] < T.[Date])
AS PreviousBalance,
Deposit,
(SELECT SUM(Deposit)
FROM YourTable T1
WHERE T1.[Date] <= T.[Date])
AS Balance
FROM YourTable T
ORDER BY [Date]

Tom Ellison


SP said:
I think I just clicked on Reply by mistake (instead of Reply Group). I'm
resending my result here.

===============================
Hi Tom

With the table I got, I change its name to "YourTable" so that I can run
your code without modification. Here is the result.

Date PreviousBalance Deposit Balance
8/1/2006 1
9/1/2006 1 5 1
10/1/2006 6 2 6

I figured you could easily replicate my table as got the same result, and
I'm hoping that you can tweak it.

Thank you
Steve


Tom Ellison said:
Dear Steve:

Without the query code for which I asked you, I will just make some
guesses:

SELECT [Date],
(SELECT SUM(Deposit)
FROM YourTable T1
WHERE T1.[Date] < T.[Date])
AS PreviousBalance,
Deposit,
(SELECT SUM(Deposit)
FROM YourTable T1
WHERE T1.[Date] < T.[Date])
AS Balance
FROM YourTable T
ORDER BY [Date]

This will work well enough as long as there are not two deposits on the
same date. It will not be able to differentiate between two
simultaneous deposits, and they will be added into the Balance
simultaneously. Unless you have something else in the table that, when
taken with [Date], can order the rows uniquely, there may not be a
solution to this, except perhaps to add the Deposits together so there
is only one shown per date.

In the query, you must replace YourTable with the actual name of your
table, or query. Initially, do not change anything else. Let's see
what this does.

Tom Ellison


Thank you very much for the quick reply. Well, the missing column is
likely the Date column. Right now, all I have is a simple table with 2
fields (Date and Deposit). From that table, it would be great to have
a query (or code as you mentioned) that generate the Balance and
PreviousBalance.

Date PreviousBalance Deposit Balance
7/06 0 1 1
8/06 1 5 6
9/06 6 2 8

Steve


Dear Steve:

Perhaps, but there HAS to be another column you don't show which puts
these in the order you show. If the rows are not ordered uniquely,
then none of this actually makes sense (well, not to me).

Please write a Query that produces what you show with the exception of
the Balance column, and which sorts the results uniquely. I can then
add the code for you to show the Balance column.

Tom Ellison


PreviousBalance Deposit Balance
0 1 1
1 5 6
6 2 8


Hi

I'm looking for help on creating a query that can generate the
PreviousBalance column above. The PreviousBalance is basically
getting its
values from the Balance column and 1 record above.

Please help.
Thanks
Steve
Click to expand...
Click to expand...
Click to expand...
 
Back
Top