By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,671 Members | 1,278 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,671 IT Pros & Developers. It's quick & easy.

Invert Unequal Dataset

P: n/a
Hello,

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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
Thank you Allen... Got your function to work nicely...

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.