Customers unsold this month Query

[Rpuzwebb]

Hi


I have 2 tables, Customers & Orders linked by IDOrder.

I can easily create a list of orders made this month, but to create a list of customers I need to contact who have NOT placed orders this month seems more difficult.

I have tried the below:
SELECT DISTINCT [Surname] & ", " & [Firstnames] & ". " & [add1] & ", " & [Add2] & ", " & [town] AS Name, Customers.Tel, Orders.Retail
FROM Customers INNER JOIN Orders ON Customers.ID = Orders.DistID
WHERE (((Customers.DistCust)=2) AND ((Orders.Retail)=0)) AND Month([Orders]![Date])<>Month(Date()) And Year([Orders]![Date])<>Year(Date());

Which returns nothing. There ARE customers in the database which meet my criteria.

If I change the above to:
SELECT DISTINCT [Surname] & ", " & [Firstnames] & ". " & [add1] & ", " & [Add2] & ", " & [town] AS Name, Customers.Tel, Orders.Retail
FROM Customers INNER JOIN Orders ON Customers.ID = Orders.DistID
WHERE (((Orders.Retail)=0) AND ((Customers.DistCust)=2) AND ((Month([Orders]![Date]))=Month(Date())) AND ((Year([Orders]![Date]))=Year(Date())));

I still get nothing. I either get nothing or a list of customers who have placed orders at other times which will include those who have ordered this month!

Any ideas? Thanks Richard

[ConneXionLost]

Hi Richard,

Try this example:

SELECT tblCustomer.*
FROM tblCustomer LEFT JOIN (SELECT tblOrder.CustomerID
FROM tblOrder
WHERE tblOrder.Retail=0 AND Month(tblOrder.OrderDate)=Month(Date()) AND Year(tblOrder.OrderDate)=Year(Date())) AS qryCC ON tblCustomer.CustomerID = qryCC.CustomerID
WHERE tblCustomer.DistCust=2 AND qryCC.CustomerID Is Null;

Change table/field names where necessary, but I recommend you avoid using "ID" as a name for a Primary Key field. In this example I switched to "CustomerID". I also recommend you avoid using "Date" as a name for any field. "Date" is a reserved VBA/SQL code word.

Cheers,

[Rpuzwebb]

Thank you for the above. Just trying to make it work now.

Looks a more elegant solution to the effort I eventually came up with using 2 queries as below:

SELECT DISTINCT [Customers]![Surname] & ", " & [Customers]![Firstnames] & ". " & [Customers]![Add1] & ", " & [Customers]![Add2] & ", " & [Customers]![Town] AS Name, [Customers]![Tel] AS Tel, Customers.ID
FROM Customers, qryCurrentSales
WHERE (((Customers.ID)<>All (SELECT [qryCurrentSales]![ID] FROM qryCurrentSales )) AND ((Customers.DistCust)=2))
ORDER BY [Customers]![Surname] & ", " & [Customers]![Firstnames] & ". " & [Customers]![Add1] & ", " & [Customers]![Add2] & ", " & [Customers]![Town];

QryCurrentSales is as below:

SELECT [Customers]![Surname] & " " & [Customers]![Firstnames] AS Name, Orders.Retail, Orders.Date, Orders.OrdID, Customers.ID
FROM Customers INNER JOIN Orders ON Customers.ID = Orders.DistID
WHERE (((Orders.Retail)>0) AND ((Month([Orders]![Date]))=Month(Date())) AND ((Year([Orders]![Date]))=Year(Date())) AND ((Customers.DistCust)=2));

Thanks very much for your efforts - Richard

[RuralGuy]

Rpuzwebb,
If you are satisfied with your results, how about using the Thread Tools and marking this thread as Solved?