Re: sum up the column

From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 10:56:12 -0600

Dear Tony:

Databases are designed to work for information that is structured in a
specific way. You are right on top of one of the major elements of
this structure. Simply stated, there is a very good, easy way to do
what you want, but your current structure is wrong.

You have multiple columns (fields) of the same kind of information,
and a potential that the number of values possible will grow. What
you need is to create another table for these values. Start by
identifying the Primary Key of your current table, and put these same
columns in the new table. Add a column that counts the elements as
they are added to the new table (like the 3 digit number you currently
put on the end of your ABCXXX column names. These columns become the
primary key of your new table. Add the ABC column to this table.

Create a relationship on the new table to the existing table on the
columns of the Primary Key to your existing table.

You can now record as many ABC values as allowed by the column you
added for counting them. If you use a long integer, you should never
run out.

If the values in ABC are unique you can do without the counting
column. Make ABC part of the Primary Key to the new table instead.

You now have virtually unlimited ability to enter as many ABC values
as are needed without ever needing to add columns to the original
table. Aggregate function can now count, sum, average, etc. on the
values.

Nothing like the ABC* wildcard you propose exists.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Thu, 1 Apr 2004 00:38:01 +0800, "tony wong" <x34@hknet.com> wrote:

>i have fields (ABC001, ABC002, ABC003.....)
>
>in query screen
>i can sum up the fields by (ABC001)+(ABC002)+(ABC003)....
>
>Can i do it by sum(ABC*)? But seems not allowed.
>
>it ask me to enter the exact field name.
>
>cos ABC field will continue to grow up, it is not wise to hard code the
>field name here.
>
>any other way out? thx.
>
>Tony
>

Relevant Pages

  • Re: Primary key question
    ... Oracle newbie trying to adjust to the differences in the two implementations. ... primary key ); ... CREATE TABLE ABC( ... let's add the primary key constraint: ...
    (comp.databases.oracle.server)
  • Re: Primary key question
    ... Oracle newbie trying to adjust to the differences in the two implementations. ... Create a basic table without a primary key: ... CREATE TABLE ABC( ... let's add the primary key constraint: ...
    (comp.databases.oracle.server)
  • Re: Primary Index and Null values
    ... You cannot do this for your primary key. ... Indexed=Yes (No Duplicates) ... F2 = Text, Required, Do Not Allow Zero Length, Not Indexed ... ABC ...
    (comp.databases.ms-access)
  • Re: Remove a space from field data
    ... Tom Ellison ... However, in your examples, you removed the first space and left the ... 123456 789 ABC ... Please respond in the newgroup and not by email. ...
    (microsoft.public.access.queries)