Results 1 to 3 of 3
  1. #1
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52

    Query to detect the lack of a record.

    Hi



    I have a database with two tables Tbl_Starter_Leaver and Tbl_RADS. So

    Tbl_Starter_Leaver
    AutoNumber
    Effective Date
    Surname
    etc...


    Tbl_Rads
    Autonumber
    Starter_Leaver_ID (Foreign Key)
    Contracted Hours (for starters as above)
    Site
    etc.

    I have two Forms. The Foreign Key on Tbl_RADS is the autonumber of the Tbl_Starter_Leaver. Table.
    a) A form to add records to Tbl_Starter_Leaver (Different set of users)
    b) I have a Split form that Show both tables (Tbl_Starter_Leaver & Tbl_RADS) on the one form.

    The problem is that some users add record to the Tbl_Starter_Leaver table but there does not need to be a record on Tbl_RADS record. (different users).

    The problem is that when I create a Query to report on both tables only Tbl_Starter_Leaver record with associated Tbl_RADS record are returned.

    How can I get all data to be returned even if there is no Tbl_RADS record present. ?

    Thanks in advance.

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You need a LEFT JOIN (also called an OUTER JOIN) between the two tables.

    In query design view, right-click the join line between the two tables, then select Join Properties.

    Select either option 2 or option 3, according to how you have designed it - you want all records from Tbl_Starter_Leaver.

  3. #3
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Fantastic...

    Thank you so much.

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

Similar Threads

  1. Replies: 0
    Last Post: 06-22-2016, 01:33 PM
  2. Virus or lack of intelligence?
    By psbailey42 in forum Access
    Replies: 1
    Last Post: 09-04-2013, 10:37 AM
  3. Lack of method in VBA code
    By sseba15 in forum Programming
    Replies: 7
    Last Post: 09-19-2011, 11:53 PM
  4. Replies: 4
    Last Post: 09-05-2011, 11:10 PM
  5. How do I detect new records.
    By jcollins in forum Programming
    Replies: 3
    Last Post: 03-17-2011, 03:48 PM

Tags for this Thread

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