1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Updating/Changing Sequential Field in Access

Discussion in 'Business Applications' started by bluegummi, Jul 11, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. bluegummi

    bluegummi Thread Starter

    Joined:
    Jul 11, 2008
    Messages:
    10
    I've got an Access (2003) table containing records of manufacturing process to create a part. The purpose of the table is to create a report that serves as ordered instructions for making a certain part. The records must be sorted by the field "step" which is just a number field.

    The problem I'm running into is whenever a new step is added or the order of the steps needs to change, the step numbering is thrown off and must be updated. For example, if someone decides that step 30 really ought to come after step 75, then step 31 becomes 30, 32 becomes 31 . . . and so on (with other variations depending on whether its just switching steps around or adding a new one). How can I update the step numbering when new records are added or existing records are rearranged?

    I'm pretty sure Visual Basic will be needed, but if not that would be great . . . since I'm not very familiar with Visual Basic. Anything, even a point in the right direction would be greatly appreciated. Thanks!
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Process Sheets, that is quite original usage of a database. :)
    What would you want to do ideally, renumber the whole set of op numbers or arrange to insert a number between two others to keep the sequence?
     
  3. bluegummi

    bluegummi Thread Starter

    Joined:
    Jul 11, 2008
    Messages:
    10
    I had initially thought the ideal would be to renumber the whole set, but whatever gets the job done works for me!
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, what designates a set, the part number?
    Are the part numbers and Operations in the same table?
    Can you provide a zipped copy of the database?
     
  5. bluegummi

    bluegummi Thread Starter

    Joined:
    Jul 11, 2008
    Messages:
    10
    I wish I could send you a copy of the database, but it contains proprietary info from the company. Sorry.

    The database is very simple right now, just started creating it. I'm hoping to get this updating part of the system working before I add too much more data.

    There's only one table with records of process steps in order. The database won't be used to store part info, just these process steps. The table has fields: ID, position, stepNumber, stepDescription, time, and a few other metrics. A set is defined as all of the steps for one position (I think there are 6 positions with about 70 steps each).
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I do not need the "proprietary Data", just a copy of the structure, ie take a copy of the database and then delete all of the records, Compact & Repair it to reduce it's size and then Zip it.
    Is the database going on a Network Server?
    If so will it be "Secured" using the "Security Wizard" to create login Ids and Passwords and control who can do what?
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    From your description the Step data should be in a seperate table from the Positions and linked via the PositionID
     
  8. bluegummi

    bluegummi Thread Starter

    Joined:
    Jul 11, 2008
    Messages:
    10
    Sorry for the delay. I have attached a zipped copy of the database. It won't be connected to a server. Thanks in advance for you help!
     

    Attached Files:

  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    bluegummi, before I look at re-ordering if that is really required would you please look at this version of your Database.
    This is how I think That the Tables should be laid out and Related (See Relationship).
    One Assembly, For many Positions, One position for many Steps.
     

    Attached Files:

  10. bluegummi

    bluegummi Thread Starter

    Joined:
    Jul 11, 2008
    Messages:
    10
    Thanks for the relationships help. I'm still fairly inexperienced with Access, so I appreciate any advice I can get.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Did you look at the Query?
    Do you still want to renumber the Steps?
    If so what Step size do you want. steps of 10?
     
  12. bluegummi

    bluegummi Thread Starter

    Joined:
    Jul 11, 2008
    Messages:
    10
    I'm not sure if we're completely understanding each other. By step I didn't mean gap. It is more of a reference to one step in a step-by-step-process (ie. Step1-Load the part, Step2-Drill a hole in the part, Step3-Polish the part, Step4-Clean the part . . .), where the "Load the part" is the step description (instructions for a mechanic).

    I probably didn't explain very well before. I want to create a report for the mechanics that displays the steps of the process in order (1, 2, 3, 4, . . . 69, 70). Someone in engineering might decide later to reorder the steps of the process differently, and this is where I need help. Being able to change the order of the steps, and then renumber them properly.
     
  13. bluegummi

    bluegummi Thread Starter

    Joined:
    Jul 11, 2008
    Messages:
    10
    Thanks for your help, sorry I haven't been very clear. I have to go, but if you figure anything out let me know. Post it and I'll check it Monday. Thanks again!
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    bluegummi, here it is, I have created a special form for the re-ordering process.
    You can select the Assembly, the position for that assembly (only one position has steps at the moment) and then select the step to be re-ordered. Enter a value in the "Required Step position" field and click the "Re-order this step".
    In the Steps Table I have identified (and tested) 2 steps, step 3 which should be step 2 and step 4 which should be step 6.
    If the user doesn't enter a value or enters 0 there is a warning message and no action is taken.
    If the user enters a value greater than the last step the VBA uses the last step.

    With regards to "Step Size" in my previous post, I worked for 33 years in the Automotive Industry in Quality Engineering. The company always used Steps of 10 in their Instruction sheets (they called them operation sheets).
    The benifits of this are two fold, 1 you can insert up to 9 new sheets (operations) between 2 current operation numbers and 2 when it comes to ISO 9002 "Document Control" it means that only 1 sheet has to be re-issued if a change is made.
    Whereas with your renumbering system either the whole package or at least all affected sheets have to be re-issued.
    Will you be having document "change" and "Control" data on your Step sheets?

    On another matter, I notice that you have a "Photo" field, do you have the VBA code to view the photos on the Step Form and print it on the Step Sheets?
     

    Attached Files:

  15. bluegummi

    bluegummi Thread Starter

    Joined:
    Jul 11, 2008
    Messages:
    10
    I'm sorry I doubted! What a great way to start a Monday! You definitely went above and beyond the call of duty. The form works beautifully, exactly as it should. And you definitely have an understanding of manufacturing processes. I'm currently waiting for management to decide whether or not there will be control data included in the process sheets. But if they do decide to include it, I will be sure to take your advice and propose the steps of 10 or so. Great idea! Thank you!

    As to the photos . . . right now, I'm just converting the jpegs to bitmaps which seems to be working well enough (although it would be nice to eliminate the conversion process if possible).
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/729517

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice