Results 1 to 15 of 15
  1. #1
    K Roger is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Sep 2011
    Posts
    27

    Access 2003 Select Top 3 query. Query executes but incorrect answers

    From this table File1

    File1
    AOU Data Type
    Number1 Number
    Number Number


    I have created a simple Select query in order to determine the top 3 rows (number1) within AOU.
    SELECT File1.AOU, File1.NumberCount, File1.number1
    FROM File1
    WHERE (((File1.AOU)=130) AND ((File1.number1) In (SELECT TOP 3 number1
    FROM File1 as temp
    WHERE temp.AOU = File1.AOU)));

    From this I get the following results by selecting just one AOU number (as an example):

    AOU NumberCount number1
    130 1 1087
    130 1 447
    130 1 511

    The results looks good except that the numbers in the 3rd column are incorrect as they are not the top 3 values.
    See entire table below.

    Also if I run a query with a sort field entry added:
    SELECT File1.AOU, File1.NumberCount, File1.number1
    FROM File1
    WHERE (((File1.AOU)=130) AND ((File1.number1) In (SELECT TOP 3 number1
    FROM File1 as temp
    WHERE temp.AOU = File1.AOU
    ORDER BY AOU)));





    AOU Number Count number1
    130 2 1940
    130 1 1722
    130 1 1529
    130 1 1449
    130 1 1280
    130 1 1206
    130 1 1087
    130 1 1061
    130 1 895
    130 1 890
    130 1 795
    130 1 717
    130 1 670
    130 1 623
    130 1 569
    130 1 517
    130 1 511
    130 1 447
    130 1 434
    130 1 411
    130 1 402
    130 1 351
    130 1 272
    130 1 250
    130 1 231
    130 1 212
    130 1 194
    130 1 102
    130 1 93
    130 1 40
    130 1 17
    130 1 12
    130 1 6
    130 1 5
    130 2 2
    130 1 1



    The result is the return of all records that meet the criteria "130"

    2 Questions:
    What is incorrect with the 1st query that runs OK by does not return the correct top 3 values?
    Why does adding a sort field ignore the "IN (Select Top 3" subquery return all relevant fields regardless of their value?

    Thank you for any help or suggestions!

    Roger Troutman

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Unless I'm missing something, both queries are over-complex. I think you just need this

    Code:
    SELECT TOP 3 AOU, NumberCount, Number1
    FROM File1
    ORDER BY Number1 DESC;
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    K Roger is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Sep 2011
    Posts
    27
    Thank you for the attempt but the query offered only returned 3 values for the first AOU value. There are a couple of hundred AOU values and over 1500 records in the table..
    For briefness I only sent the results of a query and table for 1 AOU value. Sorry if I mislead you by only showing an example of 1 AOU value. I think I need the subquery with the major query.

    Roger

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Your data was misleading. If you think you need the subquery then try adding the ORDER BY line from my code to your own query

    If that still isn't what you want, please post some real/realistic data and the required result or a cut down copy of your database with the same request
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    K Roger is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Sep 2011
    Posts
    27
    Thank you for your reply. I would like to send you/post a copy of a database including just the relevant, but shortened table and queries. However, I have no experience in sending such data on this forum. Could you enlighten me as to how to do this>

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Make copy of your database and delete everything not related to this question. You can also delete most of the table records as long as you leave enough data to illustrate your question.
    Modify your data if the table contains anything confidential
    Then compact and zip your database. Click the Go Advanced button then click Manage Attachments (or the paperclip button) and upload your zip file.

    The process is a bit 'clunky' so if you need more help, have a look in the FAQ section
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    "How to Attach Files" is in the forum header/tab bar.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Probably (on fly):
    Code:
    SELECT sq1.AOU, sq2.Number1
    FROM (SELECT DISTINCT f1.AOU FROM File1 f1) sq1 LEFT JOIN (SELECT TOP 3 f2.AOU, f2.Number1 FROM File1 f2 WHERE f2.AOU = sq1.AOU) sq2 ON sq2.AOU = sq1.AOU
    I'm not sure how fast this will be though - it depends on amount of your data.
    In case you need to count numbers too, then this needs an additional JOIN layer:
    Code:
    SELECT sq.AOU, sq.Number1, COUNT(f3.Number1)
    FROM ((SELECT DISTINCT f1.AOU FROM File1 f1) sq1 LEFT JOIN (SELECT TOP 3 f2.AOU, f2.Number1 FROM File1 f2 WHERE f2.AOU = sq1.AOU) sq2 ON sq2.AOU = sq1.AOU) sq LEFT JOIN file1 f3 ON f3.AOU = sq.AOU AND f3.Number1 = sq.Number1
    GROUP BY sq.AOU, sq.Number1
    And having an aggregate query from subquery adds another couple of levels to 'slow'

  9. #9
    K Roger is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Sep 2011
    Posts
    27

    Attached database

    Thanks for the latter suggestion.

    As a novice I realize that your services are neither tutorial or programming in nature. However, I am having trouble interpreting the last response as I don't know where the files sq1,sq2 come from.
    Thus I am taking the suggestion of submitting a zip file of a file as a sample of my entire database. It contains 2 tables: tblExampleDesiredFinalResults, a cut and paste example of the desire results and tblMoTest, the actual table of unprocessed data.
    and 3 queries 1) all records and 2 examples of SQL that I have written, both with incorrect results. One retrieves bottom 3 and the other retrieves all records.

    Any further help is much appreciated.
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    f1, f2, f3, sq1, sq2 and sq are aliases. They are used either to differ between different instances of same table in query (e.g. the table File1 was referred thrice, and alias allows to refer to right instance of file), to refer to subquery (like sq1, sq2, and sq in my examples), or simply make the query syntax more compact and easier to read (when referring to tables directly, sometimes this can result in quite long querystring).

    Sorry, but I can't look at your database before Monday - I don't have Access on my home comp.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I will try to look at your file later but in the meantime, I suggest you look at this article on subqueries by Allen Browne: Top N per group
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    K Roger is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Sep 2011
    Posts
    27
    Problem solved but was an unconventional fix. I copied the SQL in a query that executed but gave incorrect answers into Word 2003, then copied it back to the SQL in the query and it worked correctly! Weird!

    Thank all who gave advice in helping me solve this problem.

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No idea why Word would fix it, but from what I saw your 'incorrect' query has the sort as a parameter, which thus places the sort on the main query, not the subquery. Plus, you're sorting ascending (by default) which will give you the minimum values, not the maximum which is what I think you want. Does this work
    Code:
    SELECT TblMoTest.number1, TblMoTest.CommonName, TblMoTest.AOU, TblMoTest.NumberCount
    FROM TblMoTest
    WHERE (((TblMoTest.number1) In (SELECT TOP 3 number1
        FROM tblMoTest as  temp
        WHERE temp.commonname = tblMoTest.commonname ORDER BY temp.number1 DESC)));
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    K Roger is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Sep 2011
    Posts
    27
    Yes this code worked fine.

    Thanks for offering another solution.

    Have a great but safe day

    Roger

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Apologies. I forgot to return to this thread earlier.

    The solution Micron provided is the standard approach & is the same idea as that in Allen Browne's link that I suggested you look at.
    If you have a different solution that works, please post it here in case it is useful to others
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 9
    Last Post: 04-09-2018, 10:41 AM
  2. Replies: 3
    Last Post: 03-14-2018, 01:53 PM
  3. Module executes but then Hangs Access
    By Nishy in forum Modules
    Replies: 4
    Last Post: 02-24-2014, 06:11 PM
  4. select multiple checkboxes in access 2003?
    By GoneFusion in forum Forms
    Replies: 2
    Last Post: 09-20-2010, 01:44 PM
  5. Access 2003 Query Help?
    By Stirling Steve in forum Queries
    Replies: 1
    Last Post: 08-26-2009, 03:25 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