Results 1 to 14 of 14
  1. #1
    darike is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    7

    Left Join Query returns invalid results

    I am an Access novice.

    I have a "Find Unmatched Query Wizard" query which compares the related fields on 2 tables and is supposed to return the unmatched records of table 1.

    There are only about 3500 records, so it's not a large database at this time, but it will be growing.

    Here is the SQL for the query:

    SELECT Initial_Information.ID, Initial_Information.Case_Number
    FROM Initial_Information LEFT JOIN Date_Closed ON Initial_Information.[Case_Number] = Date_Closed.[Case_Number]
    WHERE (((Date_Closed.Case_Number) Is Null));

    The results should be 76 unmatched record. The query returns 91 records. (I have verified the results comparing to an Excel spreadsheet which contains the same records and also by going through the 2 tables and comparing the matches.)

    How can I make this query give the correct results?

    Thanks for any assistance.

  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
    16,870
    Can you provide a zipped copy of your database?

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    There must be 91 records in the Initial_Information table that qualify to be included in the resulting record set.

  4. #4
    darike is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    7

    Zipped attachment included.

    orange
    Moderator



    Join DateSep 2009LocationOttawa, Ontario, Canada; West Palm Beach FLPosts10,064


    Can you provide a zipped copy of your database?


    Sorry it took this long to reply -- the database is my work records keeping database, so I couldn't access it till today. Attached is the file you requested.

    I have added new records. Now the error is at 13 incorrect records.

    What I have zipped is a subset of my database -- only the pertinent tables are included. The number of records returned is the same for this query as in the original database.

    Thanks again for any help.
    Attached Files Attached Files

  5. #5
    darike is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    7
    NTC
    VIP Windows 7 64bit Access 2013


    Join DateNov 2009Posts2,173


    There must be 91 records in the Initial_Information table that qualify to be included in the resulting record set.


    I have actually gone through the 2 tables and re-entered the case numbers to ascertain that the data is entered exactly the same on the fields which the query is based. I still find find 13 common records which should not be returned in the query. (There are 79 correct dissimilar records, not 92.)

    Thanks for your reply.

  6. #6
    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
    16,870
    I have recreated the query an find 92 records in table Initial_Information that are not in Date_Closed based on the Case_Number.
    Code:
    SELECT Initial_Information.Case_Number
    , Initial_Information.DATE_OF_INITIAL_CONTACT
    , Initial_Information.NATURE_OF_COMPLAINT
    , Date_Closed.Case_Number
    , Initial_Information.PROJECT_ADDRESS
    , Initial_Information.[PROACTIVE - REACTIVE]
    , Initial_Information.Zone
    FROM Initial_Information LEFT JOIN Date_Closed ON 
             Initial_Information.Case_Number = Date_Closed.Case_Number
    WHERE (((Date_Closed.Case_Number) Is Null));
    If there is more to your criteria than you have discussed, please tell us.

    Which records are these?
    I still find find 13 common records which should not be returned in the query.

  7. #7
    darike is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    7
    Quote Originally Posted by orange View Post
    I have recreated the query an find 92 records in table Initial_Information that are not in Date_Closed based on the Case_Number.
    Code:
    SELECT Initial_Information.Case_Number
    , Initial_Information.DATE_OF_INITIAL_CONTACT
    , Initial_Information.NATURE_OF_COMPLAINT
    , Date_Closed.Case_Number
    , Initial_Information.PROJECT_ADDRESS
    , Initial_Information.[PROACTIVE - REACTIVE]
    , Initial_Information.Zone
    FROM Initial_Information LEFT JOIN Date_Closed ON 
             Initial_Information.Case_Number = Date_Closed.Case_Number
    WHERE (((Date_Closed.Case_Number) Is Null));
    If there is more to your criteria than you have discussed, please tell us.

    Which records are these?
    Thanks for checking my query.

    The records which have closed in the "Date_Closed" table but are showing up in the query are:

    Case_Number -- (Date_Closed record number)
    16-03-127 -- (2160)
    16-06-63 -- (2198)
    16-06-88 -- (2151)
    16-06-90 -- (2152)
    16-06-149 -- (2159)
    16-07-45 -- (2179)
    16-07-51 -- (2157)
    16-07-58 -- (2161)
    16-07-80 -- (2149)
    16-07-81 -- (2150)
    16-07-90 -- (2162)
    16-07-100 --(2178)
    16-08-03 -- (2103)
    16-08-52 -- (2171)


    Actually, there are 14 mistakes.

    I don't see where there is any problem with record entry, table relationships, record formatting, query SQL or any other errors except the return of invalid records in the query.

    Thanks for any help you can provide.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,740
    If you create an unmatched query the other way, missing records in Date_Closed you will get 18 hits. 92-18 = 74. Is this a clue?

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,740
    The records which have closed in the "Date_Closed" table but are showing up in the query are:

    Case_Number -- (Date_Closed record number)
    16-03-127 -- (2160)
    16-06-63 -- (2198)
    16-06-88 -- (2151)
    16-06-90 -- (2152)
    16-06-149 -- (2159)
    16-07-45 -- (2179)
    16-07-51 -- (2157)
    16-07-58 -- (2161)
    16-07-80 -- (2149)
    16-07-81 -- (2150)
    16-07-90 -- (2162)
    16-07-100 --(2178)
    16-08-03 -- (2103)
    16-08-52 -- (2171)
    I don't see any of these in the Date_Closed table.

  10. #10
    darike is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    7
    Quote Originally Posted by davegri View Post
    If you create an unmatched query the other way, missing records in Date_Closed you will get 18 hits. 92-18 = 74. Is this a clue?
    Not really. The RIGHT JOIN query does return that number of records but the correct number of dissimilar Initial_Information / Date_Closed records, based on the Case_Number keys, is actually 78. I manually went through my Case_Closed table and the query and verified that the Case Numbers in #7 above are indeed Case Numbers which have been closed in the Date_Closed table.

    I also have this same data in an Excel spreadsheet which returns 78 open cases. -- [Excel is great for keeping some basic records and worthless for the detailed records which I need. Trouble is -- I am very proficient in Excel and a bare novice with Access -- but I'm learning.]

    How can I use the RIGHT JOIN query to return the correct records?

    Thanks again for your help.

  11. #11
    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
    16,870
    As davegri said
    I get 18 records when reversing the join.




    ID DATE_CLOSED.Case_Number DATE_CLOSED Initial_Information.Case_Number
    82 15-05-40 05-11-15
    2148 16-06-63 08-29-16
    2149 16-07-80 08-29-16
    2150 16-07-81 08-29-16
    2151 16-06-88 08-29-16
    2152 16-06-90 08-29-16
    2157 16-07-51 08-29-16
    2158 16-08-03 08-30-16
    2159 16-06-149 08-30-16
    2160 16-03-127 08-30-16
    2161 16-07-58 08-30-16
    2162 16-07-90 08-30-16
    2163 16-08-18 08-30-16
    2173 16-09-05 09-01-16
    2176 16-04-45 09-01-16
    2177 16-08-52 09-01-16
    2178 16-07-100 09-01-16
    2179 16-07-45 09-01-16

    SELECT Date_Closed.ID, Date_Closed.Case_Number
    , Date_Closed.DATE_CLOSED, Initial_Information.Case_Number
    FROM Date_Closed LEFT JOIN Initial_Information ON
    Date_Closed.Case_Number = Initial_Information.Case_Number
    WHERE (((Initial_Information.Case_Number) Is Null));

  12. #12
    darike is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    7
    Thanks guys.

    My database is small enough at this point to make some structure changes.

    I think I am going to include the Date_Closed field in my Initial_Information table which should remove to invalid returns I'm getting.

    The data for this database originated in an Excel spreadsheet. I can import the External Data from that spreadsheet to combine that data.

    My Case_Number field is the relational field in all tables.

    In practice -- normal database protocol -- is it practical to have a table which can be edited later ie: input Initial Information at the 1st use of a Case_Number and then later (maybe months later) input the Case_Closed date? That is what I do in my spreadsheet. I thought (as a Novice) that separating the 2 inputs to separate tables would be best -- now I don't think so.

    Do you know of problems which could arise from combining?

    Thanks for your help.

  13. #13
    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
    16,870
    I don't ---if you are working with a Case that has 1 Case Closed date. It is Open/Active until closed... seems fine.

  14. #14
    darike is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    7
    Quote Originally Posted by orange View Post
    I don't ---if you are working with a Case that has 1 Case Closed date. It is Open/Active until closed... seems fine.
    Thanks for your help.

    I did a little database design back in the day using FoxPro -- when it was a DOS application. I remembered that I needed to separate data into different tables. When I did my design layout, separating that field out looked like it might be better, but in use it was giving problems. Live and learn -- haha.

    Again, thanks for your help.

    Also, mark this as solved -- not aware of how to do that.

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

Similar Threads

  1. Replies: 12
    Last Post: 09-10-2015, 05:33 PM
  2. Replies: 17
    Last Post: 01-26-2014, 06:53 AM
  3. Replies: 4
    Last Post: 01-23-2014, 03:07 PM
  4. Left Join Query Issue - MS Access
    By mkc80 in forum Queries
    Replies: 1
    Last Post: 08-07-2013, 04:17 PM
  5. Replies: 3
    Last Post: 02-02-2011, 01:00 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