473,698 Members | 2,360 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Finances on an Access Database

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)


Nov 12 '05 #1
6 2167
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******@pcdata sheet.com
www.pcdatasheet.com
"John Ortt" <Jo******@Idont wantspamsonoret urnaddress.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)



Nov 12 '05 #2
On Wed, 28 Apr 2004 12:02:14 +0100, "John Ortt"
<Jo******@Idont wantspamsonoret urnaddress.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)

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


Nov 12 '05 #3
"John Ortt" <Jo******@Idont wantspamsonoret urnaddress.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?
Nov 12 '05 #4
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******@Idont wantspamsonoret urnaddress.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)

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


Nov 12 '05 #5
"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:nn******** *************** *********@4ax.c om...
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.

Nov 12 '05 #6
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.
Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
300
by: John Ortt | last post by:
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).
11
17053
by: Rosco | last post by:
Does anyone have a good URL or info whre Oracle and Access are compared to one another in performance, security, cost etc. Before you jump on me I know Oracle is a Cadillac compared to Access the Ford Fairlane. I need this info to complete a school project. Thanks.
64
5235
by: John | last post by:
Hi What future does access have after the release of vs 2005/sql 2005? MS doesn't seem to have done anything major with access lately and presumably hoping that everyone migrates to vs/sql. Any comments? Thanks
52
9968
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server 2005, and, since he already has licenses for Office Pro 2002, he wants to upgrade to that. I've been saying that we need to upgrade to Access 2003, not 2002, even if Office is kept at 2002. We are also looking to do a fair amount of...
15
4606
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to communicate with a MySQL database table on a web server, from inside of my company's Access-VBA application. I know VBA pretty well but have never before needed to do this HTTP/XML/MySQL type functions.
49
3221
by: Mell via AccessMonster.com | last post by:
I created databases on Access 2003 and I want to deploy them to users. My code was also done using 2003. If they have Ms Access 2000 or higher, will they be able to use these dbs with all code, etc? Please explain -- Message posted via http://www.accessmonster.com
17
4408
by: Mell via AccessMonster.com | last post by:
Is there a way to find out where an application was created from? i.e. - work or home i.e. - if application sits on a (work) server/network, the IT people know the application is sitting there, but is there a way they can find out if that application was put there from a CD or email or created at work? Hint: It's not on a client/server database, just native jet database mdb created on Access 2003 (default 2000)...
2
4272
by: JayDawg | last post by:
I went to the Microsoft Templates page to see if there is a database template for tracking and reporting on personal finances, but there is nothing good, does anyone have one, or know where I can download a good database for this?
11
2399
by: Harel | last post by:
I have a report SQL which have been working for a few weeks. Its large and the target database is large. It has 59 join, and I dont think there is anything I can do about this, since the DB I use has been normalized by pros. My problem is I try to had another join to display another value, then I get a crash report from MS-Access. Anything I can to do diagnose this problem. My development depends on this SQL: SELECT
0
8678
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9166
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9030
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8899
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8871
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
4371
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3052
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2333
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2007
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.