Left Join Query returns invalid results

[darike]

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.

[orange]

Can you provide a zipped copy of your database?

[NTC]

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

[darike]

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.

[darike]

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.

[orange]

I have recreated the query an find 92 records in table Initial_Information that are not in Date_Closed based on the Case_Number.

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.

[darike]

I have recreated the query an find 92 records in table Initial_Information that are not in Date_Closed based on the Case_Number.

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.

[davegri]

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?

[davegri]

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.

[darike]

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.

[orange]

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));

[darike]

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.

[orange]

I don't ---if you are working with a Case that has 1 Case Closed date. It is Open/Active until closed... seems fine.

[darike]

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.