Grouping & Counting on a Field

S

SkyGuy

Hello,

I've created a query that list all Access Reports that were written for
various departments. The query works fine but is displaying duplicate
department names (one display for each Access Report written for that
department).

I'd like to display the results (w/o duplicate department names) in an
Access Report as follows:

DEPARTMENT NUMBER OF REPORTS WRITTEN
HR 10
Payroll 21
Accounting 35

Please advise and thank you in advance,
Bob
 
K

Klatuu

Set the Hide Duplicates property for the text box that displays the
department to True. It will then show only the first occurance for each.
 
S

SkyGuy

Wow...that was easy and worked perfectly. Thank you!
But how do I display the number of reports written (as shown in the example
below)? I tried using the Access Question Box to find the answer but had no
luck.
 
K

Klatuu

If your query is returning the number of records, put a text box on the
report and bind it to the field in the query that returns that value.
 
S

SkyGuy

I'm sorry but my query is not returning a count of reports per department and
I'm don't know how to do that. Can I create the Count in the report instead
of the query?
 
K

Klatuu

What is your query returning?
How do you know which reports are returned for a department?
 
J

Joan Wild

Base your report on a Query. In design view of the query, choose View,
Totals.

GroupBy Department, Count Reports.
 
S

SkyGuy

Here's a copy of the query SQL:

SELECT Reports.[Report Name], Reports.[Date requested], Reports.Status,
Reports.Dept
FROM Reports
WHERE (((Reports.[Date requested]) Between #7/1/2005# And #6/30/2006#) AND
((Reports.Status)="implemented")) OR (((Reports.Status)="completed"))
ORDER BY Reports.Dept;

Hope this answers you question.
 
K

Klatuu

Put a text box on your form to count them. I think the control source should
be:
=Count([ReportName])

SkyGuy said:
Here's a copy of the query SQL:

SELECT Reports.[Report Name], Reports.[Date requested], Reports.Status,
Reports.Dept
FROM Reports
WHERE (((Reports.[Date requested]) Between #7/1/2005# And #6/30/2006#) AND
((Reports.Status)="implemented")) OR (((Reports.Status)="completed"))
ORDER BY Reports.Dept;

Hope this answers you question.

Klatuu said:
What is your query returning?
How do you know which reports are returned for a department?
Click to expand...
Click to expand...
 
Top