khal
asked on
custom aggregate function
I am trying to do the following:
in a select query I want to use Group by,and for one of the fields I want to be able to do a custom aggregate function (or call a function in a module) that concatenate the values of that field.
example:
Table1
F1 F2 F3
A v1 a
B v2 b
B v2 c
I want the result of the query using group by and the custom function to be (group by F1,F2):
F1 F2 F3(after applying the function)
A V1 a
B V2 b,c ...... concatenate fields
in brief, I am trying to use a similar function to the Access aggregate functions (like sum) where I can use it with group by. Any help of outlining the code to do this and what values to pass to the function is appreciated.
in a select query I want to use Group by,and for one of the fields I want to be able to do a custom aggregate function (or call a function in a module) that concatenate the values of that field.
example:
Table1
F1 F2 F3
A v1 a
B v2 b
B v2 c
I want the result of the query using group by and the custom function to be (group by F1,F2):
F1 F2 F3(after applying the function)
A V1 a
B V2 b,c ...... concatenate fields
in brief, I am trying to use a similar function to the Access aggregate functions (like sum) where I can use it with group by. Any help of outlining the code to do this and what values to pass to the function is appreciated.
ASKER
Thanks
it looks like this function will do it, but for some reason I am having an error when I am passing those variables from the query. It does not like the punctuation and it is giving error message
Compile error in query function funcConcatenate("F3", "Table1", "F1='" & F1 & "' AND F2='" & F2 & "'", ",") AS F3
do you see any problem with the punctuation?
it looks like this function will do it, but for some reason I am having an error when I am passing those variables from the query. It does not like the punctuation and it is giving error message
Compile error in query function funcConcatenate("F3", "Table1", "F1='" & F1 & "' AND F2='" & F2 & "'", ",") AS F3
do you see any problem with the punctuation?
Try to use semicolons ";" instead of commas "," to separate parameters.
What is the way you open your query?
Is it a saved query?
What is the way you open your query?
Is it a saved query?
ASKER
semicolons did not work. I use a saved select query. What is the difference?
ASKER
This is the exact SQL for the query
SELECT Table1.f1, Table1.f2, funcConcatenate("F3", "Table1", "F1='" & F1 & "' AND F2='" & F2 & "'", ",") AS F3
FROM Table1
GROUP BY Table1.f1, Table1.f2;
and this is the error message:
compile error in query expression 'funcConcatenate("F3", "Table1", "F1='" & F1 & "' AND F2='" & F2 & "'", ",")'.
SELECT Table1.f1, Table1.f2, funcConcatenate("F3", "Table1", "F1='" & F1 & "' AND F2='" & F2 & "'", ",") AS F3
FROM Table1
GROUP BY Table1.f1, Table1.f2;
and this is the error message:
compile error in query expression 'funcConcatenate("F3", "Table1", "F1='" & F1 & "' AND F2='" & F2 & "'", ",")'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Declare a public function (in any standard module):
Public Function funcConcatenate(txtFieldNa
Dim rs As DAO.Recordset
Dim res As String
res = ""
Set rs = CurrentDb.OpenRecordset("S
If Not rs.BOF Then
rs.MoveFirst
While Not rs.EOF
res = res & rs(txtFieldName) & txtSeparator
rs.MoveNext
Wend
res = Left(res, Len(res) - Len(txtSeparator))
End If
rs.Close
funcConcatenate = res
End Function
Usage:
SELECT F1, F2, funcConcatenate("F3", "TableName", "F1='" & F1 & "' AND F2='" & F2 & "'", ",") AS F3
FROM TableName
GROUP BY F1, F2;
HTH
Need help? - Just @skme!