Grouping & Counting on a Field

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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

 

[Joan Wild]

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

GroupBy Department, Count Reports.

 

[Guest]

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.

 

[Guest]

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

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.

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