Results 1 to 5 of 5
  1. #1
    Shamli is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Location
    California
    Posts
    142

    Query to extract data from Two tables

    I hope I explain my problem right.
    Okay here it goes.

    4 Tables:
    Employee
    Project
    Element
    EmpToProj

    Relationships:
    Employee To Project: many to many
    Employee To Element: many to many
    Project To Element: One to many

    Forms:
    EmpToProj Form: Shows the employees allotted to the projects with the elements
    Create Entry Form: Is just the replica of what my main form works like.

    Now in the create entry form:
    When the employee is selected:
    The element Combo box should display whatever element (Here it is terminal) the employee is allotted in the EmpToPRoj table and also whatever he is not allotted should display(I know this is silly. Because of a constraint I am ending up doing this)
    The Project Combo box should display whatever projects (Under the element terminal) the employee is allotted in the EmpToProj table and also the Projects under the remaining elements.
    Important thing is only the Projects under the element "terminal" (Which is allotted in the EmptoProj table)should display. Not the other projects under the element (Terminal).

    In the images I have uploaded.
    You can see, UpChunk Shucmer : Doesn't have the projects "Project3" under the project list rest of all the projects are shown, inspite they are not present in the EmpToPRoj Table.
    I have uploaded the image of how the element combo box should display and the Project combo box should display.



    I hope I explained my problem. Please let me know if any further information is required.
    Attached Thumbnails Attached Thumbnails Element.png   Project.png  
    Attached Files Attached Files

  2. #2
    Shamli is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Location
    California
    Posts
    142
    Am I Missing out anything here? I am not sure if have to make the changes in the EmpToPRoj Table. Or just in the combo boxes in the Create ENtry form.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Try this for Elements combobox RowSource:

    SELECT DISTINCT Element.Element_PK, Element.ElementName, EmpToProj.Employee_FK
    FROM (Element RIGHT JOIN Project ON Element.Element_PK = Project.Element_FK) LEFT JOIN EmpToProj ON Project.Project_PK = EmpToProj.Project_FK
    WHERE (((EmpToProj.Employee_FK)=[forms]![CreateEntry]![EmployeeID] Or (EmpToProj.Employee_FK) Is Null));
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Shamli is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Location
    California
    Posts
    142
    Thank you so much for your reply.
    I tried the query.[
    But its not pulling all the elements, like with the "Upchunk Shumer" Its not pulling Element3.
    and the project drop down displays blank. for the elements selection other than the assigned projects.


    QUOTE=June7;406133]Try this for Elements combobox RowSource:

    SELECT DISTINCT Element.Element_PK, Element.ElementName, EmpToProj.Employee_FK
    FROM (Element RIGHT JOIN Project ON Element.Element_PK = Project.Element_FK) LEFT JOIN EmpToProj ON Project.Project_PK = EmpToProj.Project_FK
    WHERE (((EmpToProj.Employee_FK)=[forms]![CreateEntry]![EmployeeID] Or (EmpToProj.Employee_FK) Is Null));[/QUOTE]

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Your example image does not show Element3. If you want it then change the RIGHT JOIN to LEFT JOIN. But how is this any different from just showing the complete list directly from Element table?

    I hadn't looked at the Projects combo. I am similarly confused by your requirement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 09-27-2017, 07:40 AM
  2. Building a Query that can extract data and sum
    By Sara Bellum in forum Queries
    Replies: 5
    Last Post: 03-15-2013, 08:57 AM
  3. need query to extract not similar items from two tables
    By learning_graccess in forum Queries
    Replies: 1
    Last Post: 04-02-2012, 04:52 PM
  4. Replies: 13
    Last Post: 10-26-2011, 03:49 AM
  5. Replies: 3
    Last Post: 05-13-2010, 08:18 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