Results 1 to 14 of 14
  1. #1
    gmontano is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    9

    Row number in a query

    Good morning.

    Is there an easy way to get a row number in a query?

    I want it based on how I set up my query, with the sorts and criteria, and have a field in the query that gives it a row number starting with 1, regardless of what the auto number is in the table.



    I have tried multiple things, but have not been able to figure it out.

    Thank you for your help.

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    How do you use the row number? maybe we can find another way other than generate a row number.

  3. #3
    gmontano is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    9
    I need a certain group of people to pull random records from a database. They may need to pull 100, 50, or any other number.

    I created the query with a field that gives a random numbers. The query than sorts it by random number first. I was trying to have a field with a row count from the query next. Then in the form, they could select how many records and it will only print that number of records to the report they will work.

    I hope this makes sense.

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    You don't need a row number, just use
    select top 50 * from yourQuery1
    to get first 50 records. ( yourQuery1 is the query which you sort by random number).

    Assume your report's data source is Query2, then in your form, change the statement of Query2, then open the report.
    Code:
    private sub Command1_click()
        currentdb.querydefs("query2").sql="select top 50 * from yourQuery1"
        docmd.openreport "yourReport"
    end sub

  5. #5
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    if you don't mind using VBA, you can turn your query into a make table query and then add through ADOX an autonumber field. After the table has served its purpose it can be deleted again.
    Don't forget to compact your database on regular intervals. Creating and deleting temporary tables tend to blow up the size of your database.

    grNG

  6. #6
    gmontano is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    9
    Thank you both for your suggestions.

    A group of 5 people who know less about access than I do will need to use it.

    In the form, they are able to select a type of record, a date range for the record, and what I was hoping, the number of records they want to retrieve, they may just need to pull 1.

    Are you suggesting each time they go in and change a statement?

    I was just trying to make an easy form for them to use.

    I am pretty sure I could do it if I could just figure out how to get a list in a 1 - whatever amount of records come back on the query depending on the criteria.

    I may misundertood you, but I can not have them going in changing anything other than options on a form.

    Noella, I have thought about that and haven't tried it yet. I have searched around and have seen this question quite a bit, but have not found an easy way to do it. It seems like it would be simple enough, but I may have to go your route.

    For clarity, this is what I mean.

    If I have a table with this information:

    Number Type
    1 Blue
    2 Red
    3 Green

    And I run a query with red and green as the criteria, I would like the query to return the following:

    Number Type
    1 Red
    2 Green

    I just need a row number, not saved or updated anywhere.

  7. #7
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295

  8. #8
    gmontano is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    9
    Maybe I am lookinjg at this all wrong and have gotten away from what I have been trying to do and failing at.

    Let's say I have the following query that gives batting averages:

    Average
    .123
    .521
    .324
    .254

    Is there an expression I can add to the query to rank them in that query:

    Average Rank
    .123 4
    .521 1
    .324 2
    .254 3

    Maybe this is easier to understand.

    I appreciate all of you help. I also see I just got a response, so let me check that.

    Thanks for the response week. I am going to give that a shot right now and see how it works.

  9. #9
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    create a make table query that will store the data in decending order by the batting average. then go into the design view of the new table, and add an autonumber field.

  10. #10
    gmontano is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    9
    Quote Originally Posted by TheShabz View Post
    create a make table query that will store the data in decending order by the batting average. then go into the design view of the new table, and add an autonumber field.
    The end result of the query will be in a multi user enviroment, so I can not take this approach, even with deleting the table.

    I think I may just have to look at it from a different angle.

    I just thought it would be easy to add in a sequencial numbered column that starts with 1 for the first record that returns in a query based on the criteria and sorting of the query, and continues down sequentially for all the records that the query returns.

    I see by searching the internet, it is not easy at all.

  11. #11
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    I tried the query below and it worked slowly but can get the right result, the only thing is you need a unique key column.

    SELECT DCount("*","yourTable","uniqueKeyfield<='" & uniqueKeyfield & "'") AS seq, yourtable.* FROM [yourTable]

  12. #12
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Quote Originally Posted by gmontano View Post
    Thank you both for your suggestions.

    A group of 5 people who know less about access than I do will need to use it.

    In the form, they are able to select a type of record, a date range for the record, and what I was hoping, the number of records they want to retrieve, they may just need to pull 1.

    Are you suggesting each time they go in and change a statement?

    I was just trying to make an easy form for them to use.

    I am pretty sure I could do it if I could just figure out how to get a list in a 1 - whatever amount of records come back on the query depending on the criteria.

    I may misundertood you, but I can not have them going in changing anything other than options on a form.
    you can change the query in you code, not by hand.

  13. #13
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Following query runs much faster than previous one:

    SELECT [b].RES_CODE, (select count(*) from [country treaty table] as a where a.res_code<=b.res_code)
    FROM [Country Treaty Table] as b

  14. #14
    gmontano is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    9
    Great.

    I will try it now.

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

Similar Threads

  1. number of days query
    By osuprog in forum Queries
    Replies: 15
    Last Post: 09-17-2010, 01:15 PM
  2. Query Looking for Date as Number
    By Davenumber40 in forum Queries
    Replies: 2
    Last Post: 08-02-2010, 06:18 AM
  3. The number of records in a query
    By Persist in forum Programming
    Replies: 3
    Last Post: 07-13-2010, 06:23 AM
  4. Auto Number in Query
    By Alex Motilal in forum Programming
    Replies: 1
    Last Post: 09-21-2009, 07:17 AM
  5. query for data type number
    By oracle in forum Queries
    Replies: 1
    Last Post: 12-14-2005, 03:35 PM

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