Results 1 to 4 of 4
  1. #1
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67

    How to use Row_Number() Over Partition By ... in Access

    Click image for larger version. 

Name:	Access_RowNumber.jpg 
Views:	51 
Size:	44.0 KB 
ID:	36359



    I've searched for this many times and I am very grateful to all the posts on variants of VBA script that call an incrementing function to get row_numbering into a select query.
    The goal is add a row-counter for data that can be grouped e.g.
    (not part of query) Name Car Car_ID
    1 Andrea Mustang 1
    2 Andrea Shelby Cobra 2
    3 Betty Jeep 1
    4 Betty Rav4 2
    5 Claire BMW 1

    This is the code that I used which still has some of the problems in other scripts (namely in datasheet view the first row constantly increments and it can only be used once at a time).

    In a new module

    Code:
    Option Compare Database
    Option Explicit
    Private ReferenceRowID As Long
    Private ReferenceGroupID As Variant
    Private lngRowNumber As Long
     
    Public Function Row_Number(varChangeField As Variant) As Long
    If varChangeField = ReferenceGroupID Then 'the group by field is the same as previous, increment the row counter
     lngRowNumber = lngRowNumber + 1
    Else
     ReferenceGroupID = varChangeField 'the group by field has changed, reset the counter to 1
     lngRowNumber = 1
    End If
    Row_Number = lngRowNumber
    End Function
    The Design View picture is attached


    The SQL is:
    SELECT tbl_Cars.fldName, tbl_Cars.fldCar, Row_Number([fldName]) AS CarID FROM tbl_Cars GROUP BY tbl_Cars.fldName, tbl_Cars.fldCar ORDER BY tbl_Cars.fldName, tbl_Cars.fldCar;

    Hope this helps.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,946
    I use a different approach based on a Serialize function.
    For details, see http://www.mendipdatasystems.co.uk/r...ies/4594424063
    So far, I've found no disadvantages with that method.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    Is there a way to delete this? I've been using the code I wrote above and it has too many short-comings to be worth posting. I think the recordset method is a better way to.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,946
    I don't understand your last comment
    However once a thread has any answers it should not normally be deleted though a mod could move it.
    The topic may be useful to someone else, if not to you.

    if you haven't already done so, I would recommend you look at the link I supplied...or was that what you meant by the recordset method?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. OVER PARTITION type query, ranking, select top 10
    By kagoodwin13 in forum Queries
    Replies: 2
    Last Post: 02-10-2016, 03:35 AM
  2. Replies: 1
    Last Post: 08-21-2015, 02:54 PM
  3. Replies: 2
    Last Post: 07-02-2014, 05:59 PM
  4. Partition with Access SQL
    By b82726272 in forum Queries
    Replies: 1
    Last Post: 04-08-2014, 03:59 AM
  5. Replicating ROW_NUMBER OVER (PARTITION BY...)
    By normanj in forum Queries
    Replies: 2
    Last Post: 11-27-2012, 07:36 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
  •  
Other Forums: Microsoft Office Forums