none
Populating Tables w/ Relationships RRS feed

  • 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!

    Monday, December 5, 2016 4:17 PM

All replies

  • 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

    Monday, December 5, 2016 4:26 PM
  • 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

    Monday, December 5, 2016 10:35 PM
  • 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 MSDNFSF@microsoft.com.

    Tuesday, December 6, 2016 12:39 AM
    Moderator