Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Top 10's (2003 (11.6566.6568) SP2)

    Hi there.

    I am having problems with the attached DB and Excel workbook. What I am trying to achieve (as shown in the Excel Workbook) is to get the top 10 Behaviours according to ompliant (in descending order) grouped by lngPeriod, lngDelGrp and CheckList.

    This is driving me nuts. I have tried countless permutations according to knowledge passed on to me on this forum before, but am having no luck. in getting the results I am looking for. Could someone please advise me where I am going wrong?

    Cheers,

    Niven <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Top 10's (2003 (11.6566.6568) SP2)

    According to your Excel workbook, you only want to group by lngPeriod and lngDelGrp, *not* by Check List. If you also want to group by Check List, there is no group with more than 10 items, so there's no need to use a top 10 query.

    You must explicitly specify where the fields are from in the subquery to avoid confusion. See attached version.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top 10's (2003 (11.6566.6568) SP2)

    Hi Hans,

    Many thanks your reply. I'm beginning to see the light with these having confused myself no end. The modified query seems to be what I am really looking for. However, having removed the period Pe07-2007 from the lngPeriod criteria and rerun for all periods, I see that in period Pe09-2007, for example, I am getting more than 10 results for lngDelGrp's "P-Way" and "TSDG". Could you advise me as to why this is, please?

    Cheers,

    Niven <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Top 10's (2003 (11.6566.6568) SP2)

    This is because there are multiple records with the same value for %Compliance. Take for example the following values:
    lngPeriode = "Pe07-2007"
    lngDelgrp = "P-Way"
    The 10th record in the query with these values has %Compliance = 11.11%, but there are 6 further records with the same %Compliance. A Top 10 query includes them all.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Top 10's (2003 (11.6566.6568) SP2)

    Hans,

    Many thanks again for that. The penny has finally dropped.................!

    Cheers,

    Niven <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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