Populating Tables w/ Relationships

Question

Hello! So I have this giant roster of people and the different locations they are supposed to be at for different events. Is there a way I can create location tables that state who are supposed to be there and for which event?

So what I'm looking for is;

Roster

Name  | 5K

Debbie | Water Station

And what I want to get is

Water Station

5K        |    10K       

Debbie  |   Greg

Thank you for your help!

Answer 1

Your table to show who should be where is akin to a Assignment table. You need the location ID, Event ID (which would imply a date along with the event itself) and finally, a staff ID to show who is manning the location.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 2

Presumably Event will determine Location, so in the Events table will be a foreign key which references the primary key of the Locations table.  You then need an EventEmployees  table which models the binary many-to-many relationship type between Employees and Events:

 

EventEmployees  

….EventID  (FK)

….EmployeeID  (FK)

 

The primary key of this table is a composite one of the two foreign keys

 

For data entry you then simply need an events form in single for view, which will include a combo box bound to the LocationID foreign key column.  Within the form a subform based on the EventEmployees  table, in continuous forms view, linked to the parent form on EventID.  The subform will include a combo box bound to the EmployeeID column.

 

To produce a roster report you would base this on a query which joins all the tables and returns whatever columns are necessary for the data you wish to see in the report.  The report can be grouped by event to show the employees per event, or by employee to show the events per employee.

 

For an example of this sort of basic many-to-many relationship type and how to represent it in forms see StudentCourses.zip in my public databases folder at:

 

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

 

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly. 

 

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

 

This little demo file illustrates a number of possible interfaces, but a conventional form/subform is simple to build and code-free.  A simple report of courses per student is also illustrated.

---

Ken Sheridan, Stafford, England

Answer 3

Hi ,

here is an simple example that you can try to refer.

first you need to create 3 Tables named (1) person (2) event (3) location like below.

then you can create query like mentioned below to fetch the data.

SELECT person.p_name,location.l_name,event.e_name from 
(
  location 
  inner join
  person
  on  location.l_id=person.l_id
)
  inner join 
  event
  on person.e_id=event.e_id;

Output:

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].