I am an access beginner so apologies if this is a stupid question (or has been answered many times before - my searching hasn't found a solution.
I have a table called 'People' that contains individual's details and each record has a unique, numeric, 'People ID'. A second table called 'Events' contains details of the events and every record has a unique 'Event ID' and fields that contain details of attendees eg 'Attendee 1'. Attendee 2' etc. A new record is created for every new event and 'Attendee' fields are filled out through a form using a combo box that allows a user to select the participants name from 'People' and populates the 'Attendee' record with the 'People ID' for that participant. So an event would have a record showing, for example: Event ID = 39, Attendee 1 = 21, Attendee 2 = 36, Attendee 3 = 47 etc
I want to run a query that shows all the individuals details (ie all fields from 'People') as a record for each attendee of the event.
I set up a relationship between 'People ID' and 'Attendee 1', and applied the criteria filter based on 'Event ID'. This successfully brings through all the data for Attendee 1.
I thought that by setting up another relationship between 'People ID' and 'Attendee 2' this would pull through records for both attendees. However, it doesn't and I just can't make it do what I want. Any help (set out in laymans terms please!) would be amazing - thanks in advance.