Re: left join not working

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 wrote:
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?
.

Relevant Pages

  • Re: left join not working
    ... FROM tblSource LEFT JOIN [Brochure Requests] ... directly with a query, but would need to nest two queries. ... enquiries can come from and a table 'enquiries' that logs actual enquiries. ...
    (microsoft.public.access.queries)
  • left join not working
    ... 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. ... SELECT tblSource.Source, [Brochure Requests].Source ... database so don't know if there can be any other settings that could be ...
    (microsoft.public.access.queries)
  • Re: Duplicates in queries from multiples tables
    ... Please copy and post the SQL of your query. ... It is much easier to visualize and fix the SQL if we have your query. ... FROM (CLIENTS as C INNER JOIN LISTS as L ... > Smith needs to get brochure 1 and brochure 2 but Jane Smith only gets ...
    (microsoft.public.access.queries)
  • Re: How does Google make money?
    ... I have a query for you: What do you do to find out about a company ... which hasnt gone public yet? ... The receptionist might be able to give you a copy of a brochure or the annual report. ...
    (comp.unix.solaris)
  • indicate query values in form + relation
    ... value of the yes/no fields but also the and /or relation for the query since ... Report ... Brochure ... Is this possible with one single query and one single form? ...
    (microsoft.public.access.forms)