Many to One Query

[shewolf]

I'm working with a database that that tracks records sent to various users. One table contains the information on the record (sender, date, subject) and it links to a table that tracks the fact the record 1 was sent to person X, Y and Z.

My problem is that I want to build a query that will allow me to generate a report showing the details for Record One and then list the recipients, however, I keep coming up with a query that repeats the sender, date and subject information for each recipient. So end up with something like this:

Sender..............Date..................Subject...............Recipient
Mary................2/11/07..............BlahBlah...............Fred
Mary................2/11/07..............BlahBlah...............John
Mary................2/11/07..............BlahBlah...............Tom, Dick and Harry

When what I ultimately want is something that looks like:

Sender..............Date.................Subject
Mary................2/11/07..............BlahBlah
Recipients: Fred, John, Tom, Dick & Harry

I know there is probably something very simple and obvious I'm missing but I'm tired of pulling my hair out over this. As usual, any and all assistance is greatly appreciated.

 

[pbaldy]

This type of thing?

http://support.microsoft.com/kb/210163/en-us

 

[shewolf]

Sort of...

Right now I have my tables like this:

Table!Records
ID....Sender.....Date.....Subject
1 Mary 2/11 BlahBlah
2 Sally 2/12 YakityYak


Table!Recipients
RecordNo.....Recipient
1 Tom
1 Dick
1 Harry
2 Mary
2 Fred
2 Tom

And my field ID in Table!Records is an equal join to RecordNo in Table!Recipients.