I have a database of sales and I want to be able to see what has sold during a particular time frame over the years i.e. see what sold most in the last 10 years between July 1 and July 15. Problem is that not all the items were sold every year, and I need to be able to get the average sold. I was able to count distinct years in MySQL but after migrating to Access I can't figure out how to count distinct while still grouping by the individual product.
Relevant fields are StockID (the items' unique id) and TransDate (a datetime that I pull the year from) I've tried things similar to
SELECT Count(*) FROM (SELECT DISTINCT YEAR(Transdate) from Sales)
Seems you are looking for select with group by ..
select StockID, count(*) from (select distinct stockID, Year( Transdate) from my_table ) group StockID