How to create product query based on inactive dates (1 Viewer)

[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?

 

[JHB]

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

 

[hansamuq]

Thanks! It's working now!

 

[JHB]

Good you got it - good luck.