Results 1 to 5 of 5
  1. #1
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165

    Employees and their Volunteer Organizations/Positions

    So I'm new to access and I'm trying to create a database that will display the organizations and positions of all employees.



    I have a couple of tables set up:
    tblEmployees
    tblInvolvement

    The Employees table has a few fields per record that are supposed to link to the Involvement table. I have been trying to get the listbox to query and list the organizations and positions that each employee has.

    Attached is what I have so far.

    I am not sure where I am going wrong, but the listbox isn't pulling up anything.

    Thanks in advance for any help.

    -Power

    I thought about using VBA and doing dlookups to check each field and then populate the listbox, but I will need to be able to generate reports where I can list employees by organization, by position, and organizations by employee (which is what the main form will actually be doing).
    Attached Files Attached Files

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You have a design flaw, the repeating position fields. You have a many-to-many relationship, typically represented with a junction table. It would have fields for the employee ID and the involvement ID. Instead of Employee ID 2 having 4 fields with positions, he would have 4 records in this junction table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Should have included a link for normalization, the "rules" you've broken:

    Fundamentals of Relational Database Design -- r937.com
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165

    :S

    Thank you for the pointers. I've fixed the tables; however, the form now doesn't want to pull the group/position for the listbox.

    Any pointers on this one?
    Attached Files Attached Files

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    One problem is that you used lookup fields in the junction table. You "see" the text value "001", but the ID is what is being stored, the integer 2. More here:

    http://access.mvps.org/access/lookupfields.htm

    That means your criteria is trying to compare apples to oranges.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 08-20-2015, 05:28 AM
  2. Setting Field positions
    By George in forum Access
    Replies: 8
    Last Post: 07-25-2015, 06:13 AM
  3. Field Positions
    By MaineLady in forum Access
    Replies: 3
    Last Post: 07-25-2015, 05:18 AM
  4. Organizations v. Individuals how to set up
    By Lena Olson in forum Database Design
    Replies: 1
    Last Post: 04-10-2015, 01:38 PM
  5. Volunteer management application
    By NOTLguy in forum Access
    Replies: 3
    Last Post: 10-01-2010, 12:04 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