Results 1 to 3 of 3

Any way to get query to increment a value in a query

  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    331

    Any way to get query to increment a value in a query


    I have a query that has a field called line number. I pull some of those lines out based on my query criteria and then output it to a new file. Now I need to update that line number with updated lines from 1 to the end of the file.

    I do prep the data and send it to a temp table so If I can run some type of update query on that table that would also work. I have googled and cant seem to find a way to do this in a query.

    thanks for any help,

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,428
    If your temp table (table3) contains an autonumber ID field then maybe
    Code:
    SELECT (Select COUNT(*) FROM table3 AS A where A.ID <=b.ID) AS Expr1, B.ID
    FROM table3 AS B ORDER BY B.ID;
    It shouldn't matter what the autonumbers are (assuming you're deleting those records when the increment is done) because you want to make use of the Count and not the actual numbers.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  3. #3
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    178
    When there is no UniqueID Number field then try this: https://www.msaccesstips.com/2010/01...ry-column.html
    http://www.msaccesstips.com (Learn MS-Access Tips and Tricks)
    Learn Advanced MS-Access Programming with examples and VBA.

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

Similar Threads

  1. Increment numbers
    By grkchakri in forum Access
    Replies: 4
    Last Post: 07-22-2018, 04:07 AM
  2. Append Query and Increment Number
    By burrina in forum Queries
    Replies: 1
    Last Post: 02-02-2013, 12:28 AM
  3. Increment Field Value
    By Malseun in forum Access
    Replies: 9
    Last Post: 02-03-2012, 04:55 AM
  4. How to increment values through a query
    By doci4a in forum Queries
    Replies: 3
    Last Post: 03-17-2011, 08:25 AM
  5. Replies: 5
    Last Post: 11-12-2010, 12:10 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
  •  
Tech Forums: Microsoft Office Forums