Report for Crosstab Query.....

[ukgthor]

I have attached a sample of a report that I have created from a crosstab query. The problem I am having is with the % Part. (participation) calculations on the left and at the bottom of the page.

Can anyone help me with the calulcations to return the correct participation percentages.

Thanks in advance.

 

[JamesMcS]

What have you got in the percentage box? How is it calculated at the moment? What are you expecting to see?

 

[ukgthor]

For the left hand side cells, I am looking for the percentage partipation of units for each of the rows - for example: (1) Months Sine LREC: =>0 and <=3 (23,162,864) vs Grand Total of Total Units (43,676,991) - this should return 53.03%

For the bottom cells, I am looking for the percentage partipation of units for each of the colums - for example: WWC - AGE 1 (2,252,340) vs Grand Total of Total Units (43,676,991) - this should return 5.16%

Hopefully this is enough information for you. The results currently in the cells are incorrect.

 

[JamesMcS]

And the formulae you have in there at the moment?

 

[ukgthor]

For the left hand side: =Sum([Total Of Total Units]/[Total Of Total Units])

For the bottom: =Sum([WWC - AGE 1/[Total of Total Units])

 

[JamesMcS]

Try

=(sum([total of total units])/sum([total of total units]))/100

And the same with the second. The first one is dividing the same field together is that right?

 

[JamesMcS]

It's also worth mentioning that formatting as apercentage multiplies the value by 100 and sticks a % at the end.

 

[ukgthor]

This didn't work. Just to be clear that the current fomulas/results shown are incorrect, therefore I am after new formulas to provide the correct results.

For the left hand side cells, I am looking for the percentage partipation of units for each of the rows - for example: Total Units for (1) Months Sine LREC: =>0 and <=3 (23,162,864) vs Grand Total of Total Units (43,676,991) - this should return 53.03%

For the bottom cells, I am looking for the percentage partipation of units for each of the colums - for example: Total Units for WWC - AGE 1 (2,252,340) vs Grand Total of Total Units (43,676,991) - this should return 5.16%

Thanks for your assistance

 

[JamesMcS]

Yep got that from your last post. When you say it didn't work, are you getting errors, or jut not the numbers you're expecting?

And also for clarity, I am suggesting formulas to replace the ones that you'ev currently got.

 

[ukgthor]

I'm not getting the results I expected. As per my examples, for the top left hand box I am returning a result of 500% when infact I should return a 53.03% result and for the left hand bottom cell I am returning 19.3% when infact I should return a 5.16% result.

 

[JamesMcS]

Right, have you addressed the duplicated field names in the calculations as I mentioned in the post above?

 

[ukgthor]

I can't address the duplicate field names, as the calculations need to refer to the same field name.

For the total 23,162,864 I am totalling the 'Total Units' for colums WWC Age 1 to WWC Age 5 and for the Grand Total 43,676,991 I am summing the 'Total Units' for rows (1) Months Since LREC: =>0 and <=3 to (5) Months Since LREC: No Date LREC Recorded

Apologies if I am missing something obvious or not explaining myself well enough.

 

[JamesMcS]

That's why it's not working then!! You can't divide the same field name by itself. Change the field names so Access can tell the difference, I would imagine the formula I suggested will work.

 

[ukgthor]

I have managed to obtain totals in the report that refer to the same field name, so I am unsure why I can't produce percentages to show the same.

The formula for the 23,162,864 number is 'Total of Total Units' and the formula for the 43,676,991 is '=Sum([Total Of Total Units]), and to get the percentage participation I tried the formula of =sum([Total of Total Units]/Sum([Total Of Total Units]) - however as mentioned this did not work. Surely if I can produce totals, I should be able to produce percentages ?.

Thanks again.

 

[JamesMcS]

As per previous post, you can't have two fields with the same name and expect Access to be able to differentiate between the two. That's why it's not working.