Finances on an Access Database

[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)

[PC Datasheet]

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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"John Ortt" <Jo******@Idontwantspamsonoreturnaddress.com> wrote in message
news:40**********@baen1673807.greenlnk.net...

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)

[Tom van Stiphout]

On Wed, 28 Apr 2004 12:02:14 +0100, "John Ortt"
<Jo******@Idontwantspamsonoreturnaddress.com> wrote:

Sounds like you have the correct design, which frowns on storing
calculated data. Make sure you have indexes on the fields used in any
group by and where clause.

-Tom.

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)

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

[John Winterbottom]

"John Ortt" <Jo******@Idontwantspamsonoreturnaddress.com> wrote in message
news:40**********@baen1673807.greenlnk.net...

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)

Do you have indexes on the tranDate and account columns?

[Steve Jorgensen]

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.

On Wed, 28 Apr 2004 12:02:14 +0100, "John Ortt"
<Jo******@Idontwantspamsonoreturnaddress.com> wrote:

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)

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

[John Winterbottom]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:nn********************************@4ax.com...

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.

"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.

[Steve]

On Wed, 28 Apr 2004 16:26:18 GMT, Steve Jorgensen
<no****@nospam.nospam> wrote:

<snip>

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.

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.