Results 1 to 3 of 3
  1. #1
    aat is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    30

    count (*) timing out

    Hi,



    I have a dataset of 750+ records. Each record contains 6 particular numerical information that I need to rank: num1, num2, num3, num4, num5 and num6. After I know the rank each record obtains in each num, I average these 6 rankings, which gives an average rank (avgrk). So far, so good. I have been working with this for more than a year and never had a time out problem.

    Now I posted a separated thread yesterday where I explained I wanted to rank each record on its avgrk; that, ultimate, rank (ovrlrk) would have ideally been calculated as a subquery within my same Q1 but a forum user explained me no way, get it done in a q2. My problem here is that my q2 does not respond; my access freeze and I have to crash the process after more than 10min.

    Here are my codes:

    Q1 called pctlrk

    SELECT Q1.ID, Q1.[name], Q1.[Class], Q1.[num1], Q1.[num2], Q1.[num3], Q1.[num4], Q1.[num5], Q1.[num6], (select count(*) FROM tbl1 WHERE [num1] < [Q1].[num1];)+1 AS [num1rk], (select count(*) from tbl1 where [num2] > [Q1].[num2];)+1 AS [num2rk], (select count(*) from tbl1 where [num3] > [Q1].[num3];)+1 AS [num3rk], (select count(*) from tbl1 where [num4] > [Q1].[num4];)+1 AS [num4rk], (select count(*) from tbl1 where [num5] > [Q1].[num5];)+1 AS [num5rk], (select count(*) from tbl1 where [num6] > [Q1].[num6];)+1 AS [num6rk], ([num1rk]+[num2rk]+[num3rk]+[num4rk]+[num5rk]+[num6rk])/6 AS [avgrk]
    FROM tbl1 AS Q1;

    Q2 called ovrlrk

    SELECT [Q2].ID, [Q2].[avgrk], (select count(*) from [ptclrk] where [avgrk] < [Q2].[avgrk];)+1 AS [Overall rk]
    FROM pctlrk AS Q2;


    For those who successfully pass this test, there is a subsidiary question: how can I have [overall rk] re-inserted back into my Q1 as the last field without creating a circular reference?

    Thanks for all comments & suggestions!

    Aat

  2. #2
    aat is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    30
    Hi again - I have been thinking further about this and want to check if one idea could give the direction to that problem.

    With 750+ records I end up with single set of rankings looking pretty much like that: num1rk = 278, num2rk = 79, num3rk = 451, num4rk = 612, num5rk = 213, num6rk = 303. Which gives an average rk of 272.1666666666666...

    Does the time-out come from the fact that most avgrk have an indefinite number of digits after the comma? If so, what could I used in my Q2 to query avgrk at a rounded figure to the two decimals?

    Thank you again for any help.

    Aat

  3. #3
    aat is offline Advanced Beginner
    Windows 7 Access 2010 32bit
    Join Date
    Sep 2010
    Posts
    30
    OK - a good old rounding works guys. I finally have my Q2 working.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with a Count...
    By kgbo in forum Access
    Replies: 2
    Last Post: 08-20-2013, 01:50 PM
  2. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM
  3. count & sum
    By ali zaib in forum Access
    Replies: 1
    Last Post: 01-14-2012, 04:58 PM
  4. Count on ID
    By dssrun in forum Access
    Replies: 4
    Last Post: 07-26-2011, 11:45 AM
  5. timing how long users are on each form
    By salej60 in forum Access
    Replies: 3
    Last Post: 06-07-2011, 08:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums