Results 1 to 6 of 6
  1. #1
    andrewrossdouglas is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    3

    Many to Many table design in Access

    Morning all,

    I am a novice. I have a basic Access database with two tables. The tables relate to staff and activity. Many staff can be assigned to one activity and all activities can have many staff assigned. My staff table is realtively static, but the activity table is dynamic with many records. I cannot seem to create a mechansim by which I can see chosen staff, and their data and selected fields, when I am working with the activity records. I have looked at a number of on line tutorials talking about joining tables, but the documentation on these appears to be sparse. In short, the activity element of the databse is the main focus and I need to see all staff data relevant to the chosen activity in both forms and reports.

    Anyone who can assist will have my undying gratitude.



    Andrew

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    In Basic terms , If your activity table lists activity types then the joining table (ActivityEvents? ) would have the Staff ID , the Activity ID and then the Activity data, probably StartDate, EndDate and any other Event related data. I suspect you are may be struggling as your data may not quite be normalised correctly yet.

    Without seeing your table designs this is all a bit speculative.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    andrewrossdouglas is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    3

    Getting there

    Quote Originally Posted by Minty View Post
    In Basic terms , If your activity table lists activity types then the joining table (ActivityEvents? ) would have the Staff ID , the Activity ID and then the Activity data, probably StartDate, EndDate and any other Event related data. I suspect you are may be struggling as your data may not quite be normalised correctly yet.

    Without seeing your table designs this is all a bit speculative.
    Minty,

    I have made progress and can now access the staff data in reports based on the activity forms. So far so good. Very basic sandpit table design attached(Capture 2). My issues now is that when I open my activity form. I can see the half datasheet from the junction table. So, on the form I can select a staff member to be allocated to the various activity records. You can see this is my first attachment (Capture 1). What I need to see is not the staff number, but their name? Obviously Access needs to store the ID number FK to maintain the data integrity with the Staff table. (I tried adding another field to the junction table to display the staff name)

    I hope I have explained this adequately.

    Andrew
    Attached Thumbnails Attached Thumbnails Capture2.PNG   Capture1.PNG  

  5. #5
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You are close - you don't need the staff name in the junction table, as that can be pulled from the Staff ID.
    I would also rename your ID fields as StaffID, CourseID and CSJunctID , as later on you'll have no idea which ID field you are trying to query...

    In your combo box set the row source to query staff table, but pull in the First & Last Name as a calculated field and display it, you can also hide the Staff ID number for "prettyness", and sort it by name.
    If you get stuck post a stripped down version of the database and I'm sure we can demonstrate the technique.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    andrewrossdouglas is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    3

    Getting closer

    Quote Originally Posted by Minty View Post
    You are close - you don't need the staff name in the junction table, as that can be pulled from the Staff ID.
    I would also rename your ID fields as StaffID, CourseID and CSJunctID , as later on you'll have no idea which ID field you are trying to query...

    In your combo box set the row source to query staff table, but pull in the First & Last Name as a calculated field and display it, you can also hide the Staff ID number for "prettyness", and sort it by name.
    If you get stuck post a stripped down version of the database and I'm sure we can demonstrate the technique.
    Minty,

    I am getting closer to a solution here. I will play a little more and see if I can get the solution myself. Thanks for your advice and guidance. If I get stuck I will post the cut down version and seek some advice

    Andrew

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

Similar Threads

  1. Replies: 1
    Last Post: 01-17-2018, 04:14 AM
  2. Replies: 5
    Last Post: 06-03-2016, 09:22 AM
  3. Table design for a dates driven workflow in Access
    By mbenton in forum Database Design
    Replies: 6
    Last Post: 05-03-2016, 08:39 AM
  4. Replies: 4
    Last Post: 02-25-2014, 01:34 AM
  5. Access 2003: New table design help
    By bmantz65 in forum Access
    Replies: 2
    Last Post: 03-15-2013, 11:37 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