Calculated field using detail information

[fredk]

I had the problem of getting two values into a crosstab query - I ended up creating two seperate crosstab queries and then I brought them together into one union query. That solved my problem of getting both values into one query - My result is:

acct mgr expr1 total Jan Feb Mar
joe smith Base Members 7870 1831 299 401
joe smith Current Members 6665 1408 299 401
sue ditto Base Members 10340 3558 640 27
due ditto Current Members 9110 2887 640 0

(the data goes through to December....

My problem is with the report - I created the report with an "expr1" header and footer - What I need to do is to be able to divide the current members into the base members to get a percentage. That is where I am having a problem.

The data is in the detail section - I was trying to somehow get the division done in the footer of the expr1? Here is what the report looks like

acct mgr expr1 total jan feb mar
Andrew Bandilla
Base Members 7870 1831 299 401
Current Members 6665 1408 299 401
Joanne DiGeronimo
Base Members 10340 3558 640 27
Current Members 9110 2887 640 0

I hope I explained this correctly - Any help would be greatly appreciated!!!

Fred

 

[ElSteveO]

If you're trying to do it in the report footer it should be pretty easy. However, I'm not real clear on what you're trying to accomplish. Do you want to divide the grand total of the current members by the grand total of the base members to get one number, or do you want separate totals for each rep for each month?

If it's the latter, then I gather you want, for ex, Joanne DiGeronimo's total (9110/10340), then her monthly percentage too, like for Jan it would be 2887/3558. Is that correct?

Steve

 

[fredk]

Thanks for replying ElSteveO - I did get it figured out - You are correct, my object was to divide the current members by the base members for each person -

The result seemed pretty easy after I figured it out but it took me about a day to figure it out - I just used the if statement in the souce of unbound fields


Thanks!!!

Fred