# Thread: Cross Tab Query (A2000)

1. ## Cross Tab Query (A2000)

I've created a Cross Tab query which shows Efficiency from 01 Jan 2002 to 31 Jan 2002 which is throwing an incorrect percentage.

The sum from Labour Booking TG= 286.75 TT=232.5 should work out at 123%
The sum from qryCrossTabOpEff TG= 286.75 TT=232.5 should work out at 123%

When running qryCrossTabOpEff1, it calculates at 127.46% How can this be.

Would some one mind looking at the attachment, I can't figure it out.

(Sorry about the naming convention, it's an old db )

Dave

2. ## Re: Cross Tab Query (A2000)

Hi Dave,
I think your problem is that your crosstab query is taking the average of the previously calculated percentages. This is an unweighted average and not equal to the sum of dataA/sum of dataB. A simple example:
<table border=1><td>Row</td><td>A</td><td>B</td><td>Average</td><td>Row1</td><td>100</td><td>50</td><td>50%</td><td>Row2</td><td>7500</td><td>2500</td><td>33.33%</td><td>Total:</td><td>7600</td><td>2550</td><td>33.55%</td></table>
but the average of 50% and 33.33% is 41.67%.
Hope that helps.

3. ## Re: Cross Tab Query (A2000)

Sorry Rory, I understand but am not quite sure how to remedy it.

The field [tg]/[tt] is ness for the calculation.

I'm stumped on how to fix it.

Dave

4. ## Re: Cross Tab Query (A2000)

Dave,
Change the Eff field in your crosstab query to somehting like:
AveEff: sum([TG])/sum([TT])
and change the entry in the Total row from Avg to Expression. This should then give the answer you need.
Hope that helps.

5. ## Re: Cross Tab Query (A2000)

Thanks Rory, thats sorted the problem.

Just one thing before I put this problem to bed.

I have attached the Query in design view and also the report produced from it.
Would you mind just looking at it in case it's incorrect. I was thinking about the months with no data, would the expression need changing to allow for this, and how.
Sorry I'm no good with syntax .

Dave

6. ## Re: Cross Tab Query (A2000)

Dave,
It looks OK to me from what I can see. The only problem I can think of would be if your TT total value were 0, so you might want to check for that if it could occur - something like
iif(sum([TT])=0,0,sum([TG])/sum([TT])).
Hope that helps.

7. ## Re: Cross Tab Query (A2000)

Thanks Rory.

Every thing is ok now.

Dave

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•