Re: Unique monthly count

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" <EdS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:33569025-D4A3-406E-ACD2-0945DA2666D7@xxxxxxxxxxxxxxxx
Appreciate your reply.. Your suggestion and Karl Dewey's both work!

--
Thanks. EdS


"John Spencer" wrote:

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" <KARLDEWEY@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5AB4281D-963A-4A47-B373-26F867E63ABA@xxxxxxxxxxxxxxxx
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" wrote:

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?

--
Thanks. EdS





.

Relevant Pages

  • Re: Design problem and suggestions...
    ... a person could show up multiple times in different ... single 'employee address' attribute" -- not true in the situation I ... one customer has been relying on the system for over 5 years ... uses that HMO for their medical insurance coverage?! ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Many-2-many relationships: Can I be told ...
    ... multiple times. ... Unit table: one record for each subject that an employee can do: ... CourseID primary key ... Enrol table: One record for every student in a course. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Unique monthly count
    ... SELECT WorkMonth, Countas EmployeeCount ... (SELECT Format(WorkDate, "yyyy-mm") ... WHERE WorkDate Between DateSerial)-3,Month),1) ... GROUP BY Format, Employee) as UniqueTable ...
    (microsoft.public.access.queries)
  • Re: Master / Child Multi Links
    ... The employeeID is also in table2 however it can be multiple times due to the ... Do you not have an Employee table ... combo box on a Query; this query can use the "unique values" property to show ...
    (microsoft.public.access.forms)
  • Re: Unique monthly count
    ... 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 ... SELECT WorkMonth, Countas EmployeeCount ...
    (microsoft.public.access.queries)