Group column headings and sum their values

[Jean]

Hello,

can the following be done?

I have a one-rowed query, with the following column headings and
values:

M-1 M-5 M-7 B-C-1 B-D-2 B-D-4 E-5 R-4
--- --- --- ----- ----- ----- --- ---
4 5 1 2 1 3 7 3
I want similiar columns to be grouped and their respective values to be
summed.
I.e.

M-* B-* E-5 R-4
--- --- --- ---
10 6 7 3

Can someone plese tell me how this is done in SQL?

I could just add the columns manually through the Design-View, but this
would cause that new column headings that are added in the future to
not be reflected in the query.

Jean

[pietlinden]

you can't do it any other way than manually. Why would you be adding
columns? This is a telltale sign of incorrect design. Your database
should add ROWS, not columns.

Can't be done in SQL...

[pietlinden]

Okay, since you can't do it in SQL, you *could* do it with code... but
it's going to be ugly. You have to have some consistent naming scheme
for your fields, and then you could loop through your fields, and
adding the ones with similar prefixes to a query. No matter how you
look at it, it's going to be VERY ugly.

[paii, Ron]

If your table is structured something like this

ID VALUE
---- ---------
M-1 4
M-5 5
..
R-4 3

You can condition the data with 3 queries. that will allow addition of
columns by adding a row to the table. Open the 3rd query to see you ID
values in columns. There may be a way to do this with fewer query, Maybe
someone will post it.

The 1st query will sum grouped on the 1st 2 characters of "ID"

SELECT Left([ID],2) & "*" AS IDType, Sum(tblex.Value ) AS SumOfValue,
Count(Left([ID],2)) AS CountIDType, First(tblex.ID) AS FirstOfID
FROM tblex
GROUP BY Left([ID],2) & "*";

The 2nd query will output the Grouped ID or the original ID and its value,
RowV is added for the cross tab query to work

SELECT IIf([CountIDType]>1,[IDType],[FirstOfID]) AS ID, qryEx1.SumOfVal ue, 1
AS RowV
FROM qryEx1
ORDER BY IIf([CountIDType]>1,[IDType],[FirstOfID]);

The 3rd query is a crosstab to put your ID into columns

TRANSFORM Avg(qryEX2.SumO fValue) AS [The Value]
SELECT qryEX2.RowV
FROM qryEX2
GROUP BY qryEX2.RowV
PIVOT qryEX2.ID;

"Jean" <je**********@h otmail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

Hello,

can the following be done?

I have a one-rowed query, with the following column headings and
values:

M-1 M-5 M-7 B-C-1 B-D-2 B-D-4 E-5 R-4
--- --- --- ----- ----- ----- --- ---
4 5 1 2 1 3 7 3
I want similiar columns to be grouped and their respective values to be
summed.
I.e.

M-* B-* E-5 R-4
--- --- --- ---
10 6 7 3

Can someone plese tell me how this is done in SQL?

I could just add the columns manually through the Design-View, but this
would cause that new column headings that are added in the future to
not be reflected in the query.

Jean

[Jean]

Hi guys,

well I am still stuck on this one, but I have taken a different
approach now. I decided to make use of ROWS rather, in order to not
complicate things.

I posted my current problem under this thread now:
http://groups-beta.google.com/group/...6fb733bc1a4e5b

Thanks for your advice.

Kind Regards,
Jean