Query a 2nd table

[imwitti]

Hi Guys,

Very new at this so should be an easy answer.

I have a number of workshops each has its own record in the table "workshop"

in "workshop" i have fields [Attendee 1][Attendee 2][Attendee 3] ect up to 6


These attendees fields are filled by an attendee Name from my table "Contacts" which has fields [Name][Address][Job Title].

This all works fine for collecting attendees in my Workshops.

The problem i have run into is i now want to create a report on the attendees in each workshop. It is easy to make a report that lists my attendee names as it is part of my main table but how do i get the report to list each attendees address and Job Title on the same report.

Thanks in advance.

Michael

 

[recyan]

Is this the way your tables are structured ?

tblWorkshops

WorkshopID - PK
WorkShopName
...

tblContacts

ContactID - PK
ContactName
ContactAddress
ContactJobTitle

tblWorkshopsContacts

WorkshopsContactsID - PK
WorkshopID - FK
ContactID - FK

Thanks

 

[imwitti]

Is this the way your tables are structured ?

tblWorkshops

WorkshopID - PK
WorkShopName
...

tblContacts

ContactID - PK
ContactName
ContactAddress
ContactJobTitle

tblWorkshopsContacts

WorkshopsContactsID - PK
WorkshopID - FK
ContactID - FK

Thanks

Hi just the workshop and contacts tables. i have no combined table.
im useing the contacts full name as a lookup to fill the attendee field in the workshop table. Now i would like a query that has the workshop, particular attendee and his address ect as well so information coming from both tables.

 

[recyan]

Somehow, not comfortable with the way the tables are structured ( though what you want can also be possible with the present set-up ). Let us just wait for someone to come along.

Thanks

 

[cookiegary01]

I would do it like this:
Main table = Main Form: tblWorkshops
WorkshopID
WorkShopName
(Remove attendees from this table)
2nd Table=Subform of Main form; TblAssignedWorkshops
IdAssignedWorkShop
IdContact (here you select the contact = attendee)
tblContacts
ContactID
ContactName
ContactAddress
ContactJobTitle

Relationship between TblWorkshops and TblAssignedWorkshops
Relationship between TblAssignedWorkshops and TblContacts

Tip: Do NOT use [Name] as a field name