Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding the record Number (Access 2000)

    I would like to add a field in my query that will give me the row number for each record returned and order it by a date field. I have done this in Oracle SQL, but I can not seem to do it in Access or SQL Server SQL. For example, if the query returns 20 records (10 for one user and 10 for another) I want it to number based 1- 10 for both ordered by date. The script that I use for Oracle used a "partition by" syntax, but it will not work on Access and I can 't find any information online to accomplish this.

    Any help I can get would be great. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding the record Number (Access 2000)

    In a query, this is not easy. In a report, however, it is very easy: group the report by user, put a text box with Control Source =1 in the detail section, and set its Running Sum property to Over Group.

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding the record Number (Access 2000)

    I got that to work. Thanks. Now... Is there a way to do calculations for first 15 then the second 15. I need to figure out an accuracy percentage for the first 15 dates, then the second 15. I have an error flag that indicates yes or no on the report, so basically I need to count how many no's and yes's there are and divide. I was thinking of doing an iif statement, but I am not sure how to start.

    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding the record Number (Access 2000)

    Access is not very good at that kind of thing, since it is not a division that is natural to the data. For the first 15, you might create a Top 15 query: create a query, set the Sort Order as you want, then set its Top Values property to 15 - you can't select 15 from the dropdown list, but you can type it yourself. For the second 15, you would have to create a new query that excludes the first 15 - you could use the Find Unmatched Query Wizard for this - then set its Top Values property to 15.

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Adding the record Number (Access 2000)

    <P ID="edit" class=small>(Edited by MarkD on 02-Dec-03 06:42. Added note, replaced attachment with modified file.)</P>If interested, attached sample DB shows how you can number records in a query by using DCount function, either for all records or for records in a group. Both examples require a unique field to work properly. Example of SQL used (all records):

    <pre>SELECT UserTable.ID AS RecordID, UserTable.User AS UserName, _
    UserTable.Date1 AS [Date], _
    DCount("ID","UserTable","[ID]<" & [RecordID])+1 AS RecordNumber
    FROM UserTable
    ORDER BY UserTable.ID;</pre>


    In this example ID field is unique (primary key in table); must sort by this key for line numbering to work. Second example (grouped by User):

    <pre>SELECT UserTable.User AS UserName, UserTable.Date1 AS [Date], _
    DCount("ID","UserTable","[User]='" & [UserName] & "' _
    And [Date1] <#" & [Date] & "#")+1 AS RecordNumber
    FROM UserTable
    ORDER BY UserTable.User, UserTable.Date1;</pre>


    In this example query sorted by User and Date field; line numbering will work provided dates are unique in each group (I used a function to generate a random date between 1965 and 2003, if interested see the code module in att'd file). If there can be duplicate dates in group will need to modify. In such a case you could ensure both date & time stored in date field, then date/time should be unique in most cases - there are various VBA functions that allow you to extract date portion of date/time field when necessary. Note use of delimiters used in the DCount function, and use of aliases for fields displayed in query results, necessary for this to work. Also note, since DCount is called for every record returned by query, performance may be an issue if you have large number of records, slow pc or network connection, etc.

    This "technique" adapted from following MSKB article:

    MSKB 208714 - ACC2000: How to Create Running Totals in a Query

    As title implies, article concerns creating a running sum total, but same technique can be used for a "running count" using DCount instead of DSum to create "record numbers".

    PS: Revised db attached, omitted function from code module, also added simple report based on 2nd query above that demonstrates use of Group Interval to group records based on RecordNumber field (I used 5 for Interval since only small number of records in test table) - see Report1.

    HTH
    Attached Files Attached Files

Posting Permissions

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