Select Statement-Group by Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query with the following select statement "LOB: Concatenate("SELECT
LOB FROM tblacespolicies WHERE accountID =" &
[tblclientaccountinfo.accountID])". My problem is that I want to group by
[LOB] and I don't know how to do it. I've tried adding the GROUP BY in the
statement in various places but nothing is working. The query is already a
totals query and the total row does show group by in it, but it's not
grouping the values. I have a module for the Concatenate statement that I
found on one of these boards, so I'm wondering if the problem is that my
module needs to have something in it for grouping.

Can anyone help?? Thanks!
 
Suzanne said:
I have a query with the following select statement "LOB: Concatenate("SELECT
LOB FROM tblacespolicies WHERE accountID =" &
[tblclientaccountinfo.accountID])". My problem is that I want to group by
[LOB] and I don't know how to do it. I've tried adding the GROUP BY in the
statement in various places but nothing is working. The query is already a
totals query and the total row does show group by in it, but it's not
grouping the values. I have a module for the Concatenate statement that I
found on one of these boards, so I'm wondering if the problem is that my
module needs to have something in it for grouping.
Click to expand...


That looks like Duane's Concatenate function (very popular
item). Even if it isn'tm you have the [ ]s in the wrong
place:

. . . & [tblclientaccountinfo[.[accountID])

I think the auto generated Group By clause should work if
you fix the typos in the calculated field.
 
Thank you both for the replies. I tried changing the brackets, but it didn't
do anything. I think you are correct that it's Duane's function. I'm
combining values from a one-to-many relationship into one field so that I
don't have multiple rows just for one differing piece of information.

S. Clark's solution worked well, except the query runs very slow. It's
taking anywhere from 30 seconds to 90 seconds. Any idea how I can speed this
up?

Thanks!!

Marshall Barton said:
Suzanne said:
I have a query with the following select statement "LOB: Concatenate("SELECT
LOB FROM tblacespolicies WHERE accountID =" &
[tblclientaccountinfo.accountID])". My problem is that I want to group by
[LOB] and I don't know how to do it. I've tried adding the GROUP BY in the
statement in various places but nothing is working. The query is already a
totals query and the total row does show group by in it, but it's not
grouping the values. I have a module for the Concatenate statement that I
found on one of these boards, so I'm wondering if the problem is that my
module needs to have something in it for grouping.
Click to expand...


That looks like Duane's Concatenate function (very popular
item). Even if it isn'tm you have the [ ]s in the wrong
place:

. . . & [tblclientaccountinfo[.[accountID])

I think the auto generated Group By clause should work if
you fix the typos in the calculated field.
Click to expand...
 
Back
Top