How to create product query based on inactive dates

[hansamuq]

Hi all MS Access Gurus,



I wanted to create a query to query which products have never been purchased in the year of 2014-2015.

I have a record of Sales order as below.
The sales order will has the below fields

ID	Customer	Date	ProductCode	Quantity
1	CustomerA	2/3/2012	1001	10
2	CustomerA	2/3/2015	2001	10
3	CustomerB	2/4/2011	2001	10
4	CustomerC	5/3/2009	3001	10

And i have a productcode table as below
The productcode table has the below fields

ProductCode	Cost$	Selling$
1001	$1	$3
2001	$2	$5
3001	$6	$10
4001	$15	$50
5001	$40	$55

The problem i have is that when i do a query based on sales order and criteria by date based on "Not Between 1/1/2014 And 31/12/2015", it will show me the follow;

ProductCode

1001

3001

But the problem is that under productcode table, 4001 and 5001 has no purchases at all and these 2 product code should also be in this query. how can i make that happen?

[CJ_London]

include your productcode table with a left join to the sales table and modify your criteria to

date Not Between 1/1/2014 And 31/12/2015 or date is null

I presume date is not the real name of your field

[hansamuq]

Got it working
First find which products have been purchased in the year of 2014-2015.
Then use that in an unmatched query against the productcode table.