Unique monthly count

G

Guest

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?
 
G

Guest

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");
 
J

John Spencer

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

KARL DEWEY said:
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");

EdS said:
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?
Click to expand...
Click to expand...
 
G

Guest

Works great! Much appreciated!
--
Thanks. EdS


KARL DEWEY said:
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");

EdS said:
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?
Click to expand...
Click to expand...
 
G

Guest

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

--
Thanks. EdS


John Spencer said:
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

KARL DEWEY said:
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");

EdS said:
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?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

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.


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

--
Thanks. EdS


John Spencer said:
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

KARL DEWEY said:
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
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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.
--
Thanks. EdS


John Spencer said:
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.


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

--
Thanks. EdS


John Spencer said:
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 --

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
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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to count records w/o counting duplicates by week, month, or ye 4
Error 3205 - Too Many Crosstab Column Headers (300) 2
update query 4
Help with query counting unique records??? 12
count qry 2
% of callers repeat/unique 1
Please Help Build an expression 1
Sum work hours to hours & nearest 10th of an hour 14

Top