Aircode to return a string containing the results for each number. You can
then create a query into just the table that has the number as primary key,
and use the function to list the child records beside it:
Function ConcatDetail(Num As Long) As Variant
Dim rs As DAO.Recordset
Dim strOut As String
Dim strSql As String
Dim lngLen As Long
Const strcSep = "; "
strSql = "SELECT Date, W, V FROM Table1 WHERE Number = " & Num & ";"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
With rs
Do While Not .EOF
strOut = strOut & !Date & strcSep & !W & strcSep & !V & strcSep
.MoveNext
Loop
End With
rs.Close
lngLen = Len(strOut) - Len(strcSep) 'Without trailing separator.
If lngLen > 0 Then
ConcatDetail = Left(strOut, lngLen)
Else
ConcatDetail = Null
End If
Set rs = Nothing
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"osmethod" <os******@eircom.net> wrote in message
news:9c**************************@posting.google.c om...
I'm stuck!
I have received help before and progressed greatly from your(Access
Group) answers. I need some again please. Thanks in advance for any
suggestions.
Problem: Take the following dataset
Number Date W V
50200401 01/02/04 43.1 4
51200402 02/03/04 80.2 4
51200402 22/03/04 85.8 4
51200402 02/04/04 84.2 3
and transform it thus:
Number Date W V Date W V Date W V
50200401 01/02/04 43.1 4
51200402 02/03/04 80.2 4 22/03/04 85.8 4 02/04/04 84.2 3
There can be upto 10 entries under the number colum per number. Thus
Date W V headings would repeat for each entry.
I hope I have explained myself clearly.
Thank you,
osmethod
