Finances on an Access Database

  • Thread starter Thread starter John Ortt
  • Start date Start date
J

John Ortt

I have created an Access Database to store information from my various bank
accounts and keep track of my finances.

Everything works great, except that I decided not to keep the balance data
on the sheet, only the transaction amount.

I figured it was easier to just add all the previous transaction amounts to
get the balance on any given date (and a single balance adjustment at the
start of the data).

I am regretting this choice now as the code I have created to do it takes
ages......

Does anybody have any bright ideas on how I could do it? (I have included
my code below for an example)

----------------------------------------------------------------------------
--

SELECT [Q:Data].TransDate, Sum([Q:Data].[TransAmount]*-1)
AS Amount, -1*
(SELECT SUM([TransAmount])
FROM [Q:Data] as [Q:Data_1]
WHERE [Q:Data_1].[TransDate] <= [Q:Data].[TransDate]
and [Q:Data_1].[Account]=[Q:Data].[Account])
AS Balance, [Q:Data].Account
FROM [Q:Data]
GROUP BY [Q:Data].TransDate, [Q:Data].Account;

(I then crosstab the returned data so I can chart it)
 
John,

I see you are using a subquery. Dollars to Donuts, that's what is slowing your
query down to a crawl. See if you can do what you want another way including
redesigning your tables to elimiate the subquery.
 
John Ortt said:
I have created an Access Database to store information from my various bank
accounts and keep track of my finances.

Everything works great, except that I decided not to keep the balance data
on the sheet, only the transaction amount.

I figured it was easier to just add all the previous transaction amounts to
get the balance on any given date (and a single balance adjustment at the
start of the data).

I am regretting this choice now as the code I have created to do it takes
ages......

Does anybody have any bright ideas on how I could do it? (I have included
my code below for an example)
Click to expand...


Do you have indexes on the tranDate and account columns?
 
Others here have indicated their agreement with your approach, but that's
actually not the way accounting database systems usually work. The problem
with adding up all transactions to get a current balance is that you must read
-all- the rows to get a balance, and the number of rows grows linearly with
time. No matter how fast it is at any given point, it will be continually
slower from that point forward.

What most systems do is have a table of current fiscal period data, and
included in that is a single balance-forward record that is the balance at the
end of the last previously closed fiscal period. When calculating a current
balance, you do query all the records from the open period, but not the
archived records from previous periods. Fiscal periods are usually in
quarters.
 
Steve Jorgensen said:
Others here have indicated their agreement with your approach, but that's
actually not the way accounting database systems usually work. The problem
with adding up all transactions to get a current balance is that you must read
-all- the rows to get a balance, and the number of rows grows linearly with
time. No matter how fast it is at any given point, it will be continually
slower from that point forward.
Click to expand...

"Slower" is a relative term. For a personal finance database I doubt we are
talking more than a few tens of thousands of transactions. This is nothing
for Access, even with subqeuries that aggregate many rows. If the present
design works, apart from being slow, then adding checkpointing capability is
probably overkill and not worth the effort. Adding one or two indexes on the
other hand takes about 10 seconds.
 
On Wed, 28 Apr 2004 16:26:18 GMT, Steve Jorgensen

What most systems do is have a table of current fiscal period data, and
included in that is a single balance-forward record that is the balance at the
end of the last previously closed fiscal period. When calculating a current
balance, you do query all the records from the open period, but not the
archived records from previous periods. Fiscal periods are usually in
quarters.
Click to expand...

Most newer systems do not store totals, and do not force you to close
out periods. QuickBooks is a simple example.

Storing totals makes running non-standard monthly reports, comparative
reports, reports for certain divisions or transaction types, etc ...
more difficult.

The exception is systems that allow you to purge historical
treansacitons will generate records to reflect the total impact of the
transactions.

Most companies use month-ends (can be actually month-end or last
business day, or other variations) as their "periods".

Steven R. Zuch
Cogent Management Inc.
 
Back
Top