Not returning all records

[cbende2]

Hello all,

So I'm having a problem building a query.



I have 3 tables total that I will be pulling information from.

Table1 has 3 fields (field1, field2, and field3) that are linked to Table2

Table1 also has 2 fields (fieldA, fieldB) linked to Table3

A regular Query on table 1 returns 627 records.

Once I add Table2 to the Query, I delete the relationship of three fields, and make it to a single field (field1). (the reason I do this is because if I leave the Table linked to 3 fields, it returns no records.

Now when I run the query I'm only getting 412 records. The ones that are missing either have the value 0 or Null for that field (field1), How do I allow the query to return these records as well?

I tried looking into the Nz() function but I couldn't quite grasp how to use it in the query builder, anyone mind explaining to me?

Thanks

[John_G]

It's because some records in table1 do not have related records in table2. You need to make the relationship an outer join. (for A2010) Right-click the join between the two tables, select Join Properties, then select the option that says "Include ALL records from table1....".

That should give you all table1 records.

If there are records in table2 that have Null or 0 in field1, you cannot get them - there are no records in table1 to join them to. You could have the outer join the other way - "include ALL records from table2....", but it depends on what you need.

[cbende2]

Ok thanks!

After thinking and talking with my boss, we decided on having a record named "Unassigned" which actually fits our needs better. I simply ran an update query on all 0 and Null values to update to ID of Unassigned.

Thanks for helping though John_G!