Join Properties Problem

[Jim Moore]

I am having a problem getting all the records in my main table to show up on
a query which has a Left Join to a second table. SQL follows for a Query -
[Audit Data] is the main table
[Behaviors] is linked to [Audit Data] on the field [Index]
[ActionUpDate] is also linked to [Audit Data] on [Index] and I set Join
Properties to iclude all records from Audit Data and only those from
ActionUpDate where Index is equal.

When I run the Query, only records in Audit Data that have a match to
ActionUpDate show up. As an example, if I do not include ActionUpDate, the
Query will show 80 records; with ActionUpDate the Query shows 60 records.
The missing 20 are those that no entry in ActionUpDate.

SQL Follows:
SELECT [Audit Data].Index, [Audit Data].Job, Behaviors.Behavior,
Behaviors.Comment, Behaviors.Why, Behaviors.Corrective, [Audit Data].Date,
[Lookup - Department].Department, Behaviors.ActionCompleted, Behaviors.Type,
[Lookup - Area].Area, ActionUpDate.Date, ActionUpDate.Action,
ActionUpDate.Behavior



FROM ([Lookup - Department] INNER JOIN ([Lookup - Area] INNER JOIN ([Audit
Data] INNER JOIN Behaviors ON [Audit Data].Index = Behaviors.Index) ON
[Lookup - Area].Index = [Audit Data].Area) ON [Lookup - Department].Index =
[Audit Data].Department) LEFT JOIN ActionUpDate ON [Audit Data].Index =
ActionUpDate.index



WHERE ((([Audit Data].Date)>=[Forms]![ActionUpdateSelection]![StartDate] And
([Audit Data].Date)<=[Forms]![ActionUpdateSelection]![EndDate]) AND
(([Lookup - Department].Department)="Maintenance") AND
((Behaviors.ActionCompleted)=False) AND ((Behaviors.Type)=1 Or
(Behaviors.Type)=3) AND ((Behaviors.Status)=2) AND
((ActionUpDate.Behavior)=[Behaviors].[behavior]))

ORDER BY [Audit Data].Index;



What am I doing wrong???

Thanks for your help,

Jim Moore

 

[Baz]

I am having a problem getting all the records in my main table to show up on
a query which has a Left Join to a second table. SQL follows for a Query -
[Audit Data] is the main table
[Behaviors] is linked to [Audit Data] on the field [Index]
[ActionUpDate] is also linked to [Audit Data] on [Index] and I set Join
Properties to iclude all records from Audit Data and only those from
ActionUpDate where Index is equal.

When I run the Query, only records in Audit Data that have a match to
ActionUpDate show up. As an example, if I do not include ActionUpDate, the
Query will show 80 records; with ActionUpDate the Query shows 60 records.
The missing 20 are those that no entry in ActionUpDate.

SQL Follows:
SELECT [Audit Data].Index, [Audit Data].Job, Behaviors.Behavior,
Behaviors.Comment, Behaviors.Why, Behaviors.Corrective, [Audit Data].Date,
[Lookup - Department].Department, Behaviors.ActionCompleted, Behaviors.Type,
[Lookup - Area].Area, ActionUpDate.Date, ActionUpDate.Action,
ActionUpDate.Behavior



FROM ([Lookup - Department] INNER JOIN ([Lookup - Area] INNER JOIN ([Audit
Data] INNER JOIN Behaviors ON [Audit Data].Index = Behaviors.Index) ON
[Lookup - Area].Index = [Audit Data].Area) ON [Lookup - Department].Index =
[Audit Data].Department) LEFT JOIN ActionUpDate ON [Audit Data].Index =
ActionUpDate.index



WHERE ((([Audit Data].Date)>=[Forms]![ActionUpdateSelection]![StartDate] And
([Audit Data].Date)<=[Forms]![ActionUpdateSelection]![EndDate]) AND
(([Lookup - Department].Department)="Maintenance") AND
((Behaviors.ActionCompleted)=False) AND ((Behaviors.Type)=1 Or
(Behaviors.Type)=3) AND ((Behaviors.Status)=2) AND
((ActionUpDate.Behavior)=[Behaviors].[behavior]))

ORDER BY [Audit Data].Index;



What am I doing wrong???

Thanks for your help,

Jim Moore

It's yer WHERE clause mate. Specifically, this bit:

(ActionUpDate.Behavior)=[Behaviors].[behavior]

Where there is no ActionUpDate record, all of it's fields in the result set
will be null, including ActionUpDate.Behavior. A null value in
ActionUpDate.Behavior is not equal to [Behaviors].[behavior], because a null
value cannot be equal to anything. Hence, your WHERE clause is excluding
the results.

How you fix it depends on what you are trying to achieve. More info
required.

 

[Jim Moore]

Re your
A null value in

ActionUpDate.Behavior is not equal to [Behaviors].[behavior], because a
null
value cannot be equal to anything. Hence, your WHERE clause is excluding
the results.

I changed "[Behaviors].[behavior]" to "Is Null Or [Behaviors].[behavior]"
and that solved the problem!
Thanks!!!

message

I am having a problem getting all the records in my main table to show up on
a query which has a Left Join to a second table. SQL follows for a Query -
[Audit Data] is the main table
[Behaviors] is linked to [Audit Data] on the field [Index]
[ActionUpDate] is also linked to [Audit Data] on [Index] and I set Join
Properties to iclude all records from Audit Data and only those from
ActionUpDate where Index is equal.

When I run the Query, only records in Audit Data that have a match to
ActionUpDate show up. As an example, if I do not include ActionUpDate, the
Query will show 80 records; with ActionUpDate the Query shows 60 records.
The missing 20 are those that no entry in ActionUpDate.

SQL Follows:
SELECT [Audit Data].Index, [Audit Data].Job, Behaviors.Behavior,
Behaviors.Comment, Behaviors.Why, Behaviors.Corrective, [Audit
Data].Date,
[Lookup - Department].Department, Behaviors.ActionCompleted, Behaviors.Type,
[Lookup - Area].Area, ActionUpDate.Date, ActionUpDate.Action,
ActionUpDate.Behavior



FROM ([Lookup - Department] INNER JOIN ([Lookup - Area] INNER JOIN
([Audit
Data] INNER JOIN Behaviors ON [Audit Data].Index = Behaviors.Index) ON
[Lookup - Area].Index = [Audit Data].Area) ON [Lookup - Department].Index =
[Audit Data].Department) LEFT JOIN ActionUpDate ON [Audit Data].Index =
ActionUpDate.index



WHERE ((([Audit Data].Date)>=[Forms]![ActionUpdateSelection]![StartDate] And
([Audit Data].Date)<=[Forms]![ActionUpdateSelection]![EndDate]) AND
(([Lookup - Department].Department)="Maintenance") AND
((Behaviors.ActionCompleted)=False) AND ((Behaviors.Type)=1 Or
(Behaviors.Type)=3) AND ((Behaviors.Status)=2) AND
((ActionUpDate.Behavior)=[Behaviors].[behavior]))

ORDER BY [Audit Data].Index;



What am I doing wrong???

Thanks for your help,

Jim Moore

It's yer WHERE clause mate. Specifically, this bit:

(ActionUpDate.Behavior)=[Behaviors].[behavior]

Where there is no ActionUpDate record, all of it's fields in the result
set
will be null, including ActionUpDate.Behavior. A null value in
ActionUpDate.Behavior is not equal to [Behaviors].[behavior], because a
null
value cannot be equal to anything. Hence, your WHERE clause is excluding
the results.

How you fix it depends on what you are trying to achieve. More info
required.