Filter based on ID and date

[blogmk]

I have 2 tables. One for customer details. With customer ID,Company Name, Customer Name and Address.
The other for sales details with Order ID, Customer ID, salesprice and sales date.

I would like to have a list of customer ID, Company name,total sales last year(sum of salesprice for 2013), Total sales till date(Jan 2014 til now). All should include customers details who have not made any sale as well.

Please help me get the right query.

Thanks

 

[bob fitz]

Until someone come up with something better you could do this, it seems to work:
Create a query to return sales for last year:
SELECT tblCustomers.[customer ID], tblCustomers.[Customer Name], nz([salesprice],0) AS Sales, tblOrders.[sales date] AS SalesThisYr
FROM tblOrders RIGHT JOIN tblCustomers ON tblOrders.[Customer ID] = tblCustomers.[customer ID]
WHERE (((tblOrders.[sales date])<#1/1/2014#));

Create a query to return sales for this year:
SELECT tblCustomers.[customer ID], tblCustomers.[Customer Name], nz([salesprice],0) AS Sales, tblOrders.[sales date] AS SalesThisYr
FROM tblOrders RIGHT JOIN tblCustomers ON tblOrders.[Customer ID] = tblCustomers.[customer ID]
WHERE (((tblOrders.[sales date])>=#1/1/2014#));

Create a "Totals" query that joins both the above queries a to the Customer table:
SELECT tblCustomers.[customer ID], tblCustomers.[Customer Name], Sum(nz([qrySalesLastYr].[sales])) AS LastYr, Sum(nz([qrySalesThisYr].[sales])) AS ThisYr
FROM (tblCustomers LEFT JOIN qrySalesLastYr ON tblCustomers.[customer ID] = qrySalesLastYr.[customer ID]) LEFT JOIN qrySalesThisYr ON tblCustomers.[customer ID] = qrySalesThisYr.[customer ID]
GROUP BY tblCustomers.[customer ID], tblCustomers.[Customer Name];

Although you tell us your field names in your OP, you did not tell us your table names. I have used "tblCustomers" and "tblOrders".
You will need to change the SQL statements if your tables have different names.

 

[blogmk]

Thanks . I tried and it works fine. The output is what I need. But I tried calling the query from VB 2010.But it doesnt seem to work. It says that the query name cannot be found. Can you please help me with this. Thanks

 

[bob fitz]

But I tried calling the query from VB 2010.

What is VB 2010.