Records with null values aren't being returned

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.

--
Build a little, test a little.


"Lorna_Jane" wrote:

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

On Mon, 23 Nov 2009 13:09:01 -0800, Lorna_Jane
wrote:

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

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".
--

John W. Vinson [MVP]

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;


"KARL DEWEY" wrote:

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.

--
Build a little, test a little.


"Lorna_Jane" wrote:

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

Try a left join --
SELECT AllCatchments.UID, AllCatchments.Shape_Area,
RoadLengthPerCatchment.RLPC_M
FROM AllCatchments LEFT JOIN RoadLengthPerCatchment ON AllCatchments.UID =
RoadLengthPerCatchment.UID;

--
Build a little, test a little.


"Lorna_Jane" wrote:

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;


"KARL DEWEY" wrote:

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.

--
Build a little, test a little.


"Lorna_Jane" wrote:

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

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.

"KARL DEWEY" wrote:

Try a left join --
SELECT AllCatchments.UID, AllCatchments.Shape_Area,
RoadLengthPerCatchment.RLPC_M
FROM AllCatchments LEFT JOIN RoadLengthPerCatchment ON AllCatchments.UID =
RoadLengthPerCatchment.UID;

--
Build a little, test a little.


"Lorna_Jane" wrote:

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;


"KARL DEWEY" wrote:

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.

--
Build a little, test a little.


"Lorna_Jane" wrote:

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