Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2009
    Location
    Salem, AR
    Posts
    4

    Unanswered: Increment field in query

    I have a query that lists work orders to be generated. What I need to do is increment the work order number sequentially starting with the last work order number. However, the table with the work order number is not in the query itself. Is there a simple way to do this that I am just overlooking?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Code:
    SELECT Col_1, Col_2, ..., (SELECT top 1 Col_X FROM Table_2 ORDER BY Table_2.X DESC;) AS x
    FROM Table_1;
    Have a nice day!

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    if you have 5 work orders, and work order 3 is deleted, do you care that work orders 4 and 5 will from that point on be known as work orders 3 and 4 when the query is next run?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2009
    Location
    Salem, AR
    Posts
    4
    They shouldn't change. Work Order 5 will always be 5. I don't want to count the records in the table, I want to take the last Work Order number and increment that. So it should not matter if any work orders are deleted. The work order number will not change.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm not understanding. You mention another table where your work orders live. What does that have to do with this query?

    What's in this mysterious other table?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by henley12
    They shouldn't change. Work Order 5 will always be 5. I don't want to count the records in the table, I want to take the last Work Order number and increment that. So it should not matter if any work orders are deleted. The work order number will not change.
    In that case, why not just use autonumbers? (not knowing what these are is a valid reason!)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2009
    Location
    Salem, AR
    Posts
    4
    The autonumber was the problem in the first place. Here is the long story:

    I have a maintenance database that we use to track work orders. The main tables are the work order table, the equipment table and the job table. The equipment table is used to create scheduled jobs which are turned into work orders. When a scheduled job is created on a piece of equipment, I can push a button and create a work order from that. That works just fine. I also have a form that reads the equipment schedule table to see what work orders need to be scheduled for a given date range. These particular work orders are created with a series of update queries. The append query used to actually create the work orders worked fine when the work order number was the autonumber field of the table. However, a test malfunction in the schedule generator created over 750,000 invalid work orders, which I had to delete from the table. Now the work order number has a gap of over 750,000 because of the autonumber field. I have changed the work order number field to be just a numeric field which I increment based on the last work order number. This works fine on the main work order form, as well as the work order generator. The append query, however, is where I am having the problem generating the new work order numbers in sequence.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Oh, well that's not all that big of an issue.

    The first issue here is you actually care about what number is on the work order. I would caution you against that. It should not matter that you have a 750k gap between work order numbers.

    That said, you could do a one-time fix by re-keying your work orders.

    Microsoft has some words on that here:

    How to reset an AutoNumber field value in Access
    *of particular import to you would be "Reset an AutoNumber field in a table with referenced tables"

    It's basically a smoke-and-mirrors approach that involves creating a shiny new autonumber field and updating any referenced records in other tables with the newly created field. Basically...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Agreed with Teddy.

    Also look at how your responses contradict each other - in one case you want the numbers to be fixed, in another you want the numbers to change. The only difference is in one scenario one row was deleted, in the other 750,000. Your solution can either respect deletes, or not respect them. If you want to respect them conditionally then you need to use a one time hack like Teddy has suggested.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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