Left Join Query - Help Needed

[daherb]

I am trying to build a query that shows me the activity of my customers. I have a database that amongst other things holds two tables which i am trying to query.

tbl 1 - Customers
tbl 2 - Enquiries

What i eventually want to do is create a report that shows which customers have made enquiries and which ones haven't on a weekly and monthly basis.

the fields brought out of tbl1 are just the companies name, and sales person responsible for the account. Now to get all the customers to show i have learnt that you can do a left join on the query which will show all the records in the left table (tbl1) which is great and i can then limit the customer accounts that show by adding query criteria to the sales rep field from tbl1. The problem that i am having is when i try to limit the time period i.e weekly, monthly. when i enter criteria in the EnquiryDate field from tbl2 the query stops showing all the customers and only shows those that match the date criteria. Can anyone tell me how to prevent this from happening.

Thanks

 

[FoFa]

If the critiera you use in the WHERE clause is on the left joined table, it sort of changes the query to an inner join because you are telling it that MUST meet this criteria. You need to include that criteria in the left join portion instead of the WHERE clause (best solution) OR include an "OR DateCol is NULL" along with the date criteria (not as good, but works).

Best:
LEFT JOIN tbl2 on tbl1.PKEY = tbl2.FKEY AND tbl2.DateCol between [startdate] AND [enddate]

Works:
WHERE (tbl2.DateCol between [startdate] AND [enddate] OR tbl2.datecol is null)
Parens are important to this functioning properly however

As an example

 

[daherb]

Thats great Thanks alot

 

[daherb]

still have a problem, when i do that i get an error "Between operator without And in query expression"." here is my sql

SELECT DISTINCTROW tblCustomers.Account, tblCustomers.CompanyName, Count(*) AS [Count Of tblEnquiries], Count(tblEnquiries.EnquiryDate) AS [Count of EnquiryDate]
FROM tblCustomers LEFT JOIN tblEnquiries ON tblCustomers.CustomerID = tblEnquiries.CustomerID AND tblEnquiries.EnquiryDate between [Please enter Start Date] AND [Please enter End Date]
GROUP BY tblCustomers.Account, tblCustomers.CompanyName
HAVING (((tblCustomers.Account) Like "APL"));

 

[FoFa]

Try this:
SELECT tblCustomers.Account, tblCustomers.CompanyName, Count(*) AS [Count Of tblEnquiries], Count(tblEnquiries.EnquiryDate) AS [Count of EnquiryDate]
FROM tblCustomers LEFT JOIN tblEnquiries ON (tblCustomers.CustomerID = tblEnquiries.CustomerID AND tblEnquiries.EnquiryDate between [Please enter Start Date] AND [Please enter End Date])
GROUP BY tblCustomers.Account, tblCustomers.CompanyName
HAVING (((tblCustomers.Account) Like "APL"));

Can't remember, do you have to enclose the dates in #'s for Access?
LEFT JOIN tblEnquiries ON (tblCustomers.CustomerID = tblEnquiries.CustomerID AND tblEnquiries.EnquiryDate between #[Please enter Start Date]# AND #[Please enter End Date]#)

 

[daherb]

yep that worked, and no you dont need the #'s

cheers mate