Summing Derived Columns in a Report

[mhuckaby]

I have a table with the following data:

Employee   Rate   Date    Hours
Joe         A      1-1-02    2
John        B      1-2-02    3
Joe         A      1-3-02    3

I want to make the following report.

Employee    Date      A-Rate    B-Rate
Joe         1-1-02      2
John        1-2-02                 3
Joe         1-3-02      3
----------------------------------------
 Sums                   5           3

I can put the rates in the A-Rate and B-Rate columns on the report by using the expression =IIF(([Rate]="A&quot,[Hours],0).

I cannot figure out how to sum the data from these "derived" columns.

Any ideas?
Thanks!

 

[PaulBricker]

Try
=Sum(IIf([Rate]="A",[Rate],0))

Paul

 

[mhuckaby]

That worked.

Thanks, Paul!

Michael

 

[raindrop123]

Can u help? not sure if i have the right place to post a question. This is my problem...........

I have a query with a date of birth field and a date of referall field.
I have put in this criteria to work out ages in the query

DD: DateDiff(&quot;yyyy&quot;,[Date of Birth],Now())+Int(Format(Now(),&quot;mmdd&quot<Format([Date of Birth],&quot;mmdd&quot)

This is all fine and works out the exact ages of pple in the database.
For the date of referral field in the query I have also added a between[start date]and{end date] parameter and this is fine too
BUT!...........

When i create a report from this i want to be able to open it with the paremeter(fine) have the ages worked out(fine) but then to be able to have ages betwen 20 - 30 etc etc. I put a calculated control in the report as following
Dcount etc etc etc

However the Dcount wont work in the report whilst the parammeter is in the query. if I remove the parameter from the query the report Dcount works fine and I can see my counts for ages 20-30 etc but if I put the parameter back into the query the dcount comes up as #Error.
Please please help!

 

[PaulBricker]

Well, you can try putting something like this in the Control Source for your Textbox.

=Sum(IIf([DD] >= 20 And [DD]<=30,1,0))

I wasn't entirely clear on how you wanted to group the ages. This expression groups everyone between 20 and thirty in one group and counts how many there are in that group. If you want to know how many are 20 and how many are 30, you would need two textboxes with Control Sources like this.
=Sum(IIf([DD] = 20,1,0))
=Sum(IIf([DD] = 30,1,0))

Summing 1's and 0's is the same as counting. Post back if you have any problems.

Paul