Retrieve all records in query (even blank)

[Kell2604]

Hi -

This is probably a simple question...I just don't know the answer. (I'm
still an Access newby) I am running a simple query and have 2 tables linked
by a unique value. I am brining in a couple of fields from each table. My
problem is that if any of the fields are blank in the original tables it
drops those records out of the query. So I basically get only records that
have some data in every field. But, I want to get ALL of the records from the
tables even if there happens to be data missing from some of the fields.

Hope this makes sense. Thanks very much for your help!!

Kelley

 

[Brian]

The problem likely occurs when the null field is the unique value itself.

Right-click the line that joins the tables in the query builder. Go to the
JOIN properties and select the option that includes ALL records from that
table and only matching records from the other table.

This assumes that the value would be null in only one table. It will be more
complex if that field could be null in either table.

 

[Kell2604]

That did it Brian - thank you so very much!!

The records are only blank in one table...so this is exactly what I needed!!

Thanks again,
Kelley

 

[Brian]

....and you can, if necessary sometime, do the same thing and then limit the
query to those records where that field is null in the second table. This
will show you all records that exist ONLY in the first table but NOT in the
second. For example, customers that have never placed an order, etc.