If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
left join not working
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
left join not working
Thank You - the first part wouldn't fix it unfortunately but the second works
fine "John Spencer" wrote: 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? . |
Thread Tools | |
Display Modes | |
|
|