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) 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)
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)
----------------------------------------------------------------------------
"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?
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)
----------------------------------------------------------------------------
"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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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).
|
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.
|
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
|
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...
|
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.
| |
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
|
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)...
|
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?
|
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
|
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...
|
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...
| |
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...
|
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,...
|
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...
|
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...
|
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |