left join not working

[PeterW]

I am sure I must be missing something simple here but am lost if anyone can
help.
I have an database that tracks enquiries and have a table 'source' for where
enquiries can come from and a table 'enquiries' that logs actual enquiries. I
want to see which sources aren't generating enquiries and am using the
following

SELECT tblSource.Source, [Brochure Requests].Source
FROM tblSource LEFT JOIN [Brochure Requests] ON tblSource.Source =
[Brochure Requests].Source
WHERE ((([Brochure Requests].DateSent) BETWEEN Date() and Date()-28))
Order By tblSource.Source;

For some reason this only returns records with a match and I want to list
all the sources and see null values for no matches - I have inherited this
database so don't know if there can be any other settings that could be
stopping this working.
Ideally I would like to count the No of enquiries by source and can get this
to work with just the enquiries table but not when I join the two - is this
possible to do when joined? ie can null values show 0?

 

[John Spencer]

By applying criteria against the table on the right side of the left join you
negate the join. OFTEN you can fix this using an additional criterion of is Null

SELECT tblSource.Source
, [Brochure Requests].Source
FROM tblSource LEFT JOIN [Brochure Requests]
ON tblSource.Source = [Brochure Requests].Source
WHERE ([Brochure Requests].DateSent BETWEEN Date() and Date()-28
OR [Brochure Requests].Source is Null)
Order By tblSource.Source;

More frequently you have to use a subquery in the from clause. It is
unfortunate that your table name has a space in it since you can't do this
directly with a query, but would need to nest two queries.

First query: qRequestsInDate
SELECT Source
FROM [Brochure Requests]
[Brochure Requests].DateSent BETWEEN Date() and Date()-28

Now you can use that query and tblSource in a second query
SELECT tblSource.Source
, qRequestsInDate.Source
FROM tblSource LEFT JOIN qRequestsInDate
ON tblSource.Source = qRequestsInDate.Source

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[PeterW]

Thank You - the first part wouldn't fix it unfortunately but the second works
fine

By applying criteria against the table on the right side of the left join you
negate the join. OFTEN you can fix this using an additional criterion of is Null

SELECT tblSource.Source
, [Brochure Requests].Source
FROM tblSource LEFT JOIN [Brochure Requests]
ON tblSource.Source = [Brochure Requests].Source
WHERE ([Brochure Requests].DateSent BETWEEN Date() and Date()-28
OR [Brochure Requests].Source is Null)
Order By tblSource.Source;

More frequently you have to use a subquery in the from clause. It is
unfortunate that your table name has a space in it since you can't do this
directly with a query, but would need to nest two queries.

First query: qRequestsInDate
SELECT Source
FROM [Brochure Requests]
[Brochure Requests].DateSent BETWEEN Date() and Date()-28

Now you can use that query and tblSource in a second query
SELECT tblSource.Source
, qRequestsInDate.Source
FROM tblSource LEFT JOIN qRequestsInDate
ON tblSource.Source = qRequestsInDate.Source

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I am sure I must be missing something simple here but am lost if anyone can
help.
I have an database that tracks enquiries and have a table 'source' for where
enquiries can come from and a table 'enquiries' that logs actual enquiries. I
want to see which sources aren't generating enquiries and am using the
following

SELECT tblSource.Source, [Brochure Requests].Source
FROM tblSource LEFT JOIN [Brochure Requests] ON tblSource.Source =
[Brochure Requests].Source
WHERE ((([Brochure Requests].DateSent) BETWEEN Date() and Date()-28))
Order By tblSource.Source;

For some reason this only returns records with a match and I want to list
all the sources and see null values for no matches - I have inherited this
database so don't know if there can be any other settings that could be
stopping this working.
Ideally I would like to count the No of enquiries by source and can get this
to work with just the enquiries table but not when I join the two - is this
possible to do when joined? ie can null values show 0?

.