One table search

[Storelul]

Hi,
I'm a beginner with SQL in MS Access and have a problem maybe someone can help me out with.

I have one table containing four fields (no indexes):

ProductID
UserID
Status
UpdateDate

Everytime a User updates a Product a new record is added to this table; What Product did get updated, by whom and when and a indication (Status) if the update went well or not (a value between 1 and 3).

I want to create query that count the number of updates per Product, per Month. It's bonus if you also can se how many updates of each Status per Product and Month.

Simple? Not to me.

Thanks.

 

[PHV]

A starting point:
SELECT ProductID, Format(UpdateDate, 'yyyy-mm') AS [Month], Count(*) AS NumOfUpdates
FROM yourTable
GROUP BY ProductID, Format(UpdateDate, 'yyyy-mm')

Bonus track:
SELECT Status, ProductID, Format(UpdateDate, 'yyyy-mm') AS [Month], Count(*) AS NumOfUpdates
FROM yourTable
GROUP BY Status, ProductID, Format(UpdateDate, 'yyyy-mm')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Storelul]

Great! Thanks PH.
Imagine you're not interested in the different Status but the number of unique users that have updated a Product, monthly. I guess Count(*) doesn't work then?

Thanks again.

 

[PHV]

JetSQL lacks the COUNT(DISTINCT column) aggregate function.
A workaround is to use an inline view:
SELECT D.ProductID, D.Month, Count(*) AS NumOfUsers
FROM (SELECT DISTINCT A.ProductID, Format(A.UpdateDate, 'yyyy-mm') AS [Month], A.UserID
FROM yourTable AS A) AS D
GROUP BY D.ProductID, D.Month

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886