Results 1 to 4 of 4

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

  1. #1
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    60

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

    Click image for larger version. 

Name:	Access_RowNumber.jpg 
Views:	18 
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
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,703
    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 (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

  3. #3
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    60
    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
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,703
    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 (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment

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, 02:35 AM
  2. Replies: 1
    Last Post: 08-21-2015, 01:54 PM
  3. Replies: 2
    Last Post: 07-02-2014, 04:59 PM
  4. Partition with Access SQL
    By b82726272 in forum Queries
    Replies: 1
    Last Post: 04-08-2014, 02:59 AM
  5. Replicating ROW_NUMBER OVER (PARTITION BY...)
    By normanj in forum Queries
    Replies: 2
    Last Post: 11-27-2012, 06: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
  •  
Tech Forums: Microsoft Office Forums