By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,676 Members | 1,193 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,676 IT Pros & Developers. It's quick & easy.

Year-to-Date Totals

P: n/a
Hi everyone! I'm trying to produce a periodic financial report on
projects from various departments. My database is set up with the
tables tblDepartment, tblProjects, tblPeriods, and tblBudgets -- the
first three serve the obvious function; the last table contains
foreign keys to tblProjects and tblPeriods, each record corresponding
to a project's budget for a particular period. Periods are also
grouped by years (a seperate field for simplity); each year has 12
periods, but they do not necessarily correspond with the months.

I am trying to produce a report that gives budgeting information for
all the projects in a particular department, for a certain period. I
need to display each project's current budget for the period, the
annual allotted budget, and the total budget from the beginning of the
year up to the current period (the "Year-to-Date"). The first two I
can manage, but I am stuck on the last one. Now I can efficiently
tally up a certain subset of all the budgets for a project in a year?

I've considered a few options:
(1) I could use a lookup function, but there are many projects and
that would be too slow.
(2) I tried toggling the "Running Sum" property on the textbox, but it
wants to tally up the budgets for all the projects within the same
deparmtment before it adds up the previous period's budgets.
(3) I could (try to) write a query that gives a Year-to-Date tally for
each project-period combination, but the query would become
pathologically slow as the database grows.
(4) Lastly, I could write a query that filters on an external variable
(perhaps via a getCurrentPeriod() function); this would seem to be the
best solution at the moment, but I lose the ability to call up
multiple Year-to-Date figures on the same report.

Does anyone have a better idea as to how I should go about producing
my Year-to-Date's? I gather this is a fairly common budgeting figure;
perhaps someone has done something similar?

I would greatly appreciate any input. Thanks.

Alan
Nov 13 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
Dear Alan

SQL, in my experience, is the fatest way to do any type of data manipulation
(much faster than any function).

If you want to make your queries even faster than you break them down into
several queries, which append to temporary tables. First of all you delete
all the data from the temporary table and then you add new data.

For example, you could write a query that simply adds all the relevant
dates/IDs into a table, from there, next, you do more calculations, etc....

For some of my reports (e.g. a daily planning report for a restaurant), I
use six queries before the final data source is compiled.

The key is to, first of all, reduce the size of your large data tables to
only the rows that are relevant, and from there do any computations.

Let me know how you get on.

- Nicolaas

I've considered a few options:
(1) I could use a lookup function, but there are many projects and
that would be too slow.
(2) I tried toggling the "Running Sum" property on the textbox, but it
wants to tally up the budgets for all the projects within the same
deparmtment before it adds up the previous period's budgets.
(3) I could (try to) write a query that gives a Year-to-Date tally for
each project-period combination, but the query would become
pathologically slow as the database grows.
(4) Lastly, I could write a query that filters on an external variable
(perhaps via a getCurrentPeriod() function); this would seem to be the
best solution at the moment, but I lose the ability to call up
multiple Year-to-Date figures on the same report.

Does anyone have a better idea as to how I should go about producing
my Year-to-Date's? I gather this is a fairly common budgeting figure;
perhaps someone has done something similar?

I would greatly appreciate any input. Thanks.

Alan
Nov 13 '05 #2

P: n/a
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:Jo******************@news.xtra.co.nz...
Dear Alan

SQL, in my experience, is the fatest way to do any type of data manipulation (much faster than any function).
Correct.

If you want to make your queries even faster than you break them down into
several queries, which append to temporary tables. First of all you delete
all the data from the temporary table and then you add new data.


I don't think you are right here. I/O is the weak link in data access - it's
orders of magnitude slower than in-memory processing, so the general rule of
thumb is to do as little of it as possible.

Also, I've never found "queries feeding queries" to be faster than a single
sql statement. Not to say it couldn't happen - but I've never seen it. The
major causes of poor query performance that I've seen are :

1. bad database design
2. poor indexing
3. bad sql code, e.g. restricting aggregated lists by using the having
clause instead of the where clause.



Nov 13 '05 #3

P: n/a
"Alan" <k2*******@sneakemail.com> wrote in message
news:e1**************************@posting.google.c om...

I am trying to produce a report that gives budgeting information for
all the projects in a particular department, for a certain period. I
need to display each project's current budget for the period, the
annual allotted budget, and the total budget from the beginning of the
year up to the current period (the "Year-to-Date"). The first two I
can manage, but I am stuck on the last one. Now I can efficiently
tally up a certain subset of all the budgets for a project in a year?

I've considered a few options:
(1) I could use a lookup function, but there are many projects and
that would be too slow.
(2) I tried toggling the "Running Sum" property on the textbox, but it
wants to tally up the budgets for all the projects within the same
deparmtment before it adds up the previous period's budgets.
(3) I could (try to) write a query that gives a Year-to-Date tally for
each project-period combination, but the query would become
pathologically slow as the database grows.
(4) Lastly, I could write a query that filters on an external variable
(perhaps via a getCurrentPeriod() function); this would seem to be the
best solution at the moment, but I lose the ability to call up
multiple Year-to-Date figures on the same report.


IMO (3) is the way to go. If you have a good design you don't need to worry
about things slowing down - databases, (even Access), are awesomely fast at
aggregating records, even when they have millions of rows to deal with.
Nov 13 '05 #4

P: n/a
what I meant was one query creating a table and this query then using the
new table. I found this idea of breaking down queries into several steps to
be very fast.

For the complex reports that I have I do this as follows:
1. reduce the size of the table to only those records that are required
2. use this temporary table to do some calculations (e.g. aggregate costs)
3. link the next table created with all the 'lookup tables' in order to use
names (rather than IDs) in my final report

This works, in my experience, much faster than putting all this into one
single query.
Nov 13 '05 #5

P: n/a
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:2_******************@news.xtra.co.nz...
what I meant was one query creating a table and this query then using the
new table. I found this idea of breaking down queries into several steps to be very fast.

For the complex reports that I have I do this as follows:
1. reduce the size of the table to only those records that are required
2. use this temporary table to do some calculations (e.g. aggregate costs)
3. link the next table created with all the 'lookup tables' in order to use names (rather than IDs) in my final report

This works, in my experience, much faster than putting all this into one
single query.


In that case then yes, you're right, temp tables are often the best
solution. One trick, (i learned from this ng in fact), is to create
temporary tables in a separate database, (a temporary database IOW), which
you create when your app starts. This saves you from bloating your
application unneccesarily.

Another trick, (lots of tricks here), is to use this temporary database with
an ADP file to give you local relational storage when working with sql
server data. You can even bind forms in an ADP to a local recordset in this
way.
Nov 13 '05 #6

P: n/a
Hey John

That last paragraph sounds very interesting.

Could you explain this in some more detail. I am not sure that I understand
what you mean (what is an ADP file???)
Thank you.

- Nicolaas
Nov 13 '05 #7

P: n/a
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:9Y******************@news.xtra.co.nz...
Hey John

That last paragraph sounds very interesting.

Could you explain this in some more detail. I am not sure that I understand what you mean (what is an ADP file???)

Access actually consists of two distinct parts; a UI containing the forms,
reports and VBA code, and an underlying database engine, (called MS Jet).
Although you can link to tables in other dbms's using ODBC, the UI is always
"connected" permanently to a Jet database, whether you use it or not.

From Access 2K onwards MS gives the choice of using Jet or SQL Server, (or
SQL Server's stripped-down cousin, MSDE) as the underlying database. If you
choose to bind to SQL Server the file becomes an Access Project, (given the
extension ADP by default). Sounds great. The idea is to allow users to
manipulate SQL Server objects, (tables, views, stored procedures etc)
through the UI with the same ease as they can manipulate Jet objects when
bound to the traditional Jet database engine, and, perhaps more importantly,
to bind forms and reports to those objects. Unfortunately this creates as
many problems as it solves. One of these problems is the lack of local data
storage. A big strength of "traditional" Access for developing server-based
database applications is the ability to bring data back to the client, and
to store and manipulate it in local tables. A simple example is so-called
lookup tables. Instead of having to query the server each time you need a
list that deson't change, you can load the list once at startup, then query
the local list next time. With ADP's you can't do that, (well, not easily).
Another common use for local storage is for complex data entry. Let's say
your user is a laboratory and they are entering a list of samples that they
wish to analyze. For each sample they must enter all the supporting
information; location, customer name, type of sample etc. Lots of data
entry. If you have to keep sending this to the server each time they make an
entry it slows things down - sometimes substantially. With local tables you
can have all the data entered and stored locally, and then batch it back to
the server one time.

Using local, temporary Jet databases essentially gives you the best of both
worlds. You can build an ADP project, connect it to a SQL Server database,
and still bring data back and store it on the client. And you store it in a
client-side relational database - no mucking about with file storage.
Nov 13 '05 #8

P: n/a
Dear John

I really appreciate your answer. Very interesting. I did understand your
writing, but unfortunately, I still do not understand everything.

Are you saying that these are your three options for your access database
'back-end':
a. Jet
b. SQLserver
c. Some mix (which is the best idea).

Thank you once more

- Nicolaas
Nov 13 '05 #9

P: n/a
Alan,
It's almost always a good idea to attack problems like this as data
warehouse, or star-schema problems and not relational problems. What you
need to ensure you capture is an effective, accurate and stable snapshot of
the business. This is where data-warehouses work better. So, identify your
dimensions--you state Department, Project, & Time. Then figure out the
measured fact(s), perhaps allocated budget verses actual dollars spent.
After that there is some work to build a middleware piece that does the etl
step from relational to your data-warehouse. And a rather huge bit to
design & build a reporting front end. But the results are worth it. There
are lots of ways to screw this up as a relational/transaction processing
solution. Go with a star schema.

"Alan" <k2*******@sneakemail.com> wrote in message
news:e1**************************@posting.google.c om...
Hi everyone! I'm trying to produce a periodic financial report on
projects from various departments. My database is set up with the
tables tblDepartment, tblProjects, tblPeriods, and tblBudgets -- the
first three serve the obvious function; the last table contains
foreign keys to tblProjects and tblPeriods, each record corresponding
to a project's budget for a particular period. Periods are also
grouped by years (a seperate field for simplity); each year has 12
periods, but they do not necessarily correspond with the months.

I am trying to produce a report that gives budgeting information for
all the projects in a particular department, for a certain period. I
need to display each project's current budget for the period, the
annual allotted budget, and the total budget from the beginning of the
year up to the current period (the "Year-to-Date"). The first two I
can manage, but I am stuck on the last one. Now I can efficiently
tally up a certain subset of all the budgets for a project in a year?

I've considered a few options:
(1) I could use a lookup function, but there are many projects and
that would be too slow.
(2) I tried toggling the "Running Sum" property on the textbox, but it
wants to tally up the budgets for all the projects within the same
deparmtment before it adds up the previous period's budgets.
(3) I could (try to) write a query that gives a Year-to-Date tally for
each project-period combination, but the query would become
pathologically slow as the database grows.
(4) Lastly, I could write a query that filters on an external variable
(perhaps via a getCurrentPeriod() function); this would seem to be the
best solution at the moment, but I lose the ability to call up
multiple Year-to-Date figures on the same report.

Does anyone have a better idea as to how I should go about producing
my Year-to-Date's? I gather this is a fairly common budgeting figure;
perhaps someone has done something similar?

I would greatly appreciate any input. Thanks.

Alan

Nov 13 '05 #10

P: n/a
Dear Mr Webb (as opposed to the other Alan)

I am really interested in your data warehousing story. Could you please
translate that into plain English.

Is the main idea that you should calculate these values and then store them
rather than calculating them on the fly when required?

And if so, does anyone have any views on this??? I think that is very
interesting idea. I am interested in:
a. when and where do you use such techniques
b. what are the advantages/disadvantages
c. how do you explain the differences to users (they just want to know the
results, not how it works).
d. etc....

Let me know

- Nicolaas

- Nicolaas
Nov 13 '05 #11

P: n/a
Mr Webb, after you mentioned star schemas and data warehouses, I went
and look up some references on the subjects. They were very
insightful. Thanks.

It turns out that I have already incorporated some aspects of the star
schema in my database design. The tblBudgets table is actually a fact
table and the other tables, tblDepartments, tblProjects, tblPeriods
are dimensional tables. The design is relatinal in nature, but seems
to conform to a star schema. There are other tables as well, but they
are outside the scope of the immediate problem.

I am unclear as to your reference to a middleware though. I'm using
MS Access (as evident by my choice of newsgroups :) and we will be on
a simple front-end / back-end construct. Are you recommending that I
use another database management system?

As for a data warehouse, I wish we were getting information live or in
snapshot form from other database within the company. But as it is,
we are getting a flat, txt dump from the company mainframe on a
regular interval. Not quite as exciting, but at least I don't have to
deal with interoperability issues (not as many, at least).

So say I have a star schema set-up. What then? I am still having
problems with getting a Year-to-Date efficiently. I have a feeling
you are going to suggest that I go with option (3), writing an SQL
query that gives every single possible Year-to-Date value, for every
tuplet of Project x Deparmtnet x Time. Would efficiency not be a
problem if the database is used for an extended period of time?

Thanks,

Alan

P.S. Like Nicholaas, I think it would be benefitial to see a more
generic analysis of star schemas and data warehouses in general too.
"WindAndWaves" <ac****@ngaru.com> wrote in message news:<PU******************@news.xtra.co.nz>...
Dear Mr Webb (as opposed to the other Alan)

I am really interested in your data warehousing story. Could you please
translate that into plain English.

Is the main idea that you should calculate these values and then store them
rather than calculating them on the fly when required?

And if so, does anyone have any views on this??? I think that is very
interesting idea. I am interested in:
a. when and where do you use such techniques
b. what are the advantages/disadvantages
c. how do you explain the differences to users (they just want to know the
results, not how it works).
d. etc....

Let me know

- Nicolaas

- Nicolaas

Nov 13 '05 #12

P: n/a
Alan,
I am unclear as to your reference to a middleware though. I'm using
MS Access (as evident by my choice of newsgroups :) and we will be on
a simple front-end / back-end construct. Are you recommending that I
use another database management system? First, middleware. There are a great number of cases where the source data
for a data warehouse is not in a form suitable for storage in a star schema.
It may not even be in a database or spreadsheet. So, middleware is the
piece of software (either a packaged solution or a piece of custom code)
that does the extract, transform and load steps to take the data from its
source and post it to a star schema for publication and consumption by the
users. I'd actually like the acronym to be ETVL because a missing step is
Validate--ensure that the data to be loaded is accurate.
P.S. Like Nicholaas, I think it would be benefitial to see a more
generic analysis of star schemas and data warehouses in general too. Second, data warehouses in general. This is a huge topic and a big shift in
thinking from the original relational database design theories of Boyce &
Codd. But I'll gloss over the basics in a couple paragraphs. The mission
of a datawarehouse is to accurately capture historic measures of the
performance of a business. So, all the data in a datawarehouse is past
history. And the data that is kept is reformatted so it can be stored as
either descriptors by which the numbers can be viewed (dimensions) or
numeric measures of performance (facts). Now, because this is history that
is getting posted it is critical that today's numbers on a month end are the
same today and fifty years from now. Our user community has to trust that
the closed month end numbers won't change. So one key change in thinking is
that the numeric facts are stored as calculated results in a table. In a
datawarehouse it is a bad idea to build views that dynamically calculate the
numeric measures at run time. Transaction processing or relational database
would have you building a view that presented the calculated numeric facts
each time it is run. But since data is constantly pouring in to the
transaction system and changing the results presented in the views of our
numeric facts it is impossible to say with any credibility that the
month-end view run today is giving the same result as the month end view run
a couple days ago. So a dba running a datawarehouse would spend some time
understanding what measures the user community wants so they can understand
how the business is doing and then build a set of dimension and fact tables
in a star schema that will store the end results of our month end view as
rows in the fact table.
Third and last, there are typically two sometimes competing sets of
requirements for databases. The first is to capture the data stream
generated by whatever transactions a business performs. In this purpose the
goal is transaction processing speed so anything the database has to do that
would slow down its ability to capture transactions is a problem. Thus the
need to beat redundant data out of the schema. The reason you don't want to
store calculated results in a transaction processing system is that the data
is constantly changing and you need to be able to present the most current
results. The second purpose is reporting of history and this is where
reformatting the data into a star schema that captures periodic snapshots of
historic measures of the business becomes useful.
So say I have a star schema set-up. What then? I am still having
problems with getting a Year-to-Date efficiently. I have a feeling
you are going to suggest that I go with option (3), writing an SQL
query that gives every single possible Year-to-Date value, for every
tuplet of Project x Deparmtnet x Time. Would efficiency not be a
problem if the database is used for an extended period of time?


It depends on what would best support the reporting needs of your users.
But the short answer is, yes.
Is the main idea that you should calculate these values and then store them rather than calculating them on the fly when required? Yes. The purpose is to store historic data so calculating on the fly is
considered a bad idea.
b. what are the advantages/disadvantages There are many answers to this. Go look for a book on data warehouses.
Most of the problems have well documented solutions. c. how do you explain the differences to users (they just want to know the results, not how it works).

Tell the users their historic data will run cheaper, better & faster as well
as become more trustworthy.
Nov 13 '05 #13

P: n/a
That is a great concept Alan

I believe a lot of accounting programs actually use the warehouse concept,
once you post an item, it is stored as historic data and it does not get in
the way of the current data or something along those lines.
Nov 13 '05 #14

P: n/a
Wind and Waves,
Analysis Services are the way Microsoft has implemented data warehouse
features in SQL Server 2000. It's a nice implementation that can greatly
ease the dba's job in setting up and maintaining a data warehouse. Oracle
has a similar product the name of which escapes me as I type this. Given a
choice, I'd work with a packaged software to help me set up my dimensions &
facts. You can roll your own but the effort is often harder & more time
consuming that it might seem at the outset.

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:VT*******************@news.xtra.co.nz...
That is a great concept Alan

I believe a lot of accounting programs actually use the warehouse concept,
once you post an item, it is stored as historic data and it does not get in the way of the current data or something along those lines.

Nov 13 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.