Results 1 to 9 of 9

Table set up and replacement

  1. #1
    rdhunt is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Location
    Memphis
    Posts
    6

    Table set up and replacement

    I have two tables in Access, and need help;
    I am trying to replace the values in a column in one table, with the values from another column in a different table:
    for instance:
    I have one table named "2016Current" which displays the employee number and current salary.
    I have a second table named "VacationTable" in which I need to replace it's "current Salary" with the "current Salary" from the "2016Current" table.


    the Employee number is the unique identifier in both tables. when I go to link them via relationship, it doesn't give me an option to overwrite the "Vacation Table" Current Salary with the data from "2016Current"
    (This is where I wish I could do a VLOOKUP)
    What am I doing wrong? It is changing some of the salaries, but not all of them; in addition, how do I leave a field in this column blank, if the employee doesn't have a current salary because they are no longer employed?

  2. #2
    JoeM is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,887
    It sounds like what you are doing is just a Select query. A Select query just returns data, it does not update any tables behind the scenes.
    If you want to update records in one table with records from another, you would use an Update Query.
    An Update Query will only update records it find matches for (and you can add further criteria to limit it if you want).

    See:
    https://support.office.com/en-us/art...5-f6769d2bb717
    https://www.techonthenet.com/access/...date2_2007.php
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    rdhunt is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Location
    Memphis
    Posts
    6
    I used this and it only shows 11 changes, when I can clearly look at both tables and see there are many many more than that that are different.....the unique identifier is a general number (employee id) and both are formatted the same way; why isnt it populating all of them that are the same?

  4. #4
    JoeM is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,887
    Open your Update Query in Query Builder, change to SQL View, and Copy and Paste the code here so we can analyze it.
    If you could post a small sample of the data from each table, that might also be useful too (just make sure that you are using dummy data).
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  5. #5
    rdhunt is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Location
    Memphis
    Posts
    6
    I want the 2016 Gross to put the Current Gross amount in the "Current Gross" field in TBL Vaca:

    UPDATE 2016CGROSS INNER JOIN TblVacation ON [2016CGROSS].[Earn Amount] = TblVacation.C_Gross SET TblVacation.C_Gross = [2016CGROSS].[Earn Amount]
    WHERE (((TblVacation.C_Gross)=[TblVacation].[EeClock]));


    2016Gross
    Clock # Name Current Gross
    6810 Doe, John 43,864.00
    8314 Rook, Casey 22,465.00



    TBL Vaca
    ID EEClock Employee Name Current Gross Previous Gross
    1 6810 Doe, John XX 32,463.20
    2 8314 Rook, Casey XX 14,443.00

  6. #6
    JoeM is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    2,887
    INNER JOIN TblVacation ON [2016CGROSS].[Earn Amount] = TblVacation.C_Gross
    Why are you joining on dollar amount and not employee_id???

    That would match up records where the dollar amounts in each table are the same, and then try to update (which really wouldn't accomplish anything since they are already the same)!
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  7. #7
    rdhunt is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Location
    Memphis
    Posts
    6
    so, joing via the clock number (employee id), but will that replace the CGross field int TBL Vaca?

  8. #8
    rdhunt is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Location
    Memphis
    Posts
    6
    I need to replace the dollar amount with the amount FROM 2016Gross

  9. #9
    rdhunt is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Location
    Memphis
    Posts
    6
    You are a genius and I am too tired. I didnt clear the column C_Gross in order for it to populate the data from the other table. Thank you for the nudge in the right direction!!!

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

Similar Threads

  1. Replacement for Me. in public sub
    By Stephenson in forum Access
    Replies: 2
    Last Post: 09-08-2015, 12:14 PM
  2. Any replacement for the old SetValue
    By toniartist in forum Access
    Replies: 1
    Last Post: 03-17-2012, 07:58 PM
  3. Sendkeys replacement
    By numberguy in forum Programming
    Replies: 6
    Last Post: 07-14-2011, 07:29 AM
  4. Sendkeys replacement
    By numberguy in forum Forms
    Replies: 3
    Last Post: 10-29-2010, 06:20 AM
  5. Replacement in table with VB code
    By miziri in forum Access
    Replies: 2
    Last Post: 06-28-2010, 12:38 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