Complex query, do I use union?

[Julia Boswell]

Hi

I've posted about something similar some time ago and got a response telling
me to use a union query, but I've never got it to work.... Anyone got any
more advice?

I've got 2 tables with a 1-many relationship as follows:

Table1 - ReadSignRecords, fields RSRecordID (pk), DocNo, DocName etc
Table2 - ReadSignDistribution, fields DistID (pk), RSRecordID (fk),
SentToName

Table 1 stores a list of documents and table 2 lists the people that each
document was sent to, a distribution list.

I want to run a query that shows the following:

RSRecordID, DocNo, DocName, List of SentToName

The problem is that a standard select query will show the results like this:

1, 12345, document 1, Jo Bloggs
1, 12345, document 1, Fred Smith
1, 12345, document 1, Arthur Jones

I want it to look like this:

1, 12345, document 1, Jo Bloggs, Fred Smith, Arthur Jones

Can anyone help me?

Thanks

Julia

 

[Pieter Wijnen]

You can Use a simple Function instead (in a general module)

Public Function GetDistList(ByVal ID As Long) As String

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Res As String

Set Db = CurrentDb
Set Rs = Db.OpenRecordset("SELECT SentToName FROM ReadSignDistribution WHERE
RSRecordID = " & ID,DAO.dbopenSnapshot)

While Not Rs.EOF
Res = Res & ", " & Rs.Fields(0).Value
Rs.MoveNext
Wend
Rs.Close : Set Rs = Nothing
Set Db = Nothing
GetDistList = Mid(Res,3)
End Function

HtH

Pieter

 

[Julia Boswell]

Thanks, I'll take that approach

Julia
"Pieter Wijnen"