Query with filters not working!!!! I made a video to explain :(

[SebastianColombia]

First of all, god morning/afternoon/evening as the case may be. I've been struggling with a query that hasn't worked. I made a video in order to try to explain what's happening. Hope you can help me.


<ins class="adsbygoogle" data-ad-client="ca-pub-8768391963079401" data-ad-slot="7415104082" data-adsbygoogle-status="done" style="text-decoration: none; color: rgb(51, 51, 51); display: inline-block; width: 728px; height: 90px; background-color: rgb(250, 250, 250);"><ins id="aswift_0_expand" style="text-decoration: none; display: inline-table; border: none; height: 90px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 728px; background-color: transparent;"><ins id="aswift_0_anchor" style="text-decoration: none; display: block; border: none; height: 90px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 728px; background-color: transparent;">******** width="728" height="90" frameborder="0" marginwidth="0" marginheight="0" vspace="0" hspace="0" allowtransparency="true" scrolling="no" allowfullscreen="true" id="aswift_0" name="aswift_0" style="left: 0px; position: absolute; top: 0px;">*********></ins></ins></ins>
Thanks and excuse me for my English!!!

https://www.youtube.com/watch?v=Eh7G...ature=youtu.be

 

[james_lankford]

let's say your first table is a list of all the stores in your city
clothing stores, bicycle stores, shoes stores and grocery stores

lets's say the middle table is a list of departments or sections
maybe headgear, fruit, tires, clothes, vegetables, footgear

let's say the third table is specific items
helmets, hats, bananas, mangos, bicycle tires, car tires, farm tractor tires, carrots, peas, socks, boots

so you make the first join -- show me all stores and link to departments

so you'll have
grocery stores linked to fruit departments and vegetables departments and null (blanks) where grocery stores are linked to headgear, clothes, footgear and tires
and you'll have
clothing stores linked to headgear departments, clothes departments and footgear departments and null (blanks) where clothing stores are linked to fruit, tires, and vegetables

but you ARE getting EVERY row from the store table, just the way you expect

but now you make the 3rd link
and you say
show me ALL stores linked to ALL departments where the department sells mangos

what do you expect to see ?

do you still expect to see ALL stores ?

think about it

you said
show me ALL stores linked to ALL departments where the department sells mangos

well, a clothing store doesn't have a department that sells mangos
but grocery stores do

so all the grocery stores will still be returned,
but all clothing stores are going to be excluded from the result

there are NO clothes stores that have departments that sell mangos
so the query really is showing you ALL stores that have departments that do sell mangos

when you put a criteria (a restriction) on the far right table you end up restricting the results
that is the way it is supposed to work

the way to fix it is to split it into 2 queries

take the middle table and the tables on the far right and make one query

so,
link the department table with the items table and say
show me ALL departments that sell mangos

this will eliminate headgear, footgear, clothes and vegetable departments and will return all fruit departments (and any other department) that sells mangos

^ let's call this queryResults

now make another query using the first table and the query we just made

so link the stores table to queryResults

and in this query you can say
show me ALL stores linked to the departments returned in queryResults

and that will give you what you want

you will have grocery stores linked to their fruit departments
and you will have clothes stores with nulls (blanks) departments

makes sense ?

 

[SebastianColombia]

OMG!! Thanks a lot!!!!! I’m new in Access and I appreciate the way you explained it to me, you should write a book for dummies !! I wish I had a teacher like you! . Now I understand more about relationships.

If it isn't too much to ask, I wonder if you can help me with the following question: Does the same apply for calculated fields in queries??, beacause with the new query I made (as you taught me), I’m linking the table on the left and the query I made with the table on the right and the filters. But then I créate a calculated field to sum both fields from the table on the right and the table on the left, but it isn’t adding well L

I made another video and I’d really really appreciate if you took the time to see it.

https://www.youtube.com/watch?v=KqlLxO4X7Uc&feature=youtu.be

Thanks!!