Results 1 to 4 of 4

Adding the value from one table field to each new record created in another table.

  1. #1
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73

    Adding the value from one table field to each new record created in another table.

    Our company manufactures auto parts. Each part has a part number, batch number, supplier code, and even a shipping dock code which is a combination of a letter and a number i.e., N1, S2, they are the names of our docks. All numbers assigned to parts are static with the exception of this shipping dock code, it changes for many parts monthly. For example parts that were shipped from N1 dock for one period may be shipped from S2 dock in another period. We call this processes Dock Code Change. It happens every month on 50 to 100 parts. I've been working on a database for a few months that will assist in managing the Dock Code Change process. I receive the Dock Code Change information from an excel spreadsheet where I import into a table in my database.



    I'm stuck on a few areas in developing the database but most urgent is the ability to add a value to each new record that is imported. For example, we have 12 periods and I want to add a field to the import table called, "CurrentPeriod". This information is not on the import spreadsheet but I want to add the current period to each new record. The current period is stored in another table but some how I need to stamp each new record with that value. I think the way to do this is through an Update query. Can anyone offer any suggestions? Thanks.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,645
    Your guess is correct. However, it's not possible to tell you much in the way of how to do it without more info. You need a specific parameter that you can use as criteria to tell Access which rows to update with what, try creating a select query and turn that into an Update query. You will probably have to join your target table and the linked spreadsheet, unless you have this raw data in another table. Test anything you create on copies of tables.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Thanks, I created an update query called "UPDATE_IMPORT", then I created a command button adding the following code: DoCmd.OpenQuery "UPDATE_IMPORT", acViewNormal, acAdd. Each imported record now shows the current period in the correct field. Thanks again.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,645
    Welcome! Please mark your thread as solved if you have not done so already.

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

Similar Threads

  1. Adding specific text in table field record
    By nycman in forum Programming
    Replies: 6
    Last Post: 09-07-2015, 05:52 PM
  2. Replies: 8
    Last Post: 12-29-2014, 08:13 AM
  3. Replies: 7
    Last Post: 04-17-2012, 11:53 AM
  4. Replies: 3
    Last Post: 11-07-2011, 10:41 AM
  5. Replies: 1
    Last Post: 09-21-2010, 09:49 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