Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2004
    Location
    London
    Posts
    76

    Unanswered: selecting top 100 records

    Hi there,

    I need to have a query where i can return top 100, 200, 300, 400 and 500 records by revenue.

    How do I do this in Access?

    Thanks...

  2. #2
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi
    You can use teh "Top Value" in query, here's how


    1) Create a query in Design view.

    2) In the Database window, click Queries under Objects, and then click

    3) Add to the design grid the fields you want to display in the query's results, including the field you want to display top values for.

    4) In the Sort cell of the field you want to display top values for, click Descending to display the highest values or Ascending to display the lowest values. If you're sorting on other fields in the query's design grid as well, those fields must be to the right of the top values field.

    5) Click in the Top Values box on the toolbar.

    6) Enter the percentage or the number of highest or lowest values you want the query results to display.
    Note To display a percentage, enter a number followed by a percent sign (%).



    To view the query's results, click View on the toolbar.
    Note Setting the TopValues property in the query's property sheet works the same as using the Top Values button on the toolbar.


    HTH
    John

  3. #3
    Join Date
    Feb 2004
    Location
    London
    Posts
    76
    Thanks for the advice...however it does not do aid in what I need to acheive...

    I have a query which is related to a form, the form contains combo boxes...so a user can search by customer name, post code etc and retreive a list of customers which meet the specified criteria...however the user would also like to search by top 100, 200, 300 etc clients by revenue.

    i.e. top 200 clients in post code area W1

    I dont think I can use what you have specified in my query...any other advice would be helpful...Thanks

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    SELECT TOP 100 From yourTAbleName
    SELECT TOP 200 From yourTAbleName
    SELECT TOP 300 From yourTAbleName
    SELECT TOP 400 From yourTAbleName
    SELECT TOP 500 From yourTAbleName

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    ... And to expand further on Hammy's queries:

    "SELECT TOP " & CountControlHere.Value & " FROM ...

    Where you provider say a combobox to select which count to use ...

  6. #6
    Join Date
    Jan 2004
    Posts
    75
    Where does that select statement go? I make a query work just fine... but how to prompt for different values?

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by sublimsos
    Where does that select statement go? I make a query work just fine... but how to prompt for different values?
    Well if you have a textbox and a button you can set it up in the OnClick event of the button ...

  8. #8
    Join Date
    Jan 2004
    Posts
    75
    Okay... below is the query...

    Let's say... I name a text box [pcttxt] so that the user can input the desired percentage... where does the statement below go? If I put it in the on click event exactly as it is, I get errors. I tried RunSQL before I figured out that it's only for action queries... I don't know VB all that well and I simply don't know how to make it work.

    "SELECT TOP " & [pcttxt].value & "PERCENT Data.*
    FROM Data
    WHERE ((Data.[Duty Status])='pfd')
    ORDER BY Rnd(IsNull([data].[last name])*0+1);"

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by sublimsos
    Okay... below is the query...

    Let's say... I name a text box [pcttxt] so that the user can input the desired percentage... where does the statement below go? If I put it in the on click event exactly as it is, I get errors. I tried RunSQL before I figured out that it's only for action queries... I don't know VB all that well and I simply don't know how to make it work.

    "SELECT TOP " & [pcttxt].value & "PERCENT Data.*
    FROM Data
    WHERE ((Data.[Duty Status])='pfd')
    ORDER BY Rnd(IsNull([data].[last name])*0+1);"
    Well right off the top, your SQL statement is invalid. PERCENT is not a SQL keyword that I know of ...

    Question: Where is this supposed to go? Combobox? Listbox? Form? Where?

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    right up the top you say:
    I need to have a query ...
    so make a new query, viewQL, and paste (valid!!!!) sql in there.


    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by izyrider
    right up the top you say:


    so make a new query, viewQL, and paste (valid!!!!) sql in there.


    izy
    Different person Izy ... The topic has been quasi highjacked ...

  12. #12
    Join Date
    Jan 2004
    Posts
    75
    Sorry for highjacking... it's really the same topic (or so I thought...). I was having an similar issue when I saw the post... so instead of bringing up the topic AGAIN... thought I could get some relevant info.

    Anyhoo... I did get that statement from making a query and then switching to SQL view. It's the same as top # of records... only get a top % of records instead. Perfectly legit. It works fine as a query (replacing the control reference with appropriate percentage of course)

    I was hoping I could somehow set it up to let the percentage or # of records be user defined... because sometimes we need 10%, sometimes 50, sometimes 25%, etc...

    I took the exact statement, pasted it in the on_click event. Doesn't work. Why does it work as a query... but not in code?

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by sublimsos
    Sorry for highjacking... it's really the same topic (or so I thought...). I was having an similar issue when I saw the post... so instead of bringing up the topic AGAIN... thought I could get some relevant info.

    Anyhoo... I did get that statement from making a query and then switching to SQL view. It's the same as top # of records... only get a top % of records instead. Perfectly legit. It works fine as a query (replacing the control reference with appropriate percentage of course)

    I was hoping I could somehow set it up to let the percentage or # of records be user defined... because sometimes we need 10%, sometimes 50, sometimes 25%, etc...

    I took the exact statement, pasted it in the on_click event. Doesn't work. Why does it work as a query... but not in code?
    As for the PERCENT, learn something new every day ... (or I need to look it up before responding). As to why it won't work in code: Post your code so we can see it ... It might be something obvious, but impossible to tell without a visual ...

  14. #14
    Join Date
    Jan 2004
    Posts
    75
    I've never known how to make SQL statements work... so... well... I don't know what to do.... my disclaimer as always: I'm self-taught and like a moron developed this database that is now rather crucial to our company... so... I have to keep learning because they seem to think I know what I'm doing. <sheepish grin>

    Private Sub UACmd_Click()
    SELECT TOP 10 PERCENT [Data].*
    From Data
    WHERE (([Data].[Duty Status]) = "pfd")
    ORDER BY Rnd(IsNull([data].[last name])*0+1);

    End Sub

  15. #15
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by sublimsos
    I've never known how to make SQL statements work... so... well... I don't know what to do.... my disclaimer as always: I'm self-taught and like a moron developed this database that is now rather crucial to our company... so... I have to keep learning because they seem to think I know what I'm doing. <sheepish grin>

    Private Sub UACmd_Click()
    SELECT TOP 10 PERCENT [Data].*
    From Data
    WHERE (([Data].[Duty Status]) = "pfd")
    ORDER BY Rnd(IsNull([data].[last name])*0+1);

    End Sub
    Well you got the query part ... You have to wrap it in either an ADO or DAO Recordset ... Or make it the RowSource for a control or form ... Where is this info to be displayed at?

Posting Permissions

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