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 Null
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 query
Best Answer
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.