Results 1 to 4 of 4
  1. #1
    TheMote is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    4

    Criteria preventing query join properties

    Hi,



    I am running a query using the following two tables:

    Tbl_Company (contains names of companies)
    Tbl_Contacts (contains names of people employed at one of the companies in the "Tbl_Company" table)

    The Join Properties are such that I am showing all records from "Tbl_Company" and only those records from "Tbl_Contact" where the joined fields are equal (this is intentional because I want to see all companies listed, regardless of if they have a contact). However, for the companies that do have a contact, I want to add a criteria to only show active employees (a yes/no field data type) - but adding this criteria to the "Tbl_Contacts" table is preventing all the companies to show up in the query. Is there any way I can show all companies regardless of if they have a contact, but have a criteria assigned to the contact if the companies does has a contact?

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the join should be on
    Tbl_Company.CoID and
    Tbl_Contacts.CoID

    Tbl_Contacts table should be:
    EmpID
    CoID

    if the
    Tbl_Contacts table is the Employee table for that company, then theyd also have
    FirstN
    LastN
    Addr
    Active
    etc...

    instead of joining to tContacts table, create a query for only active emps, qsContactsActive
    then the subform is bound to
    qsContactsActive

    I have a combo box to swap out the data source
    Active ;
    qsContactsActive
    All; qsContactsAll
    etc..


  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
    Try adding an "OR" criteria of Is Null on the company ID field in the contacts table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    TheMote is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    4
    ranman256, creating a query specifically for active employees - and then joining that with the Tbl_Company - did the trick!

    Thank you so much!

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

Similar Threads

  1. Replies: 5
    Last Post: 08-06-2018, 10:30 AM
  2. Replies: 8
    Last Post: 03-15-2018, 11:51 AM
  3. Replies: 4
    Last Post: 08-01-2016, 06:41 AM
  4. Replies: 1
    Last Post: 07-24-2014, 08:35 PM
  5. Join Properties - Please Explain
    By Howard in forum Access
    Replies: 2
    Last Post: 01-11-2014, 12:31 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