Solved Query to find count of new instruments

[Ravi Kumar]

Dear all,

In my table instruments list
I have two columns 1. Instrument received on(date type) 2. Equipment name
The first column has the data from almost 6 years.
But now I want a query showing the count of equipment's we received only from this year(that too in month wise).
How to accomplish this?

 

[arnelgp]

Use total query: (not on pc so cant test)

Select Format([date field], "mmmm") as Month, count("1") as CountOfEquipThisYear from yourTable
where [date field] between dateserial(year(date),1,1) and dateserial(year(date),12,31)
Group by Format([date field], "mmmm")
Order by Format([date field], "mm")

 

[cheekybuddha]

SELECT
  Format([DateReceived], "YYYY-MM") AS [Month],
  [EquipmentName],
  COUNT(*) AS QtyReceived
FROM [YourTableName]
WHERE [DateReceived] >= #2020-01-01#
GROUP BY
  Format([DateReceived], "YYYY-MM"),
  [EquipmentName]
;

 

[Ravi Kumar]

Thank you for your quick help,it worked