Unique monthly count

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

EdS - 31 May 2006 14:17 GMT

Hello.. I have a table that keeps the jobs worked on daily by employees,
meaning the employee id may appear more than once each day. I have created
the following code in a query to give me the unique count of employees who
worked during the month:
SELECT Count([Employee]) AS [# of Employees] FROM (SELECT DISTINCT
[Employee] FROM [Job_Time] WHERE Year([WorkDate]) = Year(Date()) AND
Month([WorkDate]) = Month(Date()))

This works fine for the current month, however, I need to produce a month by
month count for the past three years. Any suggestions?

Signature

Thanks. EdS

Reply to this Message

KARL DEWEY - 31 May 2006 16:30 GMT

UNTESTED --

SELECT Format([WorkDate], "mmm yyyy") AS [Work Month], Count([Employee]) AS
[# of Employees]
FROM [Job_Time]
WHERE Year([WorkDate]) >= Year(Date()-3
ORDER BY Format([WorkDate], "yyyymm");

> Hello.. I have a table that keeps the jobs worked on daily by employees,
> meaning the employee id may appear more than once each day. I have created
[quoted text clipped - 6 lines]
> This works fine for the current month, however, I need to produce a month by
> month count for the past three years. Any suggestions?

Reply to this Message

John Spencer - 31 May 2006 17:04 GMT

Perhaps something like the following UNTESTED SQL statement.

SELECT WorkMonth, Count(Employee) as EmployeeCount
FROM
(SELECT Format(WorkDate, "yyyy-mm") AS WorkMonth, Employee
FROM [Job_Time]
WHERE WorkDate Between DateSerial(Year(Date())-3,Month(Date()),1)
And DateSerial(Year(date()), Month(Date()),0)
GROUP BY Format(WorkDate, "yyyy-mm"), Employee) as UniqueTable
GROUP BY WorkMonth

> UNTESTED --
>
[quoted text clipped - 18 lines]
>> by
>> month count for the past three years. Any suggestions?

Reply to this Message

EdS - 31 May 2006 17:19 GMT

Appreciate your reply.. Your suggestion and Karl Dewey's both work!

Signature

Thanks. EdS

> Perhaps something like the following UNTESTED SQL statement.
>
[quoted text clipped - 29 lines]
> >> by
> >> month count for the past three years. Any suggestions?

Reply to this Message

John Spencer - 31 May 2006 20:19 GMT

Unless I am wrong, the two should give you different results if an employee
works multiple times in the month.

I was attempting to give you a count of the number of employees that worked
in the month - whether they had one workdate record or multiple workdate
records in the month. I believe Karl Dewey's would count the number of
workdate records in the month.

> Appreciate your reply.. Your suggestion and Karl Dewey's both work!
>
[quoted text clipped - 34 lines]
>> >> by
>> >> month count for the past three years. Any suggestions?

Reply to this Message

EdS - 31 May 2006 20:43 GMT

Actually, I tried both suggestions and both gave me a unique count of
employees who worked during the month. I have run into another wrinkle,
however. The query works fine in Access, but for some reason when I try to
import it into Excel, I get an error message. I guess I'll just have to work
on that.

Signature

Thanks. EdS

> Unless I am wrong, the two should give you different results if an employee
> works multiple times in the month.
[quoted text clipped - 42 lines]
> >> >> by
> >> >> month count for the past three years. Any suggestions?

Reply to this Message

EdS - 31 May 2006 17:17 GMT

Works great! Much appreciated!

Signature

Thanks. EdS

> UNTESTED --
>
[quoted text clipped - 14 lines]
> > This works fine for the current month, however, I need to produce a month by
> > month count for the past three years. Any suggestions?

Reply to this Message