Solved

Hide duplicates and only count those records

Posted on 2001-06-14
5
390 Views
Last Modified: 2010-05-18
I'm helping a user set up a report where he wants to hide the duplicates.  That's no problem.  The problem is he wants to count the records under each category, but he only wants to count those records that are unique.  So in other words, if the Office Name "Taylor Service Center" appeared 3 times under a category, he only wants to display it once and count it once.  How do I count only unique records???  We're using Access 97.  If I have to use code, please be very specific, as I'm not a programmer.  Thanks.
0
Comment
Question by:esu4236
5 Comments
 
LVL 1

Expert Comment

by:chuck_forbes
ID: 6192837
Maybe, group the report on the field Office name, and put a RunningSum (see help menu on this) in the footer of that group (you can set it's visible property to NO and set the group footer's Can Shrink property to Yes - so that it doesn't appear in the final report). Then in the Report Footer, put a text box that refers to the text box that contained that running sum with "=Sum(txtRunningSum)". Just guessing though, never tried it.

-Chuck
0
 

Author Comment

by:esu4236
ID: 6192913
The report is already grouped on the Office Name.  I have a group footer set up and that's where I'm doing the count function.  I cannot do a RunningSum or a Sum function on this field, as it's not a numerical field.  It's text.  So I think I have to do some sort of counting, but I need to only count unique records.
0
 
LVL 6

Accepted Solution

by:
PsychoDazey earned 50 total points
ID: 6192928
I think the easiest way would be to build a query and set the record source for the report to the query.
Go into query design view and select the fields you want in the report.  Then click on the "Sum" Button on the menu bar.  That will add a row called "Totals".  For the records you want counted, click count in the sum column.  The help file is pretty extensive onn this subject.
0
 
LVL 57
ID: 6194964
You can also do the count right in the report.  In the report declarations section, dim a variable to hold the total:

 Dim intItemCount as integer

then in the Group Headers OnPrint event do:

  intItemCount = 0

then in the detail sections OnFormat event do:

  If FormatCount = 1 then
     intItemCount = intItemCount + 1
  End If

and last, in the Group Footers OnFormat event, place the count in a unbound text control:

  Me![txtGroupCount] = intItemCount

 'txtGroupCount' is the name of the control that you create.

Jim.
0
 

Author Comment

by:esu4236
ID: 6196580
I ended up adding another column in the underlying query where it is counting the office names.  And then in the report, I'm counting the total office names under each agency in a group footer.

JDettman - I wanted to try your suggestion, but I didn't understand it.  I didn't understand what you meant about "in the report declarations section, dim a variable".  I had no idea where to go for this.  Sorry.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS Access 2010 Form Building 3 23
Access 2016 Merge Tables 8 29
Stored Procedure 2 10
In or Between 2 0
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now