Grouping & Counting on a Field

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

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.

"SkyGuy" wrote:

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

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.

"Klatuu" wrote:

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.

"SkyGuy" wrote:

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

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.

"SkyGuy" wrote:

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.

"Klatuu" wrote:

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.

"SkyGuy" wrote:

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

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?

"Klatuu" wrote:

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.

"SkyGuy" wrote:

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.

"Klatuu" wrote:

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.

"SkyGuy" wrote:

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

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

"SkyGuy" wrote:

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?

"Klatuu" wrote:

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.

"SkyGuy" wrote:

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.

"Klatuu" wrote:

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.

"SkyGuy" wrote:

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

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

GroupBy Department, Count Reports.


--
Joan Wild
Microsoft Access MVP

SkyGuy wrote:
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

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" wrote:

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

"SkyGuy" wrote:

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?

"Klatuu" wrote:

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.

"SkyGuy" wrote:

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.

"Klatuu" wrote:

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.

"SkyGuy" wrote:

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

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

"SkyGuy" wrote:

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" wrote:

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

"SkyGuy" wrote:

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?

"Klatuu" wrote:

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.

"SkyGuy" wrote:

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.

"Klatuu" wrote:

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.

"SkyGuy" wrote:

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