Results 1 to 10 of 10
  1. #1
    hhof0407 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    2

    Relationships question

    I知 trying to set up an attendance roster for variousevents. I have a set list of people who need to be signed up for various eventdates. There are multiple event sessions a month and typically the client signsup for one per month. There are 3 sessions a month for a total of 36 events. I知trying to figure out how to set the relationships that allows each event rosterlist from the overall list of clients. And will show what events each client isregistered for. So I知 assuming this is a many-to-many relationship?


    I知 hoping to end up with the final table combinations:
    GeorgeWashington (registered for Session #1, Session #3 and Session #4)
    John Adams(registered for Session #1, Session #2, Session #3)
    Ben Franklin(registered for Session #2)

    Session #1 Registration(George Washington, John Adams)
    Session #2Registration (John Adams, Ben Franklin)
    Session #3Registration (George Washington, John Adams)

    So far I've created tables for Client information, Session dates, Registration status(registered and reminder contact been made). Not sure if this is the best way to set things up.


    I知 very new to access, so any advice is appreciated.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your registration status table will be the table to control your many to many relationship

    I'm assuming session dates includes more than just a date - perhaps location, who's running it etc

    it should look something like

    tblRegistrations
    RegistrationPK autonumber
    SessionFK long - link back to tblSessionDates
    ClientFK - long - link back to tblClients

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Yep sounds like you have it. Maybe something like:

    tblSession - SessionID(autonumber) - each unique session description and related info specific to that event
    tblClient - ClientID(autonumber) - unique client info list
    tblRegistration - RegistrationID(autonumber), SessionID, ClientID, SessionDate, etc. (SessoinID and ClientID will link back to those tables)

    The relationship would be:
    tblSession to tblRegistration on SessionID (1 to many)
    tblClientID to tblRegistration on CliendID (1 to many)

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There are multiple event sessions...There are 3 sessions a month
    Not sure what is the difference between an event, event session and a session, or if they'reN) the same thing. I'm guessing a session is a many part of a single event. If that's the case, and Event1 can have 3 sessions, you should have a tblClients, tblSession and tblEvents. The event id would appear in the session table for each session related to the event. The client id would appear in the session table for each client related to the session, along with their registration status (Y/N). Not sure I see the need for a registration status table.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    hhof0407 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    2
    Click image for larger version. 

Name:	Capture.PNG 
Views:	19 
Size:	14.6 KB 
ID:	25243

    So I don't think I'm there yet. I don't know if these even makes sense. But these are the things I'm trying to link up.

    Essentially each contact should have a user profile (Contact table-this is the easy part!)

    Each contact can sign up for multiple group sessions (Group Dates table)

    Each contact should be listed as signed up for the date and need reminder contacts for each Group that they are signed up for. So this needs to be the interactive part, where someone can log if they contacted that person in regards to a specific group date. A contact can sign up for multiple groups and have multiple contact attempts made.

    Should I merge any of these tables? Would it work to include the contact attempt information with the Group Dates?

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you are addressing me, I'm on the road now and cannot elaborate until tomorrow if that's OK
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In my view you would do yourself a great favor by spending 45 min to 1 hour working through one or two tutorials from RogersAccessLibrary. You have to work through the tutorial but you will learn how to identify business facts, entities and how to identify/create relationships.

    Here are 2 tutorials:
    Class info
    Consolidated Widgets

    You should avoid a naming convention that allows embedded spaces in field and object names.

    If you build a data model based on your business facts, you can test that model before getting too involved in Access and forms.

    Good luck.

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I would also recommend better and consistent naming of fields. For Contacts table, name the key ContactID. Then in your linking table, call it ContactID also, not CaregiverID so it does not get all confusing. Do this with the other tables if possible. And definitely as orange said, keep spaces or special characters our of object names.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    following on from Bulzie's advice I would go on step further. Use ContactPK for a suffix for primary keys and ContactFK for family/foreign keys. And avoid spaces in field names like the plague - totally unnecessary, likely to cause problems and take ages to resolve. If the reason for spaces is so that it 'looks right', use the caption property, that is what it is there for.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Orange has valid points. Also, make your related names more intuitive as suggested - simply removing spaces is not enough. For example, if you have CareGiverID as the many side of ContactID, later on, you will struggle with this since the names don't suggest a connection. I personally don't use the FK PK idea. I prefer to use ID for the main table, and in the related table, I use the related table name with "ID" so I know where it comes from, which also tells me it's foreign. For example, by reading a sql statement having tblClient.ID = tblContacts.ClientID I know that tblConacts.ClientID comes from tblClient. When you drag these tables into query design view, it's pretty much intuitive even without the automatic joins provided by your relationship model. Not that this is better than any other method - just different and is my preference.

    Considering the relationship view (with perhaps one field not visible in the Contacts table) and your description of the requirements, I wonder if you are mixing contacts (as in contacting someone) and client information. The view suggests you are storing client/student/worker etc. type data, and your requirements suggest you wish to record each notification to that "client" concerning a session. In that case, I'd have a notifications table that relates the client id and the session id to each dated contact attempt. If that's what you're building by having 3 contacts field in a status table, I think that's a spreadsheet mentality (row data in columns). I won't offer a relationship for what I'm suggesting at this time since I don't think I fully understand the model.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Begginer Relationships Question
    By dluga20 in forum Access
    Replies: 8
    Last Post: 06-11-2015, 05:57 AM
  2. Relationships question- field not showing up
    By teacherdude in forum Access
    Replies: 1
    Last Post: 01-27-2012, 05:59 PM
  3. Relationships Question(s)
    By Exwarrior187 in forum Database Design
    Replies: 9
    Last Post: 02-14-2011, 02:12 PM
  4. Another relationships question
    By canfish in forum Database Design
    Replies: 0
    Last Post: 07-28-2010, 02:23 PM
  5. question about relationships
    By grad2009 in forum Access
    Replies: 3
    Last Post: 02-16-2010, 06:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums