query to calculate balance on bank statement

[aa]

I am entering my bank statement into a table.
The table has two columes: DATE, VALUE,
I want a query which would add the third colume BALANCE and calculate it
automatically, i.e. for each line it should take BALANCE from the previous
line and add VALUE from the current line.
How would such a query look like?

 

[Ken Snell MVP]

Do you have a primary key field in your table? You will want one (preferably
an Autonumber field, set to Increment) for making this more feasible.

Let us know what the primary key field name is.

 

[aa]

No probs to add a primary key, let's call it ID.
What will be the query?

 

[Ken Snell MVP]

SELECT T.[DATE], T.[VALUE],
(SELECT SUM(TT.[VALUE]) AS TTV
FROM TableName AS TT
WHERE TT.ID <= T.ID AND
TT.[DATE] <= T.[DATE]) AS [Balance]
FROM TableName AS T;

Replace TableName with the real name of the table.


Also, note that you should not name a field "Date" or "Value". They and many
other words
are reserved words in ACCESS and should not be used for control names, field
names, etc. Allen Browne (MVP) has a very comprehensive list of reserved
words at his website:

Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html


See these Knowledge Base articles for more information about reserved words
and characters that should not be used (these articles are applicable to
ACCESS 2007 as well):

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

 

[aa]

Thank you very much