Calculating data from records results of a cross tab query

[ChuckW]

Hi,

I have a report based on a cross tab query that give sales for 2007 and 2008
and budget amounts for 2008 by month for each client. The rows headers of
the cross tab are Client and Year (2007 Actual, 2008 Actual and 2008 Budget).
The field headers are the months in the year (January - December). I have
created a report where the page header is the Months, the FullName hear is
the Client Name and the Detail section has the results. It would look
something like this:

January February March April May June July
.....
Joe Smith
2007 Actual $400 $500 $600 $500 $400 $300 $200
2008 Actual $600
2008 Budget $500

I want to compare the 2008 Actual to the Budget and then give a percent to
budget for each client. So for this example, the client was at 120% for the
month of January ($600/$500). I would run this report at the beginning of
each month. I am trying to figure out how to calculate two records.

Thanks,

Chuck

 

[Duane Hookom]

There is a dynamic monthly crosstab solution at
http://www.tek-tips.com/faqs.cfm?fid=5466. I would combine this with
http://www.tek-tips.com/faqs.cfm?fid=4524 to get Actual and Budget in the
same output row. You could then calculate the percent.

 

[ChuckW]

Duane,.

Thanks for your help. I looked at your note and the links. Does this mean
that in order to do a percentage calculation the two values need to be in the
same row? I have January Actual and January Budget in separate rows. One on
top of the other. I want to find the percent to budget (January Actual
divided by January Budget)? It is easy to do something like this in Excel.
I wonder if I can somehow combine the two programs together