Query to display selected records data based on fields in another table

[GTJamie]

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.

[ranman256]

The attendee table should have 2 fields:
EventID, AttendeeID

then add all people for that event.

The table is not: eventid,attendee1, attendee2,etc...

[mike60smart]

Hi
It sounds like you have your structure set up wrong.

Are you saying that 1 Event can have many Attendees?


Or are you saying that 1 Person can attend Numerous Events?

[Welshgasman]

You need a junction table that holds at least
TableID ' PK for that table, call it what you will
PeopleIDFK ' from the People table. Get rid of spaces in fieldnames, seriously.
EventIDFK ' from the event table

Then it becomes simple to get what you would need.

[GTJamie]

Thanks - 1 event can have many attendees.

[GTJamie]

Thanks. It is currently set up as you suggest. However I am trying to change it to the alternative approach that I have outlined as it makes the data input much simpler and quicker.

[mike60smart]

Welshgasman has given you the correct method for dong this.

Man Form with a Subform

[GTJamie]

Thanks for all the help. Can I ask the stupid question and ask what you mean by PK and FK?

[Welshgasman]

PK Primary key for table
FK Foreign Key in table which is the PK in another table and enables the link to exist between the two tables.

[GTJamie]

Than you all for your help - you are amazing!