How to Count in Report

[MysticElaine]

I am building a report that has a Providers header, Language header, and Type header grouped on each other. So when it prints out, it states each Name, then every language associated with the person, and for each language if they interpreted or translated.

I already know how to do the regular count function for each header. What I am trying to figure out is if I can do a sort of DCount or not. The Provider/Name header just counts all records for that name and I need to try to split that count into two sections, one where it shows how many times the person did something for themselves and how many they did it for someone else. I don't know if I will be able to do it or not since it is in a header area.

Example:
John Smith has a total of 10 records he spoke spanish to someone. 7 of those records he spoke spanish with his own client. 3 of those records he spoke spanish to someone else's client.

In my query that I run the report off of, I have 2 fields, A (which is not on the report) and B (which is the Providers/Name header). I would like to try to count so that for each person under Name header if A=B it means his own client and if A =/= B then it means someone else's client.

I hope this doesn't confuse anyone too much. Below is what my report looks like now and in bold italics is what I want to add.

John Smith 10  Own Clients 7  Others Clients 3
     Spanish 5
          Interpretation 5
     French  5
          Interpretation 3
          Translation    2
Jane Doe  20   Own Clients 20  Others Clients 0
     German 20
          Interpretation 15
          Translation    5 

Thanks!

[twinnyfo]

Elaine,

In the query that you build for your report, include that very same language for two new fields:

OwnClient: IIf(A=B,1,0)
OtherClient:  IIf(A<>B,1,0)

Then, in your Header, add two Text Box Controls:

txtOwnClient, which has the Control Source:

=Sum([OwnClient])

and txtOtherClient, which has the Control Source:

=Sum([OtherClient])

Hope this makes sense.

[MysticElaine]

Thanks for the response! I tried to add the 2 new fields, hope I did it right...it comes up Exp1: [ownclients] and Exp2: [otherclients]. However, when I go to run the report, it asks me to enter the parameters for the two fields, which can't be left blank. Any ideas? Thanks!

[jforbes]

MysticElaine,
Twinnyfo's recommendation is asking you to create two Columns in your Query with the following as their Source (in Field: in the Query By Example Editor): "OwnClient: IIf(A=B,1,0)" and "OtherClient: IIf(A<>B,1,0)" instead of "Exp1: [ownclients]" and "Exp2: [otherclients]"

The error message you are getting is from Access failing to resolve [ownclients] and [otherclients] to then stuff them into the Fields Exp1 and Exp2. When the Query runs and Access doesn't know what they are, it asks the user, so you probably got a couple dialog boxes that you Cancled out of, then Access really didn't know what to do with it, so it gave you the Error.

Let us know how it works.

[MysticElaine]

Perfect! Thanks!!!!!

[twinnyfo]

@jforbes,

Thanks for explaining more better than I did!

Peace!