Access Query to make a complex filter

[Guest]

I have a table that contains multiple information about meetings between
companies. The table consists of a meeting key, a foriegn key from companies
and a foriegn key that relates to the time/date slot.

What i would like to do is produce a list of all the times and companies
that a specific company will be linked to. I can produce a query to show all
time slots that a company is in, by creating a query with all the relevent
information and filtering on the company ID. Equaly I can find out all the
companies that are attending a specific meeting by building a similar query
and filtering on time slot.

However i want a list that shows for any given company all the time slots
they are booked into, and who it is that they are meeting with, all on the
one list.

Any assistance at all would be verry verry much appreciated. Further
information is ofcourse available if neccasary

 

[Allen Browne]

A meeting can only be at one date/time, but will have multiple companies
attending. You will therefore have a Meeting table with fields:
MeetingID primary key
MeedingDateTime when the meeting is on.
LocationID where the meeting is held.

Then you have a MeetingCompany table, with fields:
MeetingID foreign key to Meeting.MeetingID
CompanyID foreign key to Company.CompanyID

Now you want a query showing all the meetings a particular company is in:
SELECT MeetingID
FROM MeetingCompany
WHERE MeetingCompany.CompanyID = 999;

And next you want to list all the other companies in that meeting as a
concatenated list. For that part see:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm