Find number of records based on Date Difference (1 Viewer)

[123dstreet]

Hi All,

I have a table with RMA (Return Merchandise Authorizations). This table has many fields, RMA #, Serial Number, Return Date, etc...

My goal is to find how many serial numbers have been returned annually over the last 4 years. Most serial numbers are only returned every 2 years, so I would like to find the total number that are returned annually.

It seems simple, but I can't figure out how pull that total number out of a query.

Thank you in advance for any help!

Dan!

 

[kiloez]

Do a count of serial numbers grouping on the years

 

[123dstreet]

Could you give me a quick example as to how I would do that?

 

[GohDiamond]

Example from a table (tbl_RMA) with Fields (SerialNumber, RMANo , ReturnDate, etc)

SELECT tbl_RMA.SerialNumber, Count(tbl_RMA.SerialNumber) AS CountOfSerialNumber, Year([ReturnDate]) AS ReturnYear
FROM tbl_RMA
WHERE (((Year([ReturnDate])) Between Year(Date()) And Year(Date())-4))
GROUP BY tbl_RMA.SerialNumber, Year([ReturnDate])
ORDER BY Year([ReturnDate]);

Produces:
SN Ct Yr
0001 4 2013
0002 4 2013
0003 4 2013
0004 4 2013
0005 4 2013
0001 3 2014
0002 4 2014
0003 5 2014
0004 6 2014
0005 2 2014
0001 3 2015
0002 6 2015
0003 4 2015
0004 2 2015
0005 5 2015

Not the only way, but a way...
CHeers!
Goh