MS Access query: records with empty fields?

3
votes

I got a little problem with a query in MS Access 2010.

I have a huge table (tblItemRunData) mainly consisting of foreign keys. Now I want to make query out of it but in combination with the related tables to get the information behind each foreign key.

The problem I have is, that some fields in the tblItemRunData itself or in the related tables may be empty. If they are empty or more like Null, than the query won't show the records to me. I'm aware of why this is happening: http://office.microsoft.com/en-us/access-help/about-working-with-blank-fields-in-queries-mdb-HP005188534.aspx

But I can't find a way to manipulate the query to show me all the records where fields may be empty or not. I tried to set the criteria of every field I want to use to: Is Null OR Is Not Null which did not work for me.

I hope my explaining is good enough for you to understand my problem and I really hope someone got the answer for me.

I would really appreciate it. Thanks in advance.

Here are some screenshots to make it a little easier to understand (i hope it does make it easier)

Huge table with many FKs and related tables

What the query is giving back. I also tried it without the Is Null and Is Not Nullenter image description here

Records that are actually in the table. As you can see, prioID_Ref is not set for records no. 5 and some other fields are not set for record no. 4. Thats why I dont get them in the queryenter image description here

1
ms-accessvbams-access-2010
Could you add your query? Hard to tell you what is wrong with it if we don't know what it is. – dub stylee
I thought maybe someone could help me without posting the SQL. I "clicked" the query together in 'Design View' and I got like 20 ON and around 15 JOINs in there. But if you really want to see it I can edit my post. – FlouksBerlin
Sounds like your query should have tblItemRunData as the first table listed, then LEFT JOIN all tables to it by their appropriate keys. Then, your WHERE clause should look like: tblOrganization.orgID_Ref IS NULL OR tblEmployees.FinanceOwnerID_Ref IS NULL OR tblEmployees.TechOwnerID_Ref IS NULL etc., going through each joined table. – VBlades

1 Answers

2
votes

The idea of involving several tables in one Query can be both complicated and also a lot more hard to debug when an error comes up. The efficiency is dried out, as you are involving a lot more tables that might not be part of the actual Relationship Schema. If you really need them, try the best approach for any problem - "Divide & Conquer".

Try splitting the JOINS among subqueries for better result. Also the JOIN you need to involve is not the DEFAULT "INNER" JOIN. You need to have either a LEFT or RIGHT JOIN. More information on JOIN, with a very good dataset example could be found : http://www.w3schools.com/sql/sql_join.asp

You can walk though the examples to get your head around each other. With several join in many tables there is also two other problems that could affect. The returned Dataset might not be updatable. The JOIN should be performed one after an other. If you try to convert all queries into Right/Left join, then you might end up with errors on how you should not be joining.