CODEWITHSUNDEEP
sqlms-access
henkem
henkem

Reputation: 3

MS Access Count same items with multiple columns based on date

I have a table with the following fields, ID, Username, Application, and Last Access date. For example..

1, user1, app1, date 
2, user1, app2, date
3, user2, app1, date
4, user3, app3, date
5, user4, app1, date
6, user5, app2, date

I would like to create a table with application counts for all dates, last 30 days, last 90 days.. For example...

app1, 3, 1, 0
app2, 2, x, y
app3, 1, u, v

First column is the application, Second column is the count for all dates, next column within the last 30 day, etc..

Normally I really try to figure this stuff out on my own, but I am completely lost and missing something... Your help is appreciated...

Upvotes: 0

Views: 107

Answers (1)

SunKnight0
SunKnight0

Reputation: 3351

Try something like this:

SELECT Application,
SUM(IIF([Last Access date]<DateAdd('d',-30,Date()),0,1)) AS Last30Count,
SUM(IIF([Last Access date]<DateAdd('d',-90,Date()),0,1)) AS Last90Count
FROM [TableName]
GROUP BY Application

The above has no provision for NULL values (it assumes all records are complete) and it counts records not users (so if there are two records with the same user/application combo in the same time period it will be counted twice).

Upvotes: 1

Related Questions