年初至今的总计 [英] Year-to-Date Totals

问题描述

大家好！我正在尝试从各个部门制作一份关于

项目的定期财务报告。我的数据库设置了

表tblDepartment，tblProjects，tblPeriods和tblBudgets -

前三个服务于明显的功能;最后一个表包含

tblProjects和tblPeriods的外键，每个记录对应一个项目特定时期的预算。期间也按年份分组（为简单起见，单独的字段）;每年有12个
期间，但它们不一定与月份一致。


我正在尝试生成一份报告，为
特定部门的所有项目，在一段时间内。我需要显示每个项目当期的预算，

年度分配预算，以及从
$开始的总预算截至当期的b $ b年（年初至今）。前两个我可以管理，但是我被困在最后一个。现在我可以有效地计算一年内项目所有预算的某个子集吗？


我考虑了几个选项：

（1）我可以使用查找功能，但有很多项目和

太慢了。

（2）我试过切换运行总和文本框上的属性，但是它想要计算同一个

清单中所有项目的预算，然后再加上前一个时期的预算。

（3）我可以（尝试）编写一个查询，给出每个项目期间组合的年初至今$ t $ b，但查询将变为

随着数据库的增长在病理上变慢。

（4）最后，我可以编写一个过滤外部变量的查询

（可能通过getCurrentPeriod （）函数）;这似乎是目前最好的解决方案，但是我失去了在同一份报告中调用多个年初至今数据的能力。


有没有人能更好地了解我应该如何制作

我的年初至今？我认为这是一个相当普遍的预算数字;

也许有人做过类似的事情？


我会非常感谢任何输入。谢谢。


艾伦

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

推荐答案

亲爱的艾伦


根据我的经验，SQL是进行任何类型数据操作的最佳方式

（比任何函数快得多）。


如果你想制作您的查询甚至比将它们分解为几个查询更快，后者附加到临时表。首先，你从临时表中删除

所有数据，然后添加新数据。


例如，你可以编写一个简单添加的查询将所有相关的

日期/ ID放入表中，然后再进行更多计算等....


对于我的一些报告（例如，餐馆的每日计划报告），我会在编制最终数据源之前使用六个查询。


关键是，首先是所有，将大数据表的大小减小到

只有相关的行，并从那里进行任何计算。


让我知道你是怎么做的继续。


- Nicolaas


我考虑了几个选择：

（1）I可以使用查找功能，但有很多项目和

太慢了。

（2）我尝试切换运行总和。文本框上的属性，但是它想要计算同一个

清单中所有项目的预算，然后再加上前一个时期的预算。

（3）我可以（尝试）编写一个查询，给出每个项目期间组合的年初至今$ t $ b，但查询将变为

随着数据库的增长在病理上变慢。

（4）最后，我可以编写一个过滤外部变量的查询

（可能通过getCurrentPeriod （）函数）;这似乎是目前最好的解决方案，但是我失去了在同一份报告中调用多个年初至今数据的能力。


有没有人能更好地了解我应该如何制作

我的年初至今？我认为这是一个相当普遍的预算数字;

也许有人做过类似的事情？


我会非常感谢任何输入。谢谢。


Alan

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

" WindAndWaves" <交流**** @ ngaru.com>在消息中写道

新闻：Jo ****************** @ news.xtra.co.nz ...

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:Jo******************@news.xtra.co.nz...

亲爱的Alan

根据我的经验，SQL是执行任何类型数据
操作的最佳方式（比任何函数快得多）。


正确。


如果你想让你的查询更快，而不是将它们分解成几个追加的查询到临时表。首先，你删除临时表中的所有数据，然后添加新数据。

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 / O是数据访问中的薄弱环节 - 它比内存处理慢了几个数量级，所以

拇指的一般规则就是做尽可能少。


此外，我从未发现查询提供查询。要快于单个

sql语句。不是说它不会发生 - 但我从未见过它。我见过的查询性能不佳的主要原因是：


1.糟糕的数据库设计

2。糟糕的索引

3.糟糕的SQL代码，例如使用

子句而不是where子句来限制聚合列表。

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.

" ;艾伦" < K2 ******* @ sneakemail.com>在消息中写道

news：e1 ************************** @ posting.google.c om ...

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

我正在尝试制作一份报告，在特定时期内为特定部门的所有项目提供预算信息。我需要显示每个项目当前的预算，
年度分配预算，以及从年初到当前期间的总预算（ ;年到日期和QUOT）。我可以管理的前两个，但我被困在最后一个。现在我可以在一年内有效地计算项目所有预算的某个子集吗？

我考虑了几个选项：
（1）我可以使用查找功能，但有很多项目和
会太慢。
（2）我尝试切换运行总和。文本框上的属性，但是它想要计算同一个项目中所有项目的预算，然后再加上上一期的预算。
（3）我可以（尝试）编写一个查询，为每个项目 - 期间组合提供年初至今的计数，但随着数据库的增长，查询将在病理上变得缓慢。
（4）最后，我可以编写一个过滤外部变量的查询
（可能通过getCurrentPeriod（）函数）;这似乎是目前最好的解决方案，但我失去了在同一份报告中调用多个年初至今数字的能力。

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）是要走的路。如果你有一个好的设计你不需要担心

关于减速的事情 - 数据库，甚至是Access，都非常快速地在

汇总记录，甚至当他们有数百万行要处理时。

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.

这篇关于年初至今的总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！