473,324 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Using table as a counter

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 !

May 21 '07 #1
12 1848

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.

May 21 '07 #2
On May 21, 7:57 am, "sandiptay...@gmail.com" <sandiptay...@gmail.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.


May 21 '07 #3
On May 21, 7:57 am, "sandiptay...@gmail.com" <sandiptay...@gmail.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.


May 21 '07 #4
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
May 21 '07 #5
On May 21, 12:59 pm, "Rick Brandt" <rickbran...@hotmail.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.

May 21 '07 #6
DavidB wrote:
On May 21, 12:59 pm, "Rick Brandt" <rickbran...@hotmail.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
May 21 '07 #7
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!

May 22 '07 #8
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!!!!!
May 22 '07 #9
"DavidB" <je***@yahoo.comwrote in message
news:11**********************@x35g2000prf.googlegr oups.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.

May 22 '07 #10
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.
in access it should be tblVendor. sticking with the standard hungarian
naming convention, the tbl prefix is necessary.

in an rdbms, it should be vendors - i.e. a collection of vendor
tuples. no tbl prefix is necessary, as you are not in a oo predictive
text coding environment like access. prefixing will only mess up your
data dictionary.

at the end of the day, if you design it and program it right - then
you can call it [table_v_e_n_d_o_r] for all it matters. you'll leave a
headache for anyone coming after you though!

i can't possibly comment on all the many databases you have worked
on... but please know what you are talking about before you start
shouting.

May 22 '07 #11
BillCo wrote:
in access it should be tblVendor. sticking with the standard hungarian
naming convention, the tbl prefix is necessary.
Again, not "necessary" but simply a preference. I have never used hungarian and
it does not hold the favor it once did.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
May 22 '07 #12
rwr
DavidB wrote:
On May 21, 12:59 pm, "Rick Brandt" <rickbran...@hotmail.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.
Well your wrong. Search Microsoft for SQL Server rows and columns and
you will find many hits.

May 23 '07 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: PG | last post by:
When deleting a row from the database, that id is now missing. So what I'm trying to do is update in a loop (maybe an sql loop if there is one) of all of the id numbers - sort of like renaming...
3
by: Uwe C. Schroeder | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi. I have the following little stored proc: CREATE OR REPLACE FUNCTION public.ib_nextval(varchar) RETURNS varchar AS 'DECLARE
1
by: Arash | last post by:
hi, I read couple of emails in the group regarding using the form to display a message (instead of MsgBox which needs user input to be closed). I'm not very familiar with forms, I would...
5
by: Uwe C. Schroeder | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, maybe my mind is stuck, but here's something strange. This is the classic "counter" thing, where you can't / won't use sequences....
7
by: news.east.cox.net | last post by:
Hello, I'm trying to figure out why the following code won't work for me. The Firefox javascript console tells me that tableItemClicked is not defined, but the function is right there in the...
1
by: nanoman | last post by:
Hello, I've got a Nested Set structure in MySQL 4 here with - id - lft - rgt - parent_id - root_id I wrote some test scripts and i discovered that the Nested Set (the
10
by: marting | last post by:
Before I throw my new expensive laptop out of the window! I'm stuck on getting my joins right with the correct amount in a column count. After speaking with someone a few weeks back, they...
11
PaullyB
by: PaullyB | last post by:
Hi There I'm trying to insert a CSV file into SQL Database using C#. I can read the csv file and insert it into the table, however any fields with an embebbed comma are not being read correctly. How...
11
by: JWest46088 | last post by:
I'm having difficulty trying to figure out how to print a text file from a hash table one line at a time. I have the text file read into the hash table and can print the text file all at once, but I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.