Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2010
    Posts
    4

    Unanswered: Replace Field Offset

    Hi,
    In Access, using VBA or a Query, I would like to replace some fields. I have a table called 'Replacements' with 2 columns, column 1 with a name and column 2 a corresponding classification.
    What I would like to do is search another table called 'Main Data' which has over 1 million records. In that table, I have the names in the 2nd column (this is the column I would like to search) and the corresponding classification in the 13th column.
    Is it possible to search Column 2 in 'Main Data' for the names in column 1 of the 'Replacements' table and replace the classification field in the 13th column of the 'Main Data' table withe the classification in column 2 of the 'Replacements' table.
    I'm not sure if this can be done straight forward using a Query of some sort, or using VBA. Any direction would be greatly appreciated!
    Thanks
    Simon

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT [Main Data].[names], [Main Data].[13th column] AS OLD_CLASSIFICATION, [Replacements].[column 2] AS NEW_CLASSIFICATION
    FROM Replacements INNER JOIN [Main Data] ON Replacements.[names] = [Main Data].[names]
    Change the names where they are wrong. Run this. You want what is in the second column replaced with what is in the third column - correct?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2010
    Posts
    4
    That's great thanks!
    On top of this, am I able to replace the new classifications over the top of the old one's in the 'Main Data' table?
    Thanks for your help!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - just change this to an update function and update the value in the main data table with your replacements table.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2010
    Posts
    4
    Sorry for being a bit of an amateur with this but how do you run an update query? Do you replace SELECT with UPDATE? Thanks

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Have a little nose here and post what you come up with:
    ewbi.develops: Access/JET SQL UPDATEs
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2010
    Posts
    4
    Thanks, I have tried this but can not fathom why it is not working. I have renamed 'Main Data' to 'Main_Data', 'Name' to 'Ticker' and 'Classifications' to Class3'. Do you know what is wrong with it?

    UPDATE Main_Data
    SET Main_Data.Class3 = Replacements.Class3
    FROM Replacements INNER JOIN Main_Data ON Main_Data.Ticker = Replacements.Ticker
    WHERE Main_Data.Ticker = Replacements.Ticker

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah yes - there are two examples on that web page. The first is T-SQL (not valid syntax for your needs). You want to model your statement on the second.
    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
  •