使用表作为计数器 [英] Using table as a counter

问题描述

我有一个查询生成一个包含以下列的表格：

Aaa Aa1 Aa2 Aa3 Baa1 Baa2 Baa3 Ba1 Ba2 Ba3 B1 B2 B3 CCaa D


这是由insert into语句生成的。然后我修改表格

添加一个自动增量主键（以保留行顺序）。


我想要的是能够循环我的代码，即每个''run''产生一个上述格式的
表。我想要另一张表来保存

表的总和。例如


说我有


Cols：第1项/第2项

11 1

4 10

和：


Cols：第1项/第2项

19 12

14 8


我想要另一张桌子给我


Cols：Item 1 / Item 2

30 13

18 18


当我再次运行代码并说出以下内容时：


Cols：项目1 /项目2

1 1

1 1


我想更新我的总和表反映


Cols：Item 1 / Item 2

31 14

19


关于如何做到这一点的任何想法？


谢谢！

Hi,

I have a query that generates a table with following columns follows:

Aaa Aa1 Aa2 Aa3 Baa1 Baa2 Baa3 Ba1 Ba2 Ba3 B1 B2 B3 CCaa D

This is generated by an insert into statement. I then modify the table
to add an autoincrement primary key (to preserve row order).

What I want is to be able to loop my code, ie each ''run'' produces one
table of the above format. I want another table to hold the sum of
tables. e.g.

Say I had

Cols: Item 1 / Item 2
11 1
4 10

and:

Cols: Item 1 / Item 2
19 12
14 8

I want another table to give me

Cols: Item 1 / Item 2
30 13
18 18

and when i run the code again and say the following gets produced:

Cols: Item 1 / Item 2
1 1
1 1

I want to update my summation table to reflect

Cols: Item 1 / Item 2
31 14
19 19

Any thoughts on how this can be done ?

Thanks !

推荐答案

假设自动编号很合适，并且您按照编号添加了

行数，那么：


select

table1.item1 + table2.ite m1 as item_1，

table1.item2 + table2.item2 as item_2

from

table1 inner join table2 on table1.PK = table2.PK


会给你总结。


否则你将需要子查询来提供基于
$ b的psudo密钥$ b行顺序（按您的autonum排序）并加入它们。你可能需要

单独查看。


我担心你每次创建一堆桌子

运行代码，但是你有没有更好的代码来更新现有表格中的数据？很难知道你

在这里没有看到你用来生成

桌子的代码。


如果你_really_想要创建一个结果表，你可以从设计视图中轻松地将

以上的查询变成一个make表查询

查询编辑。

Assuming the autonumbers are in tact and you are adding matching the
rows number by number, then:

select
table1.item1 + table2.item1 as item_1,
table1.item2 + table2.item2 as item_2
from
table1 inner join table2 on table1.PK = table2.PK

will give you the summation.

Otherwise you will need sub-queries to privide a psudo key based on
row order (sorted by your autonum) and join them on that. you may need
to look that up seperately.

i''m worried that you are creating a bunch of tablees every time you
run the code, but would you not be far better off having code that
updated the data in the existing tables? it''s hard to know that you
are doing here without seeing the code you are using to produce your
tables.

If you _really_ want to make a table of the result, you can turn the
above query into a make table query quite easily from the design view
of the query editor.

5月21日上午7:57，sandiptay ... @ gmail.com < sandiptay ... @ gmail.com>

写道：

On May 21, 7:57 am, "[e-mail]" <[e-mail]>
wrote:

我有一个查询生成一个包含以下列的表格：


Aaa Aa1 Aa2 Aa3 Baa1 Baa2 Baa3 Ba1 Ba2 Ba3 B1 B2 B3 CCaa D


这是由insert into语句生成的。然后我修改表格

添加一个自动增量主键（以保留行顺序）。


我想要的是能够循环我的代码，即每个''run''产生一个上述格式的
表。我想要另一张表来保存

表的总和。例如


说我有


Cols：第1项/第2项

11 1

4 10

和：


Cols：第1项/第2项

19 12

14 8


我想要另一张桌子给我


Cols：Item 1 / Item 2

30 13

18 18


当我再次运行代码并说出以下内容时：


Cols：项目1 /项目2

1 1

1 1


我想更新我的总和表反映


Cols：Item 1 / Item 2

31 14

19


关于如何做到这一点的任何想法？


谢谢！

Hi,

I have a query that generates a table with following columns follows:

Aaa Aa1 Aa2 Aa3 Baa1 Baa2 Baa3 Ba1 Ba2 Ba3 B1 B2 B3 CCaa D

This is generated by an insert into statement. I then modify the table
to add an autoincrement primary key (to preserve row order).

What I want is to be able to loop my code, ie each ''run'' produces one
table of the above format. I want another table to hold the sum of
tables. e.g.

Say I had

Cols: Item 1 / Item 2
11 1
4 10

and:

Cols: Item 1 / Item 2
19 12
14 8

I want another table to give me

Cols: Item 1 / Item 2
30 13
18 18

and when i run the code again and say the following gets produced:

Cols: Item 1 / Item 2
1 1
1 1

I want to update my summation table to reflect

Cols: Item 1 / Item 2
31 14
19 19

Any thoughts on how this can be done ?

Thanks !

第一张表没有他们有字段的列。


第二张表没有他们有recrods的行。 />

第三，自动编号不会与任何给定的订单匹配。

自动编号不能用作具有任何含义的字段

表中的数据是什么。它只是分配给每条记录的唯一

。使用增量而不是随机

自动编号根本不会改变这一点。仅仅因为给定记录

具有值6。在自动编号字段中并不意味着它是

第6条记录。因此，（恕我直言）总是更好地使用随机的

autonumbers所以甚至没有给出数字意味着

任何东西的外观。


要做我认为你要问你应该首先添加两个

字段。一个字段，您使用唯一数据填充（例如

项目标记号，员工ID等）。另一个名为

RunDate的字段或者某个silimar，用于保存给定运行的日期。

然后修改yoru''code''以便它将数据附加到每个

上的表运行而不是每次都创建一个新表。之后你可以用
运行一个简单的求和查询来获得所需的输出。


如果你以你的方式继续，你将需要运行

多个查询以确保不匹配的记录总结等。

First tables do not have columns they have fields.

Second tables do not have rows they have recrods.

Third, autonumber will not NECESSARILY match any given ''order''.
Autonumber is NOT intended to be used as a field that has any meaning
what-so-ever- to the data within the table. It is merely a unique
numebr assigned to each record. Using increment rather than random
autonumbers does not change this at all. Just because a given record
has the value "6" in the autonumber field does NOT mean that it is the
6th record. As such it is (IMHO) always better to use random
autonumbers so asto not even give the appearance that the numbers mean
anything.

To do what I THINK you are asking you should first have ADD TWO
fields. One a field that YOU populate with unique data (such as a
item tag number, employee id, etc). The other a field with called
RunDate or something silimar that hold the date a given run was made.
Then modify yoru ''code'' so that it appends data to the table on each
run rather than creating a new table each time. After that you can
run a simple summation query to get the desired output.

If you continue in the manner you are on, you will need to run
multiple queries to ensure that unmatched records are summed etc.

5月21日上午7:57 ，sandiptay ... @ gmail.com < sandiptay ... @ gmail.com>

写道：

On May 21, 7:57 am, "[e-mail]" <[e-mail]>
wrote:

我有一个查询生成一个包含以下列的表格：


Aaa Aa1 Aa2 Aa3 Baa1 Baa2 Baa3 Ba1 Ba2 Ba3 B1 B2 B3 CCaa D


这是由insert into语句生成的。然后我修改表格

添加一个自动增量主键（以保留行顺序）。


我想要的是能够循环我的代码，即每个''run''产生一个上述格式的
表。我想要另一张表来保存

表的总和。例如


说我有


Cols：第1项/第2项

11 1

4 10

和：


Cols：第1项/第2项

19 12

14 8


我想要另一张桌子给我


Cols：Item 1 / Item 2

30 13

18 18


当我再次运行代码并说出以下内容时：


Cols：项目1 /项目2

1 1

1 1


我想更新我的总和表反映


Cols：Item 1 / Item 2

31 14

19


关于如何做到这一点的任何想法？


谢谢！

Hi,

I have a query that generates a table with following columns follows:

Aaa Aa1 Aa2 Aa3 Baa1 Baa2 Baa3 Ba1 Ba2 Ba3 B1 B2 B3 CCaa D

This is generated by an insert into statement. I then modify the table
to add an autoincrement primary key (to preserve row order).

What I want is to be able to loop my code, ie each ''run'' produces one
table of the above format. I want another table to hold the sum of
tables. e.g.

Say I had

Cols: Item 1 / Item 2
11 1
4 10

and:

Cols: Item 1 / Item 2
19 12
14 8

I want another table to give me

Cols: Item 1 / Item 2
30 13
18 18

and when i run the code again and say the following gets produced:

Cols: Item 1 / Item 2
1 1
1 1

I want to update my summation table to reflect

Cols: Item 1 / Item 2
31 14
19 19

Any thoughts on how this can be done ?

Thanks !

第一张表没有他们有字段的列。


第二张表没有他们有recrods的行。 />

第三，自动编号不会与任何给定的订单匹配。

自动编号不能用作具有任何含义的字段

表中的数据是什么。它只是分配给每条记录的唯一

。使用增量而不是随机

自动编号根本不会改变这一点。仅仅因为给定记录

具有值6。在自动编号字段中并不意味着它是

第6条记录。因此，（恕我直言）总是更好地使用随机的

autonumbers所以甚至没有给出数字意味着

任何东西的外观。


要做我认为你要问你应该首先添加两个

字段。一个字段，您使用唯一数据填充（例如

项目标记号，员工ID等）。另一个名为

RunDate的字段或者某个silimar，用于保存给定运行的日期。

然后修改yoru''code''以便它将数据附加到每个

上的表运行而不是每次都创建一个新表。之后你可以用
运行一个简单的求和查询来获得所需的输出。


如果你以你的方式继续，你将需要运行

多个查询以确保对不匹配的记录进行求和等。

First tables do not have columns they have fields.

Second tables do not have rows they have recrods.

Third, autonumber will not NECESSARILY match any given ''order''.
Autonumber is NOT intended to be used as a field that has any meaning
what-so-ever- to the data within the table. It is merely a unique
numebr assigned to each record. Using increment rather than random
autonumbers does not change this at all. Just because a given record
has the value "6" in the autonumber field does NOT mean that it is the
6th record. As such it is (IMHO) always better to use random
autonumbers so asto not even give the appearance that the numbers mean
anything.

To do what I THINK you are asking you should first have ADD TWO
fields. One a field that YOU populate with unique data (such as a
item tag number, employee id, etc). The other a field with called
RunDate or something silimar that hold the date a given run was made.
Then modify yoru ''code'' so that it appends data to the table on each
run rather than creating a new table each time. After that you can
run a simple summation query to get the desired output.

If you continue in the manner you are on, you will need to run
multiple queries to ensure that unmatched records are summed etc.

这篇关于使用表作为计数器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！