Help with query counting unique records???

[Guest]

I have an Access db that keeps track of employees and dates that tasks are
assigned to them, along with their completion date.

I have a query that counts the number of employees that completed tasks
assigned for the month and displays the total amount of employees for that
month. The query does this fine, but it's not exactly what I'm looking for.
I want it to count the number of different employees for that month that
completed tasks and not the number of employees for all the tasks.

I'm not sure if I'm explaing this correctly so here's an example:

SELECT DISTINCT Count(Fairness.AssOff) AS [# of Assigned Officers]
FROM Fairness
HAVING (((Year([AssDate]))=Year(Now())) AND
((Month([AssDate]))=Month(Now())));

Please help I have read Access help through and through and could not find
any answers, I have scoured forums and read tons of messages bu haven't been
able to fix this problem.

Thanks

John - completed this task
Henry - "
John - "
Alice - "
Alice - "
John - "

I want the query to count how many different employees completed tasks. In
this case 3 employees not 6 employees that the query is counting.

Here is a copy of the SQL statement:

 

[Andreas]

Create a new query, bringing in tables as necessary.
Bring in the Employee Name twice.
Change the query to a summary totals query (Sigma button on toolbar).
In the new Totals row, the first column should say Group By, change the
second column to Count (this second column is optional but might provide
useful detail data).
Then add any criteria as necessary, changing the Totals row to Where
(presumably you filter by completion date rather than assignment date?
if you do filter by assignment date, you probably want to also filter by
completion date: Not Is Null).

You can then use this query as a record source for a second summary
totals query just bringing in the employee name and doing a count.

Regards,
Andreas

 

[Van T. Dinh]

I am not sure from your description but it sounds like:

********
SELECT Count([AssOff]) AS [# of Assigned Officers]
FROM
(
SELECT DISTINCT [AssOff]
FROM [Fairness]
WHERE Year([AssDate]) = Year(Date())
AND Month([AssDate]) = Month(Date())
)
********

However, it is more efficient to use:

********
SELECT Count([AssOff]) AS [# of Assigned Officers]
FROM
(
SELECT DISTINCT [AssOff]
FROM [Fairness]
WHERE [AssDate] BETWEEN DateSerial(Year(Date()), Month(Date()), 1)
AND DateSerial(Year(Date()), Month(Date()) + 1, 0)
)
********

especially, if the Field [AssDate] is indexed.

 

[Guest]

Thanks for the help!

The problem I have now is that when I use either of the date fields and add
the criteria of current month and/or year I get no results.

I think it's something I'm doing.
The reason I need the count of the employees is that some months there are
more employees then others. The number of employees is used to guage results
in the program we are working.
The results from the amount of employees are used to calculate averages and
percentages along with some other numbers need for the program administrator.

Create a new query, bringing in tables as necessary.
Bring in the Employee Name twice.
Change the query to a summary totals query (Sigma button on toolbar).
In the new Totals row, the first column should say Group By, change the
second column to Count (this second column is optional but might provide
useful detail data).
Then add any criteria as necessary, changing the Totals row to Where
(presumably you filter by completion date rather than assignment date?
if you do filter by assignment date, you probably want to also filter by
completion date: Not Is Null).

You can then use this query as a record source for a second summary
totals query just bringing in the employee name and doing a count.

Regards,
Andreas

I have an Access db that keeps track of employees and dates that tasks are
assigned to them, along with their completion date.

I have a query that counts the number of employees that completed tasks
assigned for the month and displays the total amount of employees for that
month. The query does this fine, but it's not exactly what I'm looking for.
I want it to count the number of different employees for that month that
completed tasks and not the number of employees for all the tasks.

I'm not sure if I'm explaing this correctly so here's an example:

SELECT DISTINCT Count(Fairness.AssOff) AS [# of Assigned Officers]
FROM Fairness
HAVING (((Year([AssDate]))=Year(Now())) AND
((Month([AssDate]))=Month(Now())));

Please help I have read Access help through and through and could not find
any answers, I have scoured forums and read tons of messages bu haven't been
able to fix this problem.

Thanks

John - completed this task
Henry - "
John - "
Alice - "
Alice - "
John - "

I want the query to count how many different employees completed tasks. In
this case 3 employees not 6 employees that the query is counting.

Here is a copy of the SQL statement:

 

[Guest]

Thanks for your help!

I pasted the SQL statement into the query but it still counts 1 record for
every employee for every job. Basically I need to count how many different
employees where doing work each month or the month current month.

I am not sure from your description but it sounds like:

********
SELECT Count([AssOff]) AS [# of Assigned Officers]
FROM
(
SELECT DISTINCT [AssOff]
FROM [Fairness]
WHERE Year([AssDate]) = Year(Date())
AND Month([AssDate]) = Month(Date())
)
********

However, it is more efficient to use:

********
SELECT Count([AssOff]) AS [# of Assigned Officers]
FROM
(
SELECT DISTINCT [AssOff]
FROM [Fairness]
WHERE [AssDate] BETWEEN DateSerial(Year(Date()), Month(Date()), 1)
AND DateSerial(Year(Date()), Month(Date()) + 1, 0)
)
********

especially, if the Field [AssDate] is indexed.

--
HTH
Van T. Dinh
MVP (Access)

I have an Access db that keeps track of employees and dates that tasks are
assigned to them, along with their completion date.

I have a query that counts the number of employees that completed tasks
assigned for the month and displays the total amount of employees for that
month. The query does this fine, but it's not exactly what I'm looking for.
I want it to count the number of different employees for that month that
completed tasks and not the number of employees for all the tasks.

I'm not sure if I'm explaing this correctly so here's an example:

SELECT DISTINCT Count(Fairness.AssOff) AS [# of Assigned Officers]
FROM Fairness
HAVING (((Year([AssDate]))=Year(Now())) AND
((Month([AssDate]))=Month(Now())));

Please help I have read Access help through and through and could not find
any answers, I have scoured forums and read tons of messages bu haven't been
able to fix this problem.

Thanks

John - completed this task
Henry - "
John - "
Alice - "
Alice - "
John - "

I want the query to count how many different employees completed tasks. In
this case 3 employees not 6 employees that the query is counting.

Here is a copy of the SQL statement:

 

[Andreas]

Please post the SQL of the first query.

Regards,
Andreas

Thanks for the help!

The problem I have now is that when I use either of the date fields and add
the criteria of current month and/or year I get no results.

I think it's something I'm doing.
The reason I need the count of the employees is that some months there are
more employees then others. The number of employees is used to guage results
in the program we are working.
The results from the amount of employees are used to calculate averages and
percentages along with some other numbers need for the program administrator.



:

Create a new query, bringing in tables as necessary.
Bring in the Employee Name twice.
Change the query to a summary totals query (Sigma button on toolbar).
In the new Totals row, the first column should say Group By, change the
second column to Count (this second column is optional but might provide
useful detail data).
Then add any criteria as necessary, changing the Totals row to Where
(presumably you filter by completion date rather than assignment date?
if you do filter by assignment date, you probably want to also filter by
completion date: Not Is Null).

You can then use this query as a record source for a second summary
totals query just bringing in the employee name and doing a count.

Regards,
Andreas

I have an Access db that keeps track of employees and dates that tasks are
assigned to them, along with their completion date.

I have a query that counts the number of employees that completed tasks
assigned for the month and displays the total amount of employees for that
month. The query does this fine, but it's not exactly what I'm looking for.
I want it to count the number of different employees for that month that
completed tasks and not the number of employees for all the tasks.

I'm not sure if I'm explaing this correctly so here's an example:

SELECT DISTINCT Count(Fairness.AssOff) AS [# of Assigned Officers]
FROM Fairness
HAVING (((Year([AssDate]))=Year(Now())) AND
((Month([AssDate]))=Month(Now())));

Please help I have read Access help through and through and could not find
any answers, I have scoured forums and read tons of messages bu haven't been
able to fix this problem.

Thanks

John - completed this task
Henry - "
John - "
Alice - "
Alice - "
John - "

I want the query to count how many different employees completed tasks. In
this case 3 employees not 6 employees that the query is counting.

Here is a copy of the SQL statement:

 

[Van T. Dinh]

I am not sure what you did but I would expect the Query I posted to return 1
row of 1 column which is different from what you posted.

Post the structure of your Table(s), some sample data and your attempted SQl
String.

 

[Guest]

Thanks again.

Your posted SQL statement did work the way I wanted.
Thank you for taking your time to help me.

 

[Guest]

Thanks again for your help.

It is now working. Thanks for taking your time to help.

Please post the SQL of the first query.

Regards,
Andreas

Thanks for the help!

The problem I have now is that when I use either of the date fields and add
the criteria of current month and/or year I get no results.

I think it's something I'm doing.
The reason I need the count of the employees is that some months there are
more employees then others. The number of employees is used to guage results
in the program we are working.
The results from the amount of employees are used to calculate averages and
percentages along with some other numbers need for the program administrator.



:

Create a new query, bringing in tables as necessary.
Bring in the Employee Name twice.
Change the query to a summary totals query (Sigma button on toolbar).
In the new Totals row, the first column should say Group By, change the
second column to Count (this second column is optional but might provide
useful detail data).
Then add any criteria as necessary, changing the Totals row to Where
(presumably you filter by completion date rather than assignment date?
if you do filter by assignment date, you probably want to also filter by
completion date: Not Is Null).

You can then use this query as a record source for a second summary
totals query just bringing in the employee name and doing a count.

Regards,
Andreas


Filed wrote:

I have an Access db that keeps track of employees and dates that tasks are
assigned to them, along with their completion date.

I have a query that counts the number of employees that completed tasks
assigned for the month and displays the total amount of employees for that
month. The query does this fine, but it's not exactly what I'm looking for.
I want it to count the number of different employees for that month that
completed tasks and not the number of employees for all the tasks.

I'm not sure if I'm explaing this correctly so here's an example:

SELECT DISTINCT Count(Fairness.AssOff) AS [# of Assigned Officers]
FROM Fairness
HAVING (((Year([AssDate]))=Year(Now())) AND
((Month([AssDate]))=Month(Now())));

Please help I have read Access help through and through and could not find
any answers, I have scoured forums and read tons of messages bu haven't been
able to fix this problem.

Thanks

John - completed this task
Henry - "
John - "
Alice - "
Alice - "
John - "

I want the query to count how many different employees completed tasks. In
this case 3 employees not 6 employees that the query is counting.

Here is a copy of the SQL statement:

 

[Guest]

If you don't mind I have a question regarding your SQL statement.
How do I create that statement using Access design view of a query.
I've tried and cannot get it right, the number officers is always incorrect.
I wanted to use the same information that your statement gives but this time
choose the month based on input.

 

[Van T. Dinh]

The Query Grid can only handle less complex Queries. The more complex
queries as well as specific queries such as Union or Pass-Through queries
need to be done using the SQL View.

 

[Guest]

One last question then. Can you point me to a really good guide for writing
SQL statements that will be good for beginners as well as more complex cases?

 

[Van T. Dinh]

Try "SQL For Mere Mortals" by Hernandez + Viescas