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

access 97: queries

P: n/a
I want to group by two fields, sum the third, and concatenate the
fourth. I'm stumped on how to do this without writing code for it, I
would think there's a way to do it with just the query builder.

A B C D
1 2 4 The
1 2 3 Cow
1 2 2 Goes
1 2 1 Moo

my query would return

A B C D
1 2 10 TheCowGoesMoo

Group By A, B
Sum C
Concatenate D
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
AFAIK, there's no way to do that using the query builder. You can certain
group by A, B and sum C, but you can't concatenate in SQL.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"JMCN" <pi******@yahoo.fr> wrote in message
news:27**************************@posting.google.c om...
I want to group by two fields, sum the third, and concatenate the
fourth. I'm stumped on how to do this without writing code for it, I
would think there's a way to do it with just the query builder.

A B C D
1 2 4 The
1 2 3 Cow
1 2 2 Goes
1 2 1 Moo

my query would return

A B C D
1 2 10 TheCowGoesMoo

Group By A, B
Sum C
Concatenate D

Nov 13 '05 #2

P: n/a
That is right. No way to do it in querybuilder but I had an idea once for
the same situation. I came up with a function that did the concatenation.
The problem with it is I am not sure if it will work in all situations. For
example: using it with fields that are very long strings--maybe access will
have some kind of limitation as to how much can be concatenated. Anyway
here is the function if you want to try--check my syntax.

Function Combine(sql)
Set rst = DBEngine.Workspaces(0).Databases(0).OpenRecordset( sql)
While Not rst.EOF
temp = temp & rst.Fields(0)
rst.MoveNext
If Not rst.EOF Then temp = temp & " "
Wend
Combine = temp
End Function
the sql parameter needs to be a valid SELECT statement that has the field
you want to concatenate as the FIRST field (rst.Fields(0)). It also needs
to include any grouping or sorting so that you get "The Cow Goes Moo"
instead of "Moo Goes Cow The". I will warn you that this may be a very
inefficient way of doing this if working with large tables--it was something
I made on the fly and didn't test it alot.

Also, the way it is written above it will separate each field with a space.
This can be changed to a comma or dash or empystring or whatever you want.
cheers.

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:ce************@news04.bloor.is.net.cable.roge rs.com...
AFAIK, there's no way to do that using the query builder. You can certain
group by A, B and sum C, but you can't concatenate in SQL.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"JMCN" <pi******@yahoo.fr> wrote in message
news:27**************************@posting.google.c om...
I want to group by two fields, sum the third, and concatenate the
fourth. I'm stumped on how to do this without writing code for it, I
would think there's a way to do it with just the query builder.

A B C D
1 2 4 The
1 2 3 Cow
1 2 2 Goes
1 2 1 Moo

my query would return

A B C D
1 2 10 TheCowGoesMoo

Group By A, B
Sum C
Concatenate D


Nov 13 '05 #3

P: n/a
doing a totals query and then throwing in fConcatChild from Accessweb?
No other idea!
Nov 13 '05 #4

P: n/a
pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...
doing a totals query and then throwing in fConcatChild from Accessweb?
No other idea!

Thank you all for reaffirming my doubts. I appreciate the advise :)
Cheers - Jung
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.