Results 1 to 3 of 3

Replicating ROW_NUMBER OVER (PARTITION BY...)

  1. #1
    normanj is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    1

    Replicating ROW_NUMBER OVER (PARTITION BY...)

    Hi,



    I have a table with similar data to this:

    ID Amount
    1 23.23
    1 219.23
    1 12
    2 4491.2
    2 23441.33
    2 442
    2 9823.23

    What I would like to do this a assign a new Row Number for each group of ID's, so the results should look as follows:

    ID Amount Row
    1 23.23 1
    1 219.23 2
    1 12 3
    2 4491.2 1
    2 23441.33 2
    2 442 3
    2 9823.23 4


    Is this something like possible in MS Access 2003. I have used the ROW_NUMBER OVER (PARTITION BY XXX ORDER BY XX) in DB2 which seems to do the trick...

    Thanks,

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,476
    Textbox in report has a RunningSum property. This can be used to calculate a sequential number over group. Have to set up a GROUP section on the ID field.

    Otherwise, review: http://www.lebans.com/rownumber.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,910
    There is no native support for "ROW_NUMBER OVER (PARTITION BY XXX ORDER BY XX)" in Access as there is in DB2, Oracle and SQL Server....others???

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

Similar Threads

  1. Replies: 4
    Last Post: 08-31-2012, 12:55 PM
  2. Replies: 10
    Last Post: 05-24-2012, 02:44 AM
  3. access replicating itself
    By tintincute in forum Access
    Replies: 3
    Last Post: 09-08-2010, 12:49 PM
  4. Replicated New Tables Not Replicating Data
    By smyers in forum Access
    Replies: 0
    Last Post: 04-30-2010, 01:12 PM
  5. how to translate "row_number"?
    By mfran2002 in forum Queries
    Replies: 0
    Last Post: 09-17-2008, 03:13 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
  •  
Tech Forums: Microsoft Office Forums