Special 'aggregate' query / "Second"-function

[barbarossaii]

Hi everyone,

need a special 'aggregate' query.

Got a table like this:

PrtNo..... txt

1.............txt1
1.............txt2
2.............txt3
2.............txt4
2.............txt5
3.............txt6


A query should return:

PrtNo..... ALLtxt

1.............txt1_txt2
2.............txt3_txt4_txt5
3.............txt6


Thought about grouping "partNo", creating (hidden) 'columns' containing the txts (one w/ the "First" and one w/ the "Last" function) and putting the txt-'colums' together....

But in my example this won't work because for PrtNo "2" I would need an additional "Second" - function (and later a "3rd"/"4th" etc function). And there is no such function in the graphic user interface I use to "programm" .....

All help is appreciated!

Thanks in advance,

Barbarossa II

 

[Fornatian]

Use this function:

Public Function Names(lngCurrentRecord As Long) As String
Dim db As DAO.Database
Dim rst As Recordset
Dim strReturn As String
Dim IntB As Integer
Set db = CurrentDb()
'Get the recordset
Set rst = db.OpenRecordset("Table")
rst.MoveLast
rst.MoveFirst
Do Until rst.EOF = True
    If rst("Number") = lngCurrentRecord Then
        If strReturn = "" Then
        strReturn = rst("Name")
        Else
        strReturn = strReturn & "," & rst("Name")
        End If
    End If
    rst.MoveNext
If rst.EOF = True Then Exit Do
Loop
If Len(strReturn) > 255 Then
strReturn = "Text is Too Large To Import"
Else
End If
Names = strReturn
End Function

this concatanates a string of records sharing the same 'number' and returns the answer as a string.

This can then be referenced in a query as a calculated column:

AssociatedNames: Names([Number])

 

[barbarossaii]

Thanx

Hi Fornatian,

will try that.

Thanx,
Barbarossa II