access 97: queries

[JMCN]

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

[Douglas J. Steele]

Re: access 97: queries

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.0 408201115.37588 3b4@posting.goo gle.com...[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]

[Marvin Henry]

Re: access 97: queries

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.Worksp aces(0).Databas es(0).OpenRecor dset(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_djsteel e@NOSPAM_canada .com> wrote in message
news:cetVc.11$G 7G.5@news04.blo or.is.net.cable .rogers.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.0 408201115.37588 3b4@posting.goo gle.com...[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]

[Pieter Linden]

Re: access 97: queries

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

[JMCN]

Re: access 97: queries

pietlinden@hotm ail.com (Pieter Linden) wrote in message news:<bf31e41b. 0408211528.409b 2689@posting.go ogle.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