Results 1 to 5 of 5

Question/Issue: Referencing Table via lookup maybe?

  1. #1
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    34

    Question/Issue: Referencing Table via lookup maybe?

    Hello Access Team!

    Have a problem I am running into. Here is the situation:

    Tables created=
    - [EmployeeTable] which has ID, Last, First "All seperate Columns"
    - [EmployeePositionTable] which has ID, Last, First, PositionName, PositionCode, PositionRequirements 'All seperate Columns'
    - [RefernceTablePositions] which has PositionName, PositionCode, PositionRequirements 'All seperate Columns'

    Queries Created=
    - [AppendLastFirstToEmployeePositionTable] which has ID, Last, First 'Creating a One-To-One Relationship' New Employees added to [EmployeeTable] will be appended to [EmployeePositionTable]

    Forms Created=


    - [EmployeePostionForm] 'This form is a split Form. ID, Last, First are locked and cant be changed.

    Here is the problem: In the form view under PostionName I would like it to be a dropdown box "LookUp" to select a postion based on the referance table. Once the selection is made, I would like PoistionCode and PositionRequirements to autopopluate on the form which sets the column value for that table.

    Attached is my example database to work out the bugs prior to implimenting into my operational database.

    Thank

    autoPopfields.zip

  2. #2
    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
    14,231
    I think you table structures need some work(normalization).


    Employee---->EmployeeOccupiesPosition<-----Position--->PositionRequirements

    Here's a related draft model from a slightly different request from a while back:
    deals with Employees, Positions, EmployeeTraining, PositionRequirement, TrainingCOurses

    Click image for larger version. 

Name:	EmployeePositionTraining.jpg 
Views:	26 
Size:	40.9 KB 
ID:	39902

  3. #3
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    34
    Quote Originally Posted by orange View Post
    I think you table structures need some work(normalization).


    Employee---->EmployeeOccupiesPosition<-----Position--->PositionRequirements

    Here's a related draft model from a slightly different request from a while back:
    deals with Employees, Positions, EmployeeTraining, PositionRequirement, TrainingCOurses

    Click image for larger version. 

Name:	EmployeePositionTraining.jpg 
Views:	26 
Size:	40.9 KB 
ID:	39902
    Thank you for the feedback. I know that I can split the reference table a little more but how will that allow for positioncode and positionRequirements to auto populate based on the position selection made on the form?

  4. #4
    JoeJr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    34
    I figured it out. I need to write a little VBA code to get it to do what I wanted.

    Thank you for your help.

  5. #5
    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
    14,231

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

Similar Threads

  1. Simple Sub-Form Lookup Table Updating Issue
    By tmptplayer in forum Forms
    Replies: 8
    Last Post: 08-09-2017, 07:45 PM
  2. table lookup issue
    By Jen0dorf in forum Access
    Replies: 3
    Last Post: 06-12-2017, 03:12 PM
  3. Lookup Table Question
    By Beanie_d83 in forum Access
    Replies: 2
    Last Post: 06-17-2016, 07:38 AM
  4. Lookup Table Question
    By Leonidsg in forum Database Design
    Replies: 2
    Last Post: 01-21-2013, 08:29 PM
  5. Table Lookup Question
    By Atlascycle in forum Access
    Replies: 2
    Last Post: 02-21-2012, 03:28 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
  •  
Tech Forums: Microsoft Office Forums