Master Detail Values display across right

[imfarhan]

Dear VBA Guru
I've a Master /Detail table as follows:
Master
Memb_No, MemberName, Surname
01, Paul, Smith
02, Richard, Gray

Detail
Memb_No, First_Name Surname Relation Dob
01, Clare, Smith, Wife, xx
01, Derek, Smith, Son, xx
02, Caroline, Gray, Wife, xx
02 Melaine Gray Daughter xx

I would like to display the value like this ...."Chid/Details values show across right instead of multiple row"

MemNo , FName,Surname, D1_Fname, Relation, D1_Dob , D2_Fname,D2_Dob
01 Paul, Smith , Clare , Wife , 02/0/1900 , Derek xx
02 Richard, Gray, Caroline, Wife, xxxxxxxxx, Melaine xx

I don't think we can do through SQL if yes that would be great.

What I 've done , I've created the query to join Master/detail table together. 'tblSoyab'
and I pull all the values using rs by wrting VBA codes, but i think I;'m making fundamental mistake please advise:

I would liek to see the out in my rsTarget as follwos:

[SIZE=2]Public Sub Members_FS()
Dim rs As DAO.Recordset
Dim rsMaster As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim varFName As String
Const tbl As String = "tblSoyab"
'Const tbl As String = "SHIFT_Members"
Const tbl_dtl As String = "SHIFT_Members_Dependents"
Const tbl_targ As String = "temp2"
'varFName = ("SELECT *  from " & tbl & " WHERE AccountNumber = '" & rsMaster.Fields("AccountNumber").Value & "' ORDER BY AccountNumber")
'Set rsMaster = CurrentDb.OpenRecordset("SELECT  *   from " & tbl & "group by AccountNumber")  ' Source DAO
'initialise recordset of target table
Set rsTarget = CurrentDb.OpenRecordset("SELECT * FROM " & tbl_targ)  ' Target DAO
Set rs = CurrentDb.OpenRecordset("SELECT *  from " & tbl & " order by AccountNumber")
'& " WHERE AccountNumber = " & rsMaster.Fields("AccountNumber").Value & " ORDER BY AccountNumber")
rs.MoveFirst
Do While Not rs.EOF

 
  With rsTarget
    .AddNew
   .Fields("Fname").Value = rs.Fields("SHIFT_Members_FirstName").Value
   .Fields("Sname").Value = rs.Fields("Surname").Value
  .Fields("DOB").Value = rs.Fields("Date_of_Birth").Value
  .Fields("D1_FNAME").Value = rs.Fields("SHIFT_Members_Dependents_FirstName").Value
  .Fields("D2_FNAME").Value = rs.Fields("SHIFT_Members_Dependents_FirstName").Value
   .Fields("D3_FNAME").Value = rs.Fields("SHIFT_Members_Dependents_FirstName").Value
  .Fields("D4_FNAME").Value = rs.Fields(" SHIFT_Members_Dependents_FirstName").Value
  .Fields("D5_FNAME").Value = rs.Fields(" SHIFT_Members_Dependents_FirstName").Value
       .Update
  End With
  rs.MoveNext
Loop
  rs.Close
  
End Sub
[/SIZE]

Regards

 

[jackd]

review Allen Browne's concatenation function

Microsoft Access tips: Concatenate values from related records

 

[imfarhan]

Thanks for the reply, just to acknowledge you I've created the function which you send me on Allen-Brownes' website.
When I run my SQL it prompt some error, I think I'm making mistakes when I pass the arguments ,

......"CompanyID = " & [CompanyID])

I will look more in depth and come back to you If still prompts the error.

Regards
Farhan