Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •