Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Posts
    76

    Unanswered: Top 5 Error (by group)

    Hello all!

    I had a previous "Top 5" errors thread, but I now need something a bit different, so to save some confusion, i'm making a new thread.

    Anyway- I need a Top 5 errors by group query. The resulting query should look like this:

    Question Errors
    ---------- --------
    Q1 10
    Q2 9
    Q3 7
    Q4 5
    Q5 2

    Each Q(n) represents a 'question' column in one of my tables.
    So, Q1 would equal 'Acct_paid_off', and so on.

    I've tried to get these results... but the only thing I keep getting is something like this:

    Q1 Q2 Q3 Q4
    10 9 7 5

    Which would't work.

    Here is my table layout:
    Attached Thumbnails Attached Thumbnails table.jpg  

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    But what constitutes an error?

  3. #3
    Join Date
    Jun 2003
    Posts
    76
    OOps sorry! an UNchecked box is an error.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Ok. Well this is how I would solve this ... I would query for the sum of each unchecked checkbox on each column and save that # to a temp table ... I would then query the temp table for the TOP 5.

    The difficulty is that you're converting from columns to rows ...

  5. #5
    Join Date
    Jun 2003
    Posts
    76
    I thought about that. But, how would I get the question into the table?

    If I create a count query, the result would look like this:

    Acct_paid_off
    ---------------
    3


    I would need something like this

    Question Errors
    --------- ---------
    Acct_paid_off 3


    Is there any way to create a 'dummy' field in SQL so I can place the question in there, as shown above?

    Thanks for your help!

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by Sucoyant
    I thought about that. But, how would I get the question into the table?

    If I create a count query, the result would look like this:

    Acct_paid_off
    ---------------
    3


    I would need something like this

    Question Errors
    --------- ---------
    Acct_paid_off 3


    Is there any way to create a 'dummy' field in SQL so I can place the question in there, as shown above?

    Thanks for your help!
    That is easy ... Keep in mind that you're gonna query for each column ... So you write out what you want to the temp table. In this case, I'd make a temp table with a column called Question Description and a column called say Question Errors...

    THEN you're result query would be something akin to:

    SELECT TOP 5 [Question Description], [Question Errors] FROM MyTempTableNameHere ORDER BY [Question Errors] DESC;

    The value add to this is that you can be more descriptive of the error condition like "Account has been paid off" or "Missing this or that" ...

  7. #7
    Join Date
    Jun 2003
    Posts
    76
    How can I create a 'dummy' field to store my question in the query?

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    How were you planning on writing/running this? Is this going to be run from a form?

  9. #9
    Join Date
    Jun 2003
    Posts
    76
    This query is going to be fed to a report.

    From what I gather, the only way that I could really sort the query the way I want to, I would have to do it in the query before it gets sent to the report.

    I would like the results of the query to look something like this. Each question would correspond to a field in the table.

    Ugh.. maybe i'm thinking way off base. I'm having an off day, sorry if i'm not making sense.
    Attached Thumbnails Attached Thumbnails need.jpg  

Posting Permissions

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