COUNT in Report phase 1

[fbacchus]

In my report, I have several fields including "Transaction Kind" and a "Seller's Name". I am sorting the report by Location (another field in the report then by "Transaction Type". At the end of each transaction type, I display a count of sellers for that transaction type. For this I use =Count([Seller_Name)].

At the end of a location, I would like to count all of the sellers for that location but sellers can be within the different Transaction Types and the counts are inflated versus the count of the "uique" sellers in the Location. Is there a "Count Distinct" so that I can get the "real" count of the sellers' in each Location ? If so, what is it or what is the alternative.

thanks

fb

 

[mikey69]

Right click the form in design mode and choose sorting and grouping. Choose Location as a sorting field, check group footer. Then put a text box in the footer that will appear, choose client name, or some field that is always there. In the data source for the text box type "=Count([FieldName]), and you should get what you are looking for.

Access makes all things possible. It even makes them intelligible

 

[fbacchus]

mikey69:

Thank you for the response. My report is set-up the way you are suggesting and am getting the location counts. The problem is (explained below), given the example below:

Location A:
Trans-type - SellerName - Trans-Count
A - John B 100
- Mary J 120
- Joe S 140

** Total No Sellers 3 -- Trans count 360

B - Frank C 150
- Mary J 220
- Isabel T 640
- Tom W 430

** Total No Sellers 4 - Trans 1440

Loc A Totals - Users 6 : Trans Count 1800

The reason that the User count is 6 for the location is that Mary J represents 1 user although she appears in 2 different "Trans Type".

Hope this clarify the issue. Thanks again.

frank







fb

 

[THWatson]

frank
Do you want the total sellers to show as 6, or 7? I assume 6. Correct?

You can get the total sellers to show as 6 by...
1. Make an additional Sort/Group on Seller
2. In the Seller footer, put a tiny text box. Make its control source =1, and Running Sum Over Group. Let's call it txtSeller
3. Make the Seller Footer small, and set Visible to No.
4. In the Report Footer, put a text box that has its control source as txtSeller

See if that gets you closer to where you want to be.

Tom

 

[DaveyEdgar]

hmm,
I followed those directions and the text box in the report footer says "1". But I have it should say "7" since I have a total of 7 records that are distinct.

 

[THWatson]

DaveyEdgar
Is this a different post?

Tom

 

[DaveyEdgar]

Hi Watson yes thanks it is.

I found this on a search because I have employee evaluations. The evaluation has 10 questions.

My query has basically the name of the evaluator, the employee, the 10 questions and their answers.

My report has no detail. It is grouped on first the employee and underneath lists all his evaluations with the score. It doesn't list all the questions and answers.

I want a text field that will count distinctly the amount of evaluations total. For example If there is a total of 4 evaluations done, my "=count(evaluationID)" control source should say "4". However, it says "40", because the query includes all the questions.

I tried the method above, but the total says "1". Anyone know why?

 

[THWatson]

Davey
You could try an unbound text box with its control source set to =Sum(Abs(evaluationID))

If this doesn't do it, post back with an example of what you are getting and what you should get.

By the way, it really is best to make a new post rather than asking a question on an existing post. It would have been easy to miss your question because the post already appeared completed. Also, there are lots of experts out there, so you will get an answer quicker with a fresh post.

Tom

 

[DaveyEdgar]

Watson thankyou kindly I'll start a new post and list my steps and results