Help with running balance

It worked Bob thanks very much

Johnny

Hi Ken,

Once more I request your help!
I used the sugestion you gave me and with Bob Barrows help I could
make it work!
That way, the query returns the Balance of all transactions in
Transactions table. What I need is that the query return all all
transactions in Transactions table for a specific person (ClientID).

Could the following be a solution??
SELECT TransDate, TransValue, ClientID,
DSum("TransValue", "Transactions",
"TransDate = #" & Format(T1.TransDate, "yyyy-mm-dd") &
"# And (TransactionID = " & T1.TransactionID &
" Or TransDate #" & Format(T1.TransDate, "yyyy-mm-dd") & "#)
And T1.ClientID = [Forms]![MyForm]![MyControl]")
AS Balance
FROM Transactions AS T1
ORDER BY TransDate DESC , TransactionID DESC;

[Forms]![MyForm]![MyControl] is a combo box returning a number!

Thanks once more for the suport
Johnny

SELECT TransDate, TransValue, ClientID,
DSum("TransValue", "Transactions",
"TransDate = #" & Format(T1.TransDate, "yyyy-mm-dd") &
"# And (TransactionID = " & T1.TransactionID &
" Or TransDate #" & Format(T1.TransDate, "yyyy-mm-dd") & "#)
And ClientID =" & T1.ClientID)
AS Balance
FROM Transactions AS T1
ORDER BY TransDate DESC , TransactionID DESC;

If you are trying to get all this for one specific client, you might change
the query to

SELECT TransDate, TransValue, ClientID,
DSum("TransValue", "Transactions",
"TransDate = #" & Format(T1.TransDate, "yyyy-mm-dd") &
"# And (TransactionID = " & T1.TransactionID &
" Or TransDate #" & Format(T1.TransDate, "yyyy-mm-dd") & "#)
And ClientID =" & T1.ClientID) AS Balance
FROM Transactions AS T1
WHERE T1.ClientID = [Forms]![MyForm]![MyControl]
ORDER BY TransDate DESC , TransactionID DESC;

Hopefully all the needed parentheses are in the correct place.

Alternative would be to use a subquery to get the Balance
SELECT TransDate, TransValue, ClientID
, (SELECT SUM(TransValue) FROM Transactions as T
WHERE T.ClientID = T1.ClientID
AND (T.TransDate = T1.TransDate
AND T.TransactionID=T1.TransactionID
Or T.TransDate T1.TransDate) As Balance
FROM Transactions AS T1
WHERE T1.ClientID = [Forms]![MyForm]![MyControl]
ORDER BY TransDate DESC , TransactionID DESC;



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Johnny wrote:
Hi Ken,

Once more I request your help!
I used the sugestion you gave me and with Bob Barrows help I could
make it work!
That way, the query returns the Balance of all transactions in
Transactions table. What I need is that the query return all all
transactions in Transactions table for a specific person (ClientID).

Could the following be a solution??
SELECT TransDate, TransValue, ClientID,
DSum("TransValue", "Transactions",
"TransDate = #" & Format(T1.TransDate, "yyyy-mm-dd") &
"# And (TransactionID = " & T1.TransactionID &
" Or TransDate #" & Format(T1.TransDate, "yyyy-mm-dd") & "#)
And T1.ClientID = [Forms]![MyForm]![MyControl]")
AS Balance
FROM Transactions AS T1
ORDER BY TransDate DESC , TransactionID DESC;

[Forms]![MyForm]![MyControl] is a combo box returning a number!

Thanks once more for the suport
Johnny

Hi John,

I used what you sugested:

SELECT TransDate, TransValue, ClientID,
DSum("TransValue", "Transactions",
"TransDate = #" & Format(T1.TransDate, "yyyy-mm-dd") &
"# And (TransactionID = " & T1.TransactionID &
" Or TransDate #" & Format(T1.TransDate, "yyyy-mm-dd") & "#)
And ClientID =" & T1.ClientID) AS Balance
FROM Transactions AS T1
WHERE T1.ClientID = [Forms]![MyForm]![MyControl]
ORDER BY TransDate DESC , TransactionID DESC;

and it worked like a charm!
Thank you very much for your suport
Johnny

This thread has been very helpful to me. However, I have a slightly different need. Instead of feeding the specific ClientID from a separate form, I just need the query to have the running balance by date but tracked per each ClientID. Is there some small modification to this that would make that work?

For example

Date ClientID Transvalue Balance
01/01/09 client1 23 23
01/02/09 client2 12 12
01/06/09 client1 05 28
01/06/09 client3 09 09
01/08/09 client3 14 23
01/20/09 client1 04 32




Johnny wrote:

Help with running balance
10-Jan-09

Hi John,

I used what you sugested:

SELECT TransDate, TransValue, ClientID,
DSum("TransValue", "Transactions",
"TransDate = #" & Format(T1.TransDate, "yyyy-mm-dd") &
"# And (TransactionID = " & T1.TransactionID &
" Or TransDate #" & Format(T1.TransDate, "yyyy-mm-dd") & "#)
And ClientID =" & T1.ClientID) AS Balance
FROM Transactions AS T1
WHERE T1.ClientID = [Forms]![MyForm]![MyControl]
ORDER BY TransDate DESC , TransactionID DESC;

and it worked like a charm!
Thank you very much for your suport
Johnny

EggHeadCafe - Software Developer Portal of Choice
Speed Up Sql Server Data Inserts With SqlBulkCopy
http://www.eggheadcafe.com/tutorials...rver-data.aspx

This thread has been very helpful to me. However, I have a slightly different need. Instead of feeding the specific ClientID from a separate form, I just need the query to have the running balance by date but tracked per each ClientID. Is there some small modification to this that would make that work?

For example

Date ClientID Transvalue Balance
01/01/09 client1 23 23
01/02/09 client2 12 12
01/06/09 client1 05 28
01/06/09 client3 09 09
01/08/09 client3 14 23
01/20/09 client1 04 32




Johnny wrote:

Help with running balance
10-Jan-09

Hi John,

I used what you sugested:

SELECT TransDate, TransValue, ClientID,
DSum("TransValue", "Transactions",
"TransDate = #" & Format(T1.TransDate, "yyyy-mm-dd") &
"# And (TransactionID = " & T1.TransactionID &
" Or TransDate #" & Format(T1.TransDate, "yyyy-mm-dd") & "#)
And ClientID =" & T1.ClientID) AS Balance
FROM Transactions AS T1
WHERE T1.ClientID = [Forms]![MyForm]![MyControl]
ORDER BY TransDate DESC , TransactionID DESC;

and it worked like a charm!
Thank you very much for your suport
Johnny

EggHeadCafe - Software Developer Portal of Choice
ASP.NET: Hamburger, Yes! AJAX, "NOT!", Redux!
http://www.eggheadcafe.com/tutorials...urger-yes.aspx

This thread has been very helpful to me. However, I have a slightly different need. Instead of feeding the specific ClientID from a separate form, I just need the query to have the running balance by date but tracked per each ClientID. Is there some small modification to this that would make that work?

For example

Date ClientID Transvalue Balance
01/01/09 client1 23 23
01/02/09 client2 12 12
01/06/09 client1 05 28
01/06/09 client3 09 09
01/08/09 client3 14 23
01/20/09 client1 04 32



Johnny wrote:

Help with running balance
10-Jan-09

Hi John,

I used what you sugested:

SELECT TransDate, TransValue, ClientID,
DSum("TransValue", "Transactions",
"TransDate = #" & Format(T1.TransDate, "yyyy-mm-dd") &
"# And (TransactionID = " & T1.TransactionID &
" Or TransDate #" & Format(T1.TransDate, "yyyy-mm-dd") & "#)
And ClientID =" & T1.ClientID) AS Balance
FROM Transactions AS T1
WHERE T1.ClientID = [Forms]![MyForm]![MyControl]
ORDER BY TransDate DESC , TransactionID DESC;

and it worked like a charm!
Thank you very much for your suport
Johnny

EggHeadCafe - Software Developer Portal of Choice
Moving to VB.NET - Appelman [aPress]
http://www.eggheadcafe.com/tutorials...t--appelm.aspx

Using joins:


SELECT a.date, a.clientID, LAST(a.transvalue), SUM(b.transvalue)
FROM tableName As a INNER JOIN tableName AS b
ON a.clientID=b.clientID AND a.date= b.date
GROUP BY a.date, a.clientID

ORDER BY a.clientID, a.date


The Order by clause is optional, and can be used to help to validate the
result.


Vanderghast, Access MVP



"Michael Strite" wrote in message ...
This thread has been very helpful to me. However, I have a slightly
different need. Instead of feeding the specific ClientID from a separate
form, I just need the query to have the running balance by date but
tracked per each ClientID. Is there some small modification to this that
would make that work?

For example

Date ClientID Transvalue Balance
01/01/09 client1 23 23
01/02/09 client2 12 12
01/06/09 client1 05 28
01/06/09 client3 09 09
01/08/09 client3 14 23
01/20/09 client1 04 32




Johnny wrote:

Help with running balance
10-Jan-09

Hi John,

I used what you sugested:

SELECT TransDate, TransValue, ClientID,
DSum("TransValue", "Transactions",
"TransDate = #" & Format(T1.TransDate, "yyyy-mm-dd") &
"# And (TransactionID = " & T1.TransactionID &
" Or TransDate #" & Format(T1.TransDate, "yyyy-mm-dd") & "#)
And ClientID =" & T1.ClientID) AS Balance
FROM Transactions AS T1
WHERE T1.ClientID = [Forms]![MyForm]![MyControl]
ORDER BY TransDate DESC , TransactionID DESC;

and it worked like a charm!
Thank you very much for your suport
Johnny

EggHeadCafe - Software Developer Portal of Choice
ASP.NET: Hamburger, Yes! AJAX, "NOT!", Redux!
http://www.eggheadcafe.com/tutorials...urger-yes.aspx

Try removing the query's WHERE clause.
--
Marsh
MVP [MS Access]


Michael Strite wrote:
This thread has been very helpful to me. However, I have a slightly different need. Instead of feeding the specific ClientID from a separate form, I just need the query to have the running balance by date but tracked per each ClientID. Is there some small modification to this that would make that work?

For example

Date ClientID Transvalue Balance
01/01/09 client1 23 23
01/02/09 client2 12 12
01/06/09 client1 05 28
01/06/09 client3 09 09
01/08/09 client3 14 23
01/20/09 client1 04 32



Johnny wrote:

Help with running balance
10-Jan-09

Hi John,

I used what you sugested:

SELECT TransDate, TransValue, ClientID,
DSum("TransValue", "Transactions",
"TransDate = #" & Format(T1.TransDate, "yyyy-mm-dd") &
"# And (TransactionID = " & T1.TransactionID &
" Or TransDate #" & Format(T1.TransDate, "yyyy-mm-dd") & "#)
And ClientID =" & T1.ClientID) AS Balance
FROM Transactions AS T1
WHERE T1.ClientID = [Forms]![MyForm]![MyControl]
ORDER BY TransDate DESC , TransactionID DESC;

and it worked like a charm!
Thank you very much for your suport
Johnny

EggHeadCafe - Software Developer Portal of Choice
Moving to VB.NET - Appelman [aPress]
http://www.eggheadcafe.com/tutorials...t--appelm.aspx

Many thanks Marshall & vanderghast for your attempts to help a
newbie... I hesitate to bother you but I seem to be stuck again.
When I run my query with the following query field included, it gives
me a syntax error. I am sure that I am missing some critical
paretheses but for the life of me I cannot seem to figure out where to
put them. I am still trying to figure out the logic behind these SQL
queries... Forgive my ignorance but can you tell me what I need to
change with the following statement.


Balance: SELECT a.TransDate, a.CustID, LAST(a.ChargeAmount), SUM
(b.ChargeAmount)
FROM Accounts As a INNER JOIN Accounts AS b
ON a.CustID=b.CustID AND a.TransDate= b.TransDate
GROUP BY a.TransDate, a.CustID
ORDER BY a.CustID, a.TransDate