By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Group column headings and sum their values

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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...

Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

P: n/a
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.SumOfValue, 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.SumOfValue) AS [The Value]
SELECT qryEX2.RowV
FROM qryEX2
GROUP BY qryEX2.RowV
PIVOT qryEX2.ID;

"Jean" <je**********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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

Nov 13 '05 #4

P: n/a
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

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.