access 97: queries

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

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" <picarama@yahoo.fr> wrote in message
news:2772ee20.0408201115.375883b4@posting.google.c om...[color=blue]
> 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[/color]

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:cetVc.11$G7G.5@news04.bloor.is.net.cable.roge rs.com...[color=blue]
> 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" <picarama@yahoo.fr> wrote in message
> news:2772ee20.0408201115.375883b4@posting.google.c om...[color=green]
> > 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[/color]
>
>[/color]

doing a totals query and then throwing in fConcatChild from Accessweb?
No other idea!

pietlinden@hotmail.com (Pieter Linden) wrote in message news:<bf31e41b.0408211528.409b2689@posting.google. com>...[color=blue]
> doing a totals query and then throwing in fConcatChild from Accessweb?
> No other idea![/color]


Thank you all for reaffirming my doubts. I appreciate the advise :)
Cheers - Jung