Group By Combination on Multiple Rows

[jason.teen]

Hi,

I have a scenario like so:

TblOne
--------------------------------------------
ElementID | Audit_Nbr
---------------------------------------------
F5000000570 73459
F5000000568 73459
F5000000567 73471
F5000000568 305995
F5000000573 305995
F5000000575 305995
F5000000569 305995
F5000000569 313027
F5000000567 313027
I need to group by Audit_Nbr, then for each distinct audit number
combine the Element ID's in a SORTED comma separated text field. NOTE:
One Audit Number can have 1-many ElementID

Output should be something like,

----------------------------------------------------------------------
Audit_Nbr | SORTED_ElementID_Combination
----------------------------------------------------------------------
73459 | F5000000568, F5000000570
73471 | F5000000567
305995 | F5000000568, F5000000569, F5000000573, F5000000575
313027 | F5000000567, F5000000569
Thanks in advance

[Rick Brandt]

ja********@gmail.com wrote:

Hi,

I have a scenario like so:

TblOne
--------------------------------------------
ElementID | Audit_Nbr
---------------------------------------------
F5000000570 73459
F5000000568 73459
F5000000567 73471
F5000000568 305995
F5000000573 305995
F5000000575 305995
F5000000569 305995
F5000000569 313027
F5000000567 313027
I need to group by Audit_Nbr, then for each distinct audit number
combine the Element ID's in a SORTED comma separated text field. NOTE:
One Audit Number can have 1-many ElementID

Output should be something like,

----------------------------------------------------------------------
Audit_Nbr | SORTED_ElementID_Combination
----------------------------------------------------------------------
73459 | F5000000568, F5000000570
73471 | F5000000567
305995 | F5000000568, F5000000569, F5000000573, F5000000575
313027 | F5000000567, F5000000569
Thanks in advance

There is no standard query that can produce that output from your data. You
would need a custom VBA function that will create the comma separated list using
an Audit_Nbr as an input. Then you could build a query using that function.

SELECT DISTINCT Audit_Nbr, FunctionName(Audit_Nbr) AS Elements
FROM TblOne

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Bob Quintal]

"Rick Brandt" <ri*********@hotmail.comwrote in
news:tm***************@newssvr11.news.prodigy.com:

ja********@gmail.com wrote:

>Hi,

I have a scenario like so:

TblOne
--------------------------------------------
ElementID | Audit_Nbr
---------------------------------------------
F5000000570 73459
F5000000568 73459
F5000000567 73471
F5000000568 305995
F5000000573 305995
F5000000575 305995
F5000000569 305995
F5000000569 313027
F5000000567 313027
I need to group by Audit_Nbr, then for each distinct audit
number combine the Element ID's in a SORTED comma separated
text field. NOTE: One Audit Number can have 1-many ElementID

Output should be something like,

--------------------------------------------------------------

>-------- Audit_Nbr | SORTED_ElementID_Combination

--------------------------------------------------------------

>-------- 73459 | F5000000568, F5000000570
73471 | F5000000567
305995 | F5000000568, F5000000569, F5000000573,
F5000000575 313027 | F5000000567, F5000000569
Thanks in advance

There is no standard query that can produce that output from
your data. You would need a custom VBA function that will
create the comma separated list using an Audit_Nbr as an
input. Then you could build a query using that function.

SELECT DISTINCT Audit_Nbr, FunctionName(Audit_Nbr) AS Elements
FROM TblOne

That custom function is fConcatChild() by Dev Ashsh, available
at http://www.mvps.org/access/modules/mdl0004.htm and other
places.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[jason.teen]

I've started to write my own custom code, however, I find that the
Find/FindNext and NoMatch doesn't seem to work as I expected. If you
can see any errors in my code please let me know.

I am expecting a pointer to move the current recordset pointer as it
FindFirst on the first criteria and then subsequent FindNext.
here is my test db I'm using to create this code.

Audit_Nbr | Element ID
---------------------------------
629 F046
130 F567
130 F569
615 F576
396 F576
396 F577
396 F578
629 F622

Public Function CombineElementIDgroupbyAuditNbr()

Dim dbRef As Database
Dim rsRef, rsIn As Recordset
Dim rsOut As Recordset
Dim ChargeAttrRelCombo As String

DoCmd.SetWarnings (False)
DoCmd.RunSQL ("DELETE * FROM ref_ElementID_combined_by_AuditNbr")

Set dbRef = OpenDatabase("L:\Mapping\Legacy Data Extracts.mdb")
Set rsRef = dbRef.OpenRecordset("SELECT Audit_Nbr, Count([Element
ID]) AS Count " & _
"FROM [fakePDDBLoad] " & _
"GROUP BY Audit_Nbr")

Set rsIn = dbRef.OpenRecordset("SELECT Audit_Nbr, [Element ID] " &
_
"FROM [fakePDDBLoad] " & _
"ORDER BY Audit_Nbr, [Element ID]")

Set rsOut =
CurrentDb().OpenRecordset("ref_ElementID_combined_ by_AuditNbr")

Do While Not rsRef.EOF

rsIn.FindFirst (rsIn![Audit_Nbr] = rsRef![Audit_Nbr])

ChargeAttrRelCombo = rsIn![Element ID]

rsIn.FindNext (rsIn![Audit_Nbr] = rsRef![Audit_Nbr])

While rsIn.NoMatch = False
ChargeAttrRelCombo = ChargeAttrRelCombo + ", " +
rsIn![Element ID]
rsIn.FindNext (rsIn![Audit_Nbr] = rsRef![Audit_Nbr])
Wend

MsgBox (ChargeAttrRelCombo)

rsRef.MoveNext
Loop
End Function