Using table as a counter

[sandiptaylor]

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 !

[BillCo]

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.

[DavidB]

On May 21, 7:57 am, "sandiptay...@g mail.com" <sandiptay...@g mail.com>
wrote:

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 !

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.

[DavidB]

On May 21, 7:57 am, "sandiptay...@g mail.com" <sandiptay...@g mail.com>
wrote:

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 !

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.

[Rick Brandt]

DavidB wrote:

First tables do not have columns they have fields.

Second tables do not have rows they have recrods.

Actually these terms are fairly isolated to Access users. In the more
general SQL/Relational database world Column/Row is considered correct and
Field/Record incorrect.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[DavidB]

On May 21, 12:59 pm, "Rick Brandt" <rickbran...@ho tmail.comwrote:

DavidB wrote:

First tables do not have columns they have fields.

Second tables do not have rows they have recrods.

Actually these terms are fairly isolated to Access users. In the more
general SQL/Relational database world Column/Row is considered correct and
Field/Record incorrect.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

I have worked with many databases and NONE have EVER referred to
fields and records are columns and rows. It is 100% logical in the DB
world to use field and record. Those are the basic building blocks of
a database. The element of data you define is unary. It is akin to
the correct naming convention for tables being singular words rather
than plurals (eg a table that hold vendor records would be called
tVendor or tblVendor NOT tVendors or tblVendors. I'll stick with what
I have used since I have been coding.

[Rick Brandt]

DavidB wrote:

On May 21, 12:59 pm, "Rick Brandt" <rickbran...@ho tmail.comwrote:

>DavidB wrote:

>>First tables do not have columns they have fields.

>>Second tables do not have rows they have recrods.

Actually these terms are fairly isolated to Access users. In the
more general SQL/Relational database world Column/Row is considered
correct and Field/Record incorrect.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

I have worked with many databases and NONE have EVER referred to
fields and records are columns and rows. It is 100% logical in the DB
world to use field and record. Those are the basic building blocks of
a database. The element of data you define is unary. It is akin to
the correct naming convention for tables being singular words rather
than plurals (eg a table that hold vendor records would be called
tVendor or tblVendor NOT tVendors or tblVendors. I'll stick with what
I have used since I have been coding.

From IBM's web site...

Traditional File Access Term: File
SQL Term: Table

Traditional File Access Term: Record
SQL Term: Row

Traditional File Access Term: Field
SQL Term: Column

I have no problem with your preference to use Record/Field instead of
Column/Row. I pretty much use the terms interchangeably myself. I just
think telling people who use the terms Column/Row that they are incorrect is
inaccurate.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[BillCo]

I have worked with many databases and NONE have EVER referred to
fields and records are columns and rows. It is 100% logical in the DB
world to use field and record. Those are the basic building blocks of
a database. The element of data you define is unary. It is akin to
the correct naming convention for tables being singular words rather
than plurals (eg a table that hold vendor records would be called
tVendor or tblVendor NOT tVendors or tblVendors. I'll stick with what
I have used since I have been coding.

it doesn't really matter what you call them - but to set the record
straight, once you leave the access world, tables have columns and
rows, recordsets have records and fields. Some people actually get
quite upset when you mix this up! check out Joe Celko's SQL Server
blog... actually my old database lecturer used to talk in terms of
"tuples" - confused the life out of me!!!

Actually just searched on that term now and it brought up an
interesting table:

Informal relational term Formal relational term Non-relational
term
Table Relation File
Column Attribute Field
Row Tuple Record

so table, column and row are informal relational terms and file, field
and record are non-relational terms. and my lecturer was throwing in
old school formal terms... no wonder i was confused!

[BillCo]

actually - a little more study on classical relational database theory
has me floored... i wish i had understood this stuff in college! a
relation is a set of tuples with attributes. this is often
_represented_ as a table. i.e. there is no actual table, because by
definition, the attributes should have no order and neither should the
tuples. and what we view as a table definition is a set of constraints
on the tuples in a relation. this is why when we interact with
relations as "tables", it is more appropriate to to refer to
"columns" and "rows". and by contrast, a recordset is a set of data
returned in ordered records with ordered fields - so "records" and
"fields" is appropriate. crazy shit!!!!!

[Keith Wilby]

"DavidB" <je***@yahoo.co mwrote in message
news:11******** **************@ x35g2000prf.goo glegroups.com.. .

>
I have worked with many databases and NONE have EVER referred to
fields and records are columns and rows. It is 100% logical in the DB
world to use field and record. Those are the basic building blocks of
a database. The element of data you define is unary. It is akin to
the correct naming convention for tables being singular words rather
than plurals (eg a table that hold vendor records would be called
tVendor or tblVendor NOT tVendors or tblVendors. I'll stick with what
I have used since I have been coding.

FWIW there was a time when I would have defended that stance but not since I
began working with Oracle.

Keith.