I'm writing an Access app for a Speed Dating company.

After an event, the organizers enter the Feedback data, which lists the people that each participant would like to meet for a private date. This info is stored in a table.

I have a query which finds those couples who have picked each other, i.e. a two-way match. I need to generate a series of reports, one for each participant, listing those partners with whom they have a two-way match.

I can't figure the logic I require to generate these reports. The output from my FindMatches query results in two columns of usercodes indicating the matched participants. But each column contains multiple records for each person, depending on the number of participants they have matched with. I don't know if I need to group this data at the report stage, or filter the query, or if the whole mess is best solved in VB.

Any insights would be most welcome, as this is driving me batty!

Regards,

Brendan

the way I would do this is with a join table.

tblMatches
MatchID (Primary Key Autonumber)
EventID (foreign Key)
ParticipantID (FK)
SelectionID (FK)

Both the PartiticpantID and SelectionID would join back to the PK of the main table (i'll refer to it as tblPartiticpants). One as an alias. To do this, go into Query Design mode and add tblMatches, tblPartiticpants and then add tblPartiticpants again. It will be added as tblPartiticpants_A (I think). Then join PartiticpantID from tblMatches to PartiticpantID in tblPartiticpants and SelectionID to PartiticpantID in tblPartiticpants_A. You can then add the fields you want to include in your report.

For the report, Group on PartiticpantID and put the SelectionID and its matches inth e Detail band. This should give you a report for each person listing the people they selected.

Thanks Scott.

I was half-way to your solution in that the query used to find the matches uses a self-join, but I hadn't thought of using a join table.

If this works I'll sacrifice my firstborn to you.

Regards,

Brendan

<Later>

Scott,

It worked like a charm!

Firstborn put up a bit of a battle, so I hope you'll settle for a virtual pint. o!

Thanks & Regards,

Brendan

Gulp