CODEWITHSUNDEEP
ms-accessleft-joininner-joinresultsetmissing-data
Wilskt
Wilskt

Reputation: 347

Access 2007 - Left Join returns correct results, Inner Join returns nothing

I have a query that the only way I could get it to work was to left join, on three fields. If I did an ordinary inner join on these three fields the query returned nothing. But if I try each individual join separately, they all join as I would expect, e.g. Bob to Bob, Bookshop to Bookshop, Bread to Bread etc.

So for these two sets of query results...

1.Manager  1.Shop     1.Product  1.Cost     2.Manager  2.Shop     2.Product   2.Quantity
Bob        Hardware   Spanners    15        Bob        Hardware   Spanners     3
Terry      Food       Bread       12        Terry      Food       Bread        4
Sue        Bookshop   Books       18        Sue        Bookshop   Books        7

...this query returns no results:

SELECT 1.Manager, 1.Shop, 1.Product, 1.Cost, 2.Quantity
FROM 1 INNER JOIN 2 ON 1.Manager = 2.Manager AND 1.Shop = 2.Shop AND 1.Product = 2.Product;

I know joining on text isn't ideal, but I have similar queries that join on these three fields without problem, so wondered whether it was a 'feature' of Access that I had encountered, or whether it's likely to be a problem in the data?

-edit-

By putting the JOIN conditions into the WHERE clause instead, I found that, if I have WHERE 1.Manager = "Bob" AND 2.Manager = "Bob:

WHERE 1.Product = "Spanners"

works on its own, and:

WHERE 2.Product = "Spanners"

works on its own, but combining the two:

WHERE 1.Product = "Spanners" AND 2.Product = "Spanners"

again returns nothing!

-edit 2-

The main query does indeed behave properly when it is referencing the data in tables. So there may be something odd about the way the base queries return their results.

-edit 3-

This is the link to an example of the problem: [link removed]

01 Top Level Queries: both of these are the same, but that one refers to tables, and works; and the other refers to queries, and does not work. I want to find out why the query version doesn't work.

02 2nd Level Queries and Tables: there are two versions of each set of data - one is a query, and the other is a table made using a Make Table version of the query. Both are identical as far as I can tell.

03 and 04 Level Queries: these are lower level queries that go to make up the 2nd level queries

Tables: these are the base tables that all other queries are built on.

Upvotes: 1

Views: 832

Answers (3)

user7757733
user7757733

Reputation: 11

Had the same problema here in the future (year 2017, Access 2010). For some reason, Left Join would work bringing the exact same result Inner Join brought and mysteriously stopped. After "Feb 11 '13 at 9:54" message, I noticed that one of the joined queries had doubled Group By fields not showing (no reason for that), so I deleted them. It worked. Access recreated the no-show Group By fields, but not doubled anymore, and that was the (bug?) problem.

Upvotes: 1

Wilskt
Wilskt

Reputation: 347

For the record, if anyone looks at this question having a similar problem - one of the queries that fed into the main query was grouping on a field that didn't appear anywhere in that particular query. Once I'd removed that field from the Group By clause the main query returned the results I expected.

Odd that a query was essentially returning exactly the same results with different behaviour, but there you go.

Upvotes: 1

Peter Lake
Peter Lake

Reputation: 338

OK, so I downloaded your db and took a look. I got as far as finding that if you put the NumStores query first in you inner join then it would return records, then abandoned ship. I don't want to sound harsh but you are so far down the road of poor database design you have no hope of going further. Among the many issues that will continue to cause you problems are: No primary keys in your tables (no indexes of any kind). Incomprehensible naming convention for your objects (queries and tables). Data is duplicated in many different tables (normalization violations). Embedded subqueries in your main queries. If you want to use Access to help you you need to learn how to use it.

Upvotes: 2

Related Questions