Records with null values aren't being returned

  • Thread starter Thread starter Lorna_Jane
  • Start date Start date
L

Lorna_Jane

Hello,
Newbie here.
I am querying a one to one relationship, each record from table 1 has one or
no related records from table 2. I want those records with no related
records to be given a value of null, instead the record does not show up at
all.
How do I do this?
Thank-You
 
If your 'related field' is null then it can never show as a null will not
relate to anything.
If the field you say is null has criteria applied then maybe it is not null
but a 'zero lenght string.'
Use this as criteria --
Is Null Or ""

If this does not work the post your query SQL by opening in design view,
click on VIEW - SQL View, highlight all, copy, and paste in a post.
 
Hello,
Newbie here.
I am querying a one to one relationship, each record from table 1 has one or
no related records from table 2. I want those records with no related
records to be given a value of null, instead the record does not show up at
all.
How do I do this?
Thank-You
Click to expand...

One to one relationships are VERY rare, and may not be appropriate. If you're
not familiar with the terms "Subclassing", "Superclassing" or "Table driven
field-level security" it may be that your relationship is inappropriate!

That said... the query should have a Left Join. In the query grid, select the
join line and look at the join type; choose Option 2 (or 3) - "show all
records in Table1 and matching records in Table2".
 
Thanks for your help,
Here is my query, using Is Null Or "" gave a type mismatch error.

SELECT AllCatchments.UID, AllCatchments.Shape_Area,
RoadLengthPerCatchment.RLPC_M
FROM AllCatchments RIGHT JOIN RoadLengthPerCatchment ON AllCatchments.UID =
RoadLengthPerCatchment.UID;
 
Try a left join --
SELECT AllCatchments.UID, AllCatchments.Shape_Area,
RoadLengthPerCatchment.RLPC_M
FROM AllCatchments LEFT JOIN RoadLengthPerCatchment ON AllCatchments.UID =
RoadLengthPerCatchment.UID;
 
That worked, thanks a lot.
I tried changing the join type to all 3 options in the relationship editing
window, and none of them worked. I thought that would amount to the same
thing as changing the SQL from right to left. I guess it's different.
 
Back
Top