Results 1 to 5 of 5
  1. #1
    pabbydf is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    2

    Vlookup Function in MS Access 2010

    Hi,



    Need your advice in updating my database using a function like the vlookup in excel. My database consist of 1 Million + rows and one of its column (Company Name) updates more often. What I previously use in Excel is vlookup in updating my data but now I migrated my data into access and now having a problem in the naming convention.

    Table 1 (In excel, I use Vlookup,$A1,Table2 Range,2,0, then copy paste the formula on the affected rows)
    Old Name Production New Name
    Company1 1 CompanyA
    Company2 2 CompanyB
    Company3 3 CompanyC
    Company4 4 CompanyD
    Company5 5 CompanyE



    Table 2
    Old Name New Name
    Company1 CompanyA
    Company2 CompanyB
    Company3 CompanyC
    Company4 CompanyD
    Company5 CompanyE

    Maybe you can suggest a workaround in MS access as I need to update my database on a weekly basis considering a the big no. of rows that I need to update.

    Thanks,

    Pabby

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why do you save company name to both tables? Are you using company name as unique identifier PK/FK?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    pabbydf is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    2
    I do it to update the old data which comes from another database where the company names had been changes. Usually I use the 2012 & 2013 data of Company A to compare with my new data of now Company B.

    Hope you can help me on this.

    Thanks.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Columns may be the most import thing in an RDBMS. Not aware of a built in function to search ALL fields. Methods are usually specific to a field/column. Methods used to search fields can search through the many rows of a single column.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Sounds like you need to run an UPDATE sql action.

    The field update could be done with a DLookup expression

    or

    Join the tables on the common Old Name fields. The update expression will be like: =[OldTable].[OldName]
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Access Data (VLOOKUP function?)
    By rmoreno in forum Queries
    Replies: 13
    Last Post: 06-05-2013, 03:43 PM
  2. VLookup function in Access?
    By mveda2004 in forum Queries
    Replies: 2
    Last Post: 02-25-2013, 09:42 PM
  3. VLookup? in Access 2010
    By Lazor78 in forum Database Design
    Replies: 1
    Last Post: 07-25-2012, 03:34 AM
  4. Simple VLookup function in Access
    By acharyagautam in forum Queries
    Replies: 12
    Last Post: 02-07-2012, 12:18 PM
  5. Vlookup function in access
    By rici7 in forum Forms
    Replies: 1
    Last Post: 10-16-2010, 04:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums