从记录集问题中获取总和。 ACCESS97 [英] Getting a sum from a recordset question. Access97
问题描述
我有一个连续的表单，列出查询中的记录。其中一个
列是一个金额字段，在表单的页脚中我放置一个文本
框，显示金额的总和（总和）。
表格中的数据是从一对多的表格关系链接的。
在这个例子中，我真的无法总结一个人的价值's table $
因为如果在记录ID的许多表中有3条记录，
值将相加3次。 IOW，我可能会在表格中显示3000条记录，但我真的想要总计1000条记录。
此外，表格上显示的记录可以是从
多种方式过滤掉过滤后的数据可以在一个和多个表的数据上过滤。
我可以创建一个查询，该查询对一中的金额字段求和。 table
但是我需要查询来反映表单上的当前过滤器。
如果我创建一个类似于从表1中选择总和（金额）的查询ID IN
（选择....），查询需要很长时间才能运行。
目前我创建了一个选择唯一的SQL字符串基于过滤器在表单上显示
的ID，为SQL字符串创建一个临时查询结构，然后创建另一个总和数量的SQL字符串
其中ID是临时查询的内部联接。我打开它作为
记录集，获取值，然后删除查询。这种方法可以实现
并且接近瞬间。问题是这个表单全天打开很多
次，并且会给数据库增加膨胀。
是否有一种方法可以查询基于数据的表格
以一对多的形式显示，可以给出一个计数或总和
哪个会很快并且膨胀较少？
..
我在这里遗漏了什么吗？
如果你的表单'的记录集中有一个名为Amount的字段，你不能在页脚中放一个
文本框，其控制源为
= Sum （金额）
？
当您过滤表格时，不会更新文本框吗？
您还需要其他什么吗？
- 海龟
UN Me <未** @ together.com>在消息中写道
新闻：3F *************** @ together.com ...我有一个连续的表格列出查询中的记录。其中一个
列是一个金额字段，在表单的页脚中我放置一个文本
框，显示金额的总和（总和）。
数据在表单是从一对多的表关系链接的。
在这种情况下，我真的无法总结一个人的表中的价值
因为如果有3个记录许多表为记录id，
值将总和3次。 IOW，我可能会在表单中显示3000条记录，但我真的想要总计1000条记录。
此外，表格上显示的记录可以从
数量中过滤掉方法可以在一个和多个表的数据上过滤过滤的数据。
我可以创建一个查询，该查询对一表中的金额字段求和。表
但我需要查询来反映表单上的当前过滤器。
如果我创建一个查询，如从表1中选择总和（金额）ID IN
（选择....），查询需要很长时间才能运行。
目前我创建了一个SQL字符串，用于根据过滤器选择表单上显示的唯一ID，为SQL字符串创建一个临时查询定义，然后创建另一个SQL字符串，该字符串总结了临时查询中内部连接ID的数量。我将其打开为
记录集，获取值，然后删除查询。这种方法很有效，并且接近瞬间。问题是这个表单在一天中打开了很多次，并且增加了数据库的膨胀。
是否有一种方法可以根据数据查询表格以一对多的形式显示，可以给出一个计数或总和
，这将是快速的，并且具有较少的膨胀？
。
感谢您的回复。
MacDermott写道：
我在这里遗漏了什么？
是的。
如果您的表单中有一个名为Amount的记录集中的字段，那么你不能放入一个
文本框吗？控制源的页脚是
=总和（金额）
不，这不会起作用。让我们看看2个SQL语句。
SQL语句1）
选择ID，从表中获取金额。
Entering = Sum（Amount）将起作用并呈现正确的值。这不是我问题的代表。
SQL语句2）
选择ID，Amount，Table2.Something from表1内连接表2上
Table1.ID = Table2.ID
这是我的问题的表示。输入= Sum（金额）不会
工作，因为如果有来自Table2的记录（很多表），
金额的总和也将包括重复。例如，table1包含3个记录
，ID值为1,2和3.在表2中。与Table1相关，有两个
ID = 1，三个Id = 2，五个ID = 3。使用你的建议，=总和（金额）
将总结表格上显示的所有10条记录。
接下来，我正在过滤表格'' s记录集有多种方式。如果我能做到这一点，那将是
很好：
从Table1内部加入中选择总和（金额）（选择不同形式的Rs.query
+ filter）在Table1.ID = rs.ID
我可以创建一个查询来执行上述操作....除了我没有过滤器。
过滤器可能包含日期范围，公司名称和其他标志。 Table2中的许多
字段。
当您过滤表单时，不会更新文本框吗？
您还需要其他什么吗？
比我正在使用的方法更好的方法。
- 乌龟
U N Me <未** @ together.com>在消息中写道
新闻：3F *************** @ together.com ...我有一个连续的表格，列出了来自的记录查询。其中一个
列是一个金额字段，在表单的页脚中我放置一个文本
框，显示金额的总和（总和）。
数据在表单是从一对多的表关系链接的。
在这种情况下，我真的无法总结一个人的表中的价值
因为如果有3个记录许多表为记录id，
值将总和3次。 IOW，我可能会在表单中显示3000条记录，但我真的想要总计1000条记录。
此外，表格上显示的记录可以从
数量中过滤掉方法可以在一个和多个表的数据上过滤过滤的数据。
我可以创建一个查询，该查询对一表中的金额字段求和。表
但我需要查询来反映表单上的当前过滤器。
如果我创建一个查询，如从表1中选择总和（金额）ID IN
（选择....），查询需要很长时间才能运行。
目前我创建了一个SQL字符串，用于根据过滤器选择表单上显示的唯一ID，为SQL字符串创建一个临时查询定义，然后创建另一个SQL字符串，该字符串总结了临时查询中内部连接ID的数量。我将其打开为
记录集，获取值，然后删除查询。这种方法很有效，并且接近瞬间。问题是这个表单在一天中打开了很多次，并且增加了数据库的膨胀。
是否有一种方法可以根据数据查询表格以一对多的形式显示，可以给出一个计数或总和
，这将是快速的，并且没有膨胀？
所以你有两张桌子，你把这些信息溢出到一个表格上，
这没关系，但是当你试图计算总数时，它就是没有好处，因为一些
数字本身就是总数？从这个
中完全消除表1的金额，并仅从
表2金额中计算您需要的总金额和小计。如果您必须检索这两个金额，可能是因为表格上的定位或某些东西，请将表格1和表格
2金额检索到单独的字段中。
Robert Crouser
UN Me <未** @ together.com>在消息中写道
新闻：3F *************** @ together.com ...感谢您的回复。
MacDermott写道：
我在这里错过了什么？
是的。
如果你有在您的表单'的记录集名为Amount的字段中，你不能将
a文本框放在页脚中，控件源为
= Sum（Amount）
不，那不行。让我们看看2个SQL语句。
SQL语句1）
选择ID，从表中的金额。
输入=总和（金额）可以工作，提出正确的价值。那是
不代表我的问题。
SQL语句2）
选择ID，Amount，Table2.Something from Table1 Inner Join Table2 On
Table1.ID = Table2.ID
这是我的问题的表示。输入=总和（金额）将使
不起作用，因为如果有来自Table2的记录（许多表），则
金额的总和也将包括重复。例如，table1包含3个
记录，ID值为1,2和3.在表2中。与Table1相关，有两个
ID = 1，三个Id = 2，五个ID = 3。使用你的建议，= Sum（Amount）
将总结表单上显示的所有10条记录。
接下来，我将以各种方式过滤表单的记录集。如果我可以这样做，那么
会很好：
从Table1内部连接中选择Sum（Amount）（选择Distinct Form''
Rs.query + filter）On Table1.ID = rs .ID
我可以创建一个查询来执行上述操作....除了我没有
过滤器。过滤器可以是日期范围，公司名称和其他标志。表2中的字段中有很多
。
当您过滤表单时，不会更新文本框吗？
您还需要其他东西吗？
比我正在使用的方法更好的方法。
- 海龟
; UN Me <未** @ together.com>在消息中写道
新闻：3F *************** @ together.com ...我有一个连续的表格，列出了来自的记录查询。其中一个
列是一个金额字段，在表单的页脚中我放置一个文本
框，显示金额的总和（总和）。
数据在表单是从一对多的表关系链接的。
在这种情况下，我真的无法总结一个人的表中的价值
因为如果有3个记录许多表为记录id，
值将总和3次。 IOW，我可能会在表格中显示3000条记录
但我真的想要总计1000条记录。
此外，表格上显示的记录可以从
众多中过滤掉方法可以在一个
和许多表的数据上过滤fltered数据。
我可以创建一个查询，该查询总结来自one的数量字段。表
但我需要查询来反映表单上的当前过滤器。
如果我创建一个查询，如从表1中选择总和（金额）ID IN
（选择....），查询需要很长时间才能运行。
目前我创建了一个SQL字符串，用于根据过滤器选择表单上显示的唯一ID，为SQL字符串创建一个临时查询定义，然后创建另一个SQL字符串，该字符串总结了临时查询中内部连接ID的数量。我将其打开为
记录集，获取值，然后删除查询。这个方法
起作用并且接近瞬间。问题是这个表单全天多次打开
并且会给数据库增加膨胀。
是否有一种方法可以根据数据查询表格以一对多的形式显示，可以给出一个计数或
总和，这个数字会很快并且膨胀较少？
I have a continuous form that lists records from a query. One of the
columns is an amount field, In the footer of the form I place a text
box that displays the total (sum) of the amount.
The data in the form is linked from a one-to-many table relationship.
In this instance, I really can''t sum the value from the one''s table
since if there were 3 records in the many table for a record id, the
value would be summed 3 times. IOW, I may be displaying 3000 records in
the form but I really want to sum 1000 records.
Furthermore, the records displayed on the form can be filtered from a
multitude of ways The fltered data may be filtered on both the one and
many table''s data.
I could create a query that sums the amount field from the "one" table
but I need the query to reflect the current filter on the form.
If I create a query like "Select Sum(Amount) From Table1 Where ID IN
(Select....), the query takes a long time to run.
Currently I create a SQL string that selects the unique IDs that are
displayed on the form based on the filter, create a temp querydef for
the SQL string, then create another SQL string that sums the amount
where the ID is inner joiined on the temp query. I open this as a
recordset, grab the value, and then delete the query. This method works
and is near instantaneous. The problem is that this form is opened many
times throughout the day and adds bloat to the database.
Is there a method you use that can query a table based on the data
displayed in a form from a one-to-many that could give a count or a sum
that would be fast and have less bloat?
..
Am I missing something here?
If you have a field in your form''s recordset named Amount, can''t you put a
textbox in the footer with a controlsource of
=Sum(Amount)
?
Won''t that textbox update when you filter the form?
Is there something else you need?
- Turtle
"U N Me" <un**@together.com> wrote in message
news:3F***************@together.com...I have a continuous form that lists records from a query. One of the
columns is an amount field, In the footer of the form I place a text
box that displays the total (sum) of the amount.
The data in the form is linked from a one-to-many table relationship.
In this instance, I really can''t sum the value from the one''s table
since if there were 3 records in the many table for a record id, the
value would be summed 3 times. IOW, I may be displaying 3000 records in
the form but I really want to sum 1000 records.
Furthermore, the records displayed on the form can be filtered from a
multitude of ways The fltered data may be filtered on both the one and
many table''s data.
I could create a query that sums the amount field from the "one" table
but I need the query to reflect the current filter on the form.
If I create a query like "Select Sum(Amount) From Table1 Where ID IN
(Select....), the query takes a long time to run.
Currently I create a SQL string that selects the unique IDs that are
displayed on the form based on the filter, create a temp querydef for
the SQL string, then create another SQL string that sums the amount
where the ID is inner joiined on the temp query. I open this as a
recordset, grab the value, and then delete the query. This method works
and is near instantaneous. The problem is that this form is opened many
times throughout the day and adds bloat to the database.
Is there a method you use that can query a table based on the data
displayed in a form from a one-to-many that could give a count or a sum
that would be fast and have less bloat?
.
Thanks for the reply.
MacDermott wrote:
Am I missing something here?
Yes.
If you have a field in your form''s recordset named Amount, can''t you put a
textbox in the footer with a controlsource of
=Sum(Amount)
No, that won''t work. Let''s look at 2 SQL statements.
SQL statement 1)
Select ID, Amount From Table.
Entering =Sum(Amount) would work and present the correct value. That is not a
representation of my problem.
SQL statement 2)
Select ID, Amount, Table2.Something from Table1 Inner Join Table2 On
Table1.ID = Table2.ID
This is a representation of my problem. Entering an =Sum(Amount) would not
work because if there are records from Table2 (a many table), the sum of
Amount would also include duplicates. For example, table1 contains 3 records
with ID value of 1,2, and 3. In Table2. related to Table1, there are two
ID=1, three Id=2, and five ID=3. Using your suggestion, the =Sum(Amount)
would sum up all 10 records displayed on the form.
Next, I am filtering the form''s recordset in a variety of ways. It would be
nice if I could do this:
Select Sum(Amount) From Table1 Inner Join (Select Distinct Form''s Rs.query
+ filter) On Table1.ID = rs.ID
I could create a query to do the above....except I''d be lacking the filter.
The filter may be on date ranges, company names, and other flags. many of the
fields in Table2.
Won''t that textbox update when you filter the form?
Is there something else you need?
A better method than the one I am using.
- Turtle
"U N Me" <un**@together.com> wrote in message
news:3F***************@together.com...I have a continuous form that lists records from a query. One of the
columns is an amount field, In the footer of the form I place a text
box that displays the total (sum) of the amount.
The data in the form is linked from a one-to-many table relationship.
In this instance, I really can''t sum the value from the one''s table
since if there were 3 records in the many table for a record id, the
value would be summed 3 times. IOW, I may be displaying 3000 records in
the form but I really want to sum 1000 records.
Furthermore, the records displayed on the form can be filtered from a
multitude of ways The fltered data may be filtered on both the one and
many table''s data.
I could create a query that sums the amount field from the "one" table
but I need the query to reflect the current filter on the form.
If I create a query like "Select Sum(Amount) From Table1 Where ID IN
(Select....), the query takes a long time to run.
Currently I create a SQL string that selects the unique IDs that are
displayed on the form based on the filter, create a temp querydef for
the SQL string, then create another SQL string that sums the amount
where the ID is inner joiined on the temp query. I open this as a
recordset, grab the value, and then delete the query. This method works
and is near instantaneous. The problem is that this form is opened many
times throughout the day and adds bloat to the database.
Is there a method you use that can query a table based on the data
displayed in a form from a one-to-many that could give a count or a sum
that would be fast and have less bloat?
So you have 2 tables and you''re spilling this information onto a form and
this is okay, but when you try to total, it''s no good because some of the
figures are themselves totals? Eliminate the table 1 amount from this
altogether and calculate whatever totals and subtotals you need from the
table 2 amount only. If you HAVE to retrieve both amounts, perhaps because
of the positioning on the form or something, retrieve the table 1 and table
2 amounts into separate fields.
Robert Crouser
"U N Me" <un**@together.com> wrote in message
news:3F***************@together.com...Thanks for the reply.
MacDermott wrote:Am I missing something here?
Yes.If you have a field in your form''s recordset named Amount, can''t you put a textbox in the footer with a controlsource of
=Sum(Amount)
No, that won''t work. Let''s look at 2 SQL statements.
SQL statement 1)
Select ID, Amount From Table.
Entering =Sum(Amount) would work and present the correct value. That is
not a representation of my problem.
SQL statement 2)
Select ID, Amount, Table2.Something from Table1 Inner Join Table2 On
Table1.ID = Table2.ID
This is a representation of my problem. Entering an =Sum(Amount) would not work because if there are records from Table2 (a many table), the sum of
Amount would also include duplicates. For example, table1 contains 3 records with ID value of 1,2, and 3. In Table2. related to Table1, there are two
ID=1, three Id=2, and five ID=3. Using your suggestion, the =Sum(Amount)
would sum up all 10 records displayed on the form.
Next, I am filtering the form''s recordset in a variety of ways. It would be nice if I could do this:
Select Sum(Amount) From Table1 Inner Join (Select Distinct Form''s Rs.query + filter) On Table1.ID = rs.ID
I could create a query to do the above....except I''d be lacking the filter. The filter may be on date ranges, company names, and other flags. many of the fields in Table2.Won''t that textbox update when you filter the form?
Is there something else you need?
A better method than the one I am using.
- Turtle
"U N Me" <un**@together.com> wrote in message
news:3F***************@together.com...I have a continuous form that lists records from a query. One of the
columns is an amount field, In the footer of the form I place a text
box that displays the total (sum) of the amount.
The data in the form is linked from a one-to-many table relationship.
In this instance, I really can''t sum the value from the one''s table
since if there were 3 records in the many table for a record id, the
value would be summed 3 times. IOW, I may be displaying 3000 records in the form but I really want to sum 1000 records.
Furthermore, the records displayed on the form can be filtered from a
multitude of ways The fltered data may be filtered on both the one and many table''s data.
I could create a query that sums the amount field from the "one" table
but I need the query to reflect the current filter on the form.
If I create a query like "Select Sum(Amount) From Table1 Where ID IN
(Select....), the query takes a long time to run.
Currently I create a SQL string that selects the unique IDs that are
displayed on the form based on the filter, create a temp querydef for
the SQL string, then create another SQL string that sums the amount
where the ID is inner joiined on the temp query. I open this as a
recordset, grab the value, and then delete the query. This method works and is near instantaneous. The problem is that this form is opened many times throughout the day and adds bloat to the database.
Is there a method you use that can query a table based on the data
displayed in a form from a one-to-many that could give a count or a sum that would be fast and have less bloat?
这篇关于从记录集问题中获取总和。 ACCESS97的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
