Make Table Query Question

[Hanif Merali]

Hello,

I'm having some difficulties creating a make table query. The source
table which I'm basing the make table query has the fields:

CIF
BusLine1-2001
BusLine1-2002
BusLine1-2003
BusLine1-2004
BusLine2-2001
BusLine2-2002
etc..etc.

What I am doing is grouping by CIF summing across all other fields and
creating a table. This works fine for the time being but on a month
to month basis the field names will change, some business lines may
have 2004 revenue in say Oct which didn't in Sept. I'm trying to
create this db so that very little changes to the queries will be
needed on a monthly basis. Is there a way to write a make table query
that groups by a known field (CIF) and sums across all other fields?
Someone sent me a function which takes in a recordset as a parameter
and spits out an array of the field names in that recordset, could I
use this somehow in my make table query or is there an easier way to
go about this?

Any help on this matter would be greatly appreciated.

Thanks in advance,

Sincerly,

Hanif Merali

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your table is not Normalized. It should be like this:

CREATE TABLE table_name (
BusLine TINYINT NOT NULL , -- the line number
Data_Year SMALLINT NOT NULL , -- the data year
CIF INTEGER NOT NULL , -- the data
CONSTRAINT PK_CIF PRIMARY KEY (BusLine, Data_Year)
)

You don't say what type of data you are working, aside from the word
"summing," which lead me to assign an INTEGER as the CIF data. It would
probably be a good idea to change the name of the column to something
more descriptive of the actual data.

With the above table design your queries will be much easier. Here is
one to sum the data for bus line 25 in the year 2003 & 2004:

SELECT BusLine, Sum(CIF) AS DataTotal
FROM CIF
WHERE BusLine = 25
AND Data_Year IN (2003, 2004)

To get different sums, change the criteria (WHERE clause).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQZWCEYechKqOuFEgEQIPIACgudZ0NgDoQ/5pbrMuMxaYnt80jqgAoLqi
jTMhQfiDIN4xgOvZyMOfnNie
=JJeK
-----END PGP SIGNATURE-----