Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    18

    Unanswered: how to get a top ten report in access97?

    Hi all

    i am trying to get a report to display the 10 most borrowed toys from our toylibrary

    the thing is i have some code already,
    but if there happen to be toys who have been borrowed the same amount of times, i get 11 or even more items in my TOP 10 List


    my current sql code in my query looks like this at the moment:


    SELECT TOP 10 ToyInventory.*
    FROM ToyInventory
    ORDER BY ToysinTable.TimesBorrowed DESC;






    articlename x-borrowed

    toybear black 12
    truck yellow 10
    firetruck 8
    toytelephone 5
    crane 7
    playmobil horses 5
    farm 10
    scooter 6
    wigwam 4
    fireenginetruck 2
    animal farm 9
    rubik cube 1



    the report would show 12 items instead of 10, just because there's some toys who have been borrowed the exact number of times

    like the

    truck yellow 10
    toytelephone 5
    playmobil horses 5
    farm 10


    u c?


    but i dont want that, cuz it would be stupid to show MORE then 10 items in a TOP 10 list


    does anyone know how i can fix this?


    many thnx







    PROBLEM IS SOLVED THNX TO HomerBoo
    Last edited by mrsplash; 10-09-03 at 06:13.

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I just created a table with your toy data, the toy and number of times borrowed. The query I created only returned 10 results, but my SQL is a little different than yours:

    SELECT TOP 10 ToyInventory.articlename
    FROM ToyInventory
    ORDER BY ToyInventory.timesborrowed DESC;

    I noticed that your SQL is ordering by a table called ToysinTable, but it's not part of your table source FROM clause. How does it fit in?
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    18
    Originally posted by HomerBoo
    I just created a table with your toy data, the toy and number of times borrowed. The query I created only returned 10 results, but my SQL is a little different than yours:

    SELECT TOP 10 ToyInventory.articlename
    FROM ToyInventory
    ORDER BY ToyInventory.timesborrowed DESC;

    I noticed that your SQL is ordering by a table called ToysinTable, but it's not part of your table source FROM clause. How does it fit in?

    u r right
    1st i had 2 translate my example from dutch(my native language) to english


    i am using more tables/queries
    all the data is beiing ordered according my MAINTOYTABLE
    this maintoytable is ordered by TOYCODE
    TOYCODE is field with this inputtype:

    Inputmask: >L" "000;0
    required: yes
    allow zero length: no
    indexed: Yes (No Duplicates)


    the MAINTOYTABLE looks kinda like this:
    (its just a very small piece of the big table itself, but its here 2 give u a slight idea)

    the first piccy is the maintable

    and the second is a rough sketch of the top ten(u will see that it currently shows more then 10 toys)




    PROBLEM IS SOLVED THNX TO HomerBoo
    Last edited by mrsplash; 10-09-03 at 06:11.

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    If you want, you can attach a db with just the table, the report, and the query the report is based on. I'll take a look at it and try to determine why you are getting more than 10 results.

    My inclination is to suggest that you insert a query between the existing basis and your report which calcs the top 10, then base the report on that. Without having more data, it is a little difficult to speculate.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  5. #5
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    18
    Originally posted by HomerBoo
    If you want, you can attach a db with just the table, the report, and the query the report is based on. I'll take a look at it and try to determine why you are getting more than 10 results.

    My inclination is to suggest that you insert a query between the existing basis and your report which calcs the top 10, then base the report on that. Without having more data, it is a little difficult to speculate.

    Ok, homer , i translated part of the database in english,
    it now only contains a toytable and no query or report, so u can test and try to make a good query for me
    how do i attach the table?


    PROBLEM IS SOLVED THNX TO HomerBoo
    Last edited by mrsplash; 10-09-03 at 06:13.

  6. #6
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    zip and attach file. the section is below the Options:

    you may want to include the query you used insofar as I may be able to see why it's not working. it's absence won't stop me from helping you, though.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  7. #7
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    18
    i tested it using your sql and now it worked fine,
    it has indeed 2 do with the extra table u mentioned

    well i added the example in a zip file, and treated the code like u told me and now it works.



    PROBLEM IS SOLVED THNX TO HomerBoo
    Last edited by mrsplash; 10-09-03 at 06:12.

  8. #8
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326

    Thumbs up

    Cool.

    Do you still need me to look at it?
    All code ADO/ADOX unless otherwise specified.
    Mike.

  9. #9
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    18
    Originally posted by HomerBoo
    Cool.

    Do you still need me to look at it?


    That won't be necessary, thnk u for ur advice

    but... i think i will have more problems... so be prepared for some more questions from me


    grtz

Posting Permissions

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