aggregate text in group query

[Guest]

I have a list of people claiming expenses, many sharing the same payee and I
would like to group these records. This works fine with a grouped query,
using the aggregate function SUM to sum the amounts by payee. What would be
great (and this comes up quite a lot for different databases) is a way to
aggregate the names of the people too, i.e. see them concatenated next to the
total amount. Can this be done in SQL (rather than knocking together some
kind of VBA/ADO/DAO function)?

 

[Brian Bastl]

Hi Martin,

you may want to take a look at Duane Hookom's sample mdb to concatenate
child records. You can find it at here:

http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

HTH,
Brian

 

[Guest]

Thanks Brian (and Duane for the VBA function). This feels like the right
road to follow - the difference is that I'm not using a parent/child pair of
tables but am grouping a query based on a single table.

The following is the SQL I've managed to put together - could you tell me
where I'm going wrong as the Volunteers field stays stubbornly empty:

SELECT T.payee, Sum(T.amount) AS Total, Concatenate("SELECT surname FROM
Table1 as X WHERE 'X.[payee]' = 'T.[payee]'") AS Volunteers,
FROM Table1 AS T
GROUP BY T.payee;

Thanks.

 

[Brian Bastl]

Hi Martin,

off hand, looks like apostrophes and quotation marks were ill-placed. i've
revised it slightly under the assumption that the "payee" field is a text
data type:

SELECT T.payee, Sum(T.amount) AS Total, Concatenate("SELECT surname FROM
Table1 as X WHERE X.[payee] ='" & T.[payee] & "'") AS Volunteers,
FROM Table1 AS T
GROUP BY T.payee;

caveat: if payee is text and you have names like O'Hare, you'll have to
change it to something like:
=""" & T.[payee] & """") As Volunteers,

HTH,
Brian

Thanks Brian (and Duane for the VBA function). This feels like the right
road to follow - the difference is that I'm not using a parent/child pair of
tables but am grouping a query based on a single table.

The following is the SQL I've managed to put together - could you tell me
where I'm going wrong as the Volunteers field stays stubbornly empty:

SELECT T.payee, Sum(T.amount) AS Total, Concatenate("SELECT surname FROM
Table1 as X WHERE 'X.[payee]' = 'T.[payee]'") AS Volunteers,
FROM Table1 AS T
GROUP BY T.payee;

Thanks.

Hi Martin,

you may want to take a look at Duane Hookom's sample mdb to concatenate
child records. You can find it at here:

http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

HTH,
Brian


and
I would
be to
the

 

[Guest]

Thanks very much Brian - that worked a dream (less the comma after Volunteers
which was my mistake!). This is going to be useful in many different
applications: to be able to aggregate text as well as numerics when using a
group query.

Hi Martin,

off hand, looks like apostrophes and quotation marks were ill-placed. i've
revised it slightly under the assumption that the "payee" field is a text
data type:

SELECT T.payee, Sum(T.amount) AS Total, Concatenate("SELECT surname FROM
Table1 as X WHERE X.[payee] ='" & T.[payee] & "'") AS Volunteers,
FROM Table1 AS T
GROUP BY T.payee;

caveat: if payee is text and you have names like O'Hare, you'll have to
change it to something like:
=""" & T.[payee] & """") As Volunteers,

HTH,
Brian

Thanks Brian (and Duane for the VBA function). This feels like the right
road to follow - the difference is that I'm not using a parent/child pair of
tables but am grouping a query based on a single table.

The following is the SQL I've managed to put together - could you tell me
where I'm going wrong as the Volunteers field stays stubbornly empty:

SELECT T.payee, Sum(T.amount) AS Total, Concatenate("SELECT surname FROM
Table1 as X WHERE 'X.[payee]' = 'T.[payee]'") AS Volunteers,
FROM Table1 AS T
GROUP BY T.payee;

Thanks.

Hi Martin,

you may want to take a look at Duane Hookom's sample mdb to concatenate
child records. You can find it at here:

http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

HTH,
Brian


I have a list of people claiming expenses, many sharing the same payee and
I
would like to group these records. This works fine with a grouped query,
using the aggregate function SUM to sum the amounts by payee. What would
be
great (and this comes up quite a lot for different databases) is a way to
aggregate the names of the people too, i.e. see them concatenated next to
the
total amount. Can this be done in SQL (rather than knocking together some
kind of VBA/ADO/DAO function)?