Counting unique records

K

Kila

Registered User.
Local time
Today, 14:36
Joined
Mar 5, 2003
Messages
275
I am writing a report based on a query of 2 related tables. One table is of mailings sent out, with a primary key called Hard ID. The other table is of places the mailings were sent to with a primary key called ID. As you can imagine, one mailing may go to several places, so when the query is run, the same Hard ID will be listed for each recipient of a particular mailing.

In my report, I am able to count in the report header the total items sent out by just entering =Count([QueryName]![ID]), but how can I count the unique Hard IDs? There may be 10 mailings, but 100 total items sent (and records in the query) because each mailing goes to several recipients. The count of [ID] returns the 100, but how can I get the 10 (unique [Hard ID])s?

I'm sure I am missing something very minor, But thanks in advance!
 
Can't you use the DCount function on Hard ID in table A.

This depends on the criteria for mail sent.

Also you could try a Totals Query and include the HardID and group by Count on this.

Then you would need to use the DLookUp function
 
Thanks...

I thought of that, but could not determine what to base the DCount on....Count HardID under what conditions? I know I could to another query, and hide the duplicates, but there are several reports (10 I think), each based on a different query that prompts for information....Start & end dates, contact person, facility, etc. I was hoping to do this an easier way, if possible.

Maybe what I should also ask is an easier way to search. Is it possible to prompt for all the different varieties of data, & allow the user to enter only the limiting factors (s)he is interested in? I could not think of a way to get it to work without entering something in every field. I ended up having to create several queries.

If you had an answer to my second question, it would make your response to the 1st question a whole lot easier! Thanks for responding!
 
Ok, you can do it on the report.

Include A Hard ID group section on your report (if you haven't already)

Add a text box to this section and in the control source put...

=1 and set the Running Sum to Over All.
Set it to Visible =Yes

Name it intHardIDCount

This acts as a 'counter'

in the report footer add another textbox (Visible) and controlsource =intHardIDCount

This will display the total Hard IDs.
You won't be able to put this in the header (AFAIK)
 
Thank you. That worked, but I was hoping to put it in the header so that the total number would be the 1st thing you see. I suppose you have to choose easy fix in the wrong place, or time-consuming fix in the right one. Pick your poison!

Since I am preparing this database for someone else in another building who is not very comfortable with computers, can you tell me how to put 2 buttons on my report...one that "jumps to" the last page where the stats are, and one that prints just that one page. I know that either of these are easily accomplished using the arrows & file menu, but this is NOT a computer-oriented user, and I am not across the hall to help him all the time. I want to make it very easy for him. Thanks!
 
You can't use buttons on a report in the same way you can with forms.

If it's a particular [Hard ID] you want to print then youw ill need to either base your report on a parameter query or use a WHERE condition in your open report referencing an open record or selection mad from a combo/list box.

The latter is probably the best method.
 
Actually...as I think about it, a small "toolbox" that is always on top when the report is open may do the trick...
 
Well, the entire report is already based on a parameter query that is pulling the data in which each HardID may come up several times for each ID, and it utilizes the detailed data in all those rows in the report. The user is asking for some summary information.

I was thinking that since you can't put buttons on the report, a little toolbox may help users manouver...I could put any button I want there, and label them well. My friend probably will not remember the arrow buttons at the bottom since he will not need them daily. He will not have a problem with a button that says "Goto stats" or "Print page". Without them, he will forget & click the printer button & print all 200 (or more) pages. I work with a lot of folks who are not familiar with some very basic computer use. To keep from being called ALL-THE-TIME when they forget something, I try to "dummy-proof" my databases with really obvious buttons for things they will need to do & keep them out of the MS toolbars altogether.

However, I am forever trying to get Access to do something that SEEMS easy, but I have hard time accomplishing it. I just started working on the macro, but goto page & goto control are for forms.

At the risk of seeming like a dummy myself, What is AFAIK?

THanks for your responses.
 
Please let me know if you are aware of an easier way to accomplish what I'm trying to do.... Thanks!
 
If you want the user to be able to print a particular record(s) then yes this is straight forward.

But a particular page from a record is not something that I am familiar with. A report is normaly a full report, be it all records, one record or some records.

If you want a stats report then create a seperate report for just stats and then print this. The data from this can be created with Total queries if need be
 
Actually I was just referring to the feature under print....you can print all pages, or certain pages. If you can go to the print preview of the last page of the report (where the footer is) you should also be able to print just that page. I know I could create a separate report, which I may have to do, but it would mean 10 seperate reports for all the different reports there are.
 
You must log in or register to reply here.

Similar threads

J
Count IIf Function in Report
2
Replies
22
Views
479
Pat Hartman
P
P
Count different values in the same field
Replies
4
Views
642
Pat Hartman
P
U
Adding up counted records to get a total
Replies
11
Views
399
UltimateNeo
U
R
Count unique values
Replies
8
Views
736
June7
June7
T
How to hide a label on 2nd subreport if 1st subreport has no data
Replies
11
Views
909
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom