Results 1 to 13 of 13

Simple VLookup function in Access

  1. #1
    acharyagautam is offline Novice
    Windows Vista Access 2003
    Join Date
    Feb 2012
    Posts
    6

    Simple VLookup function in Access

    Hi I am a newbie to access and would like to know how to write a query to run a simple vlookup functions between these two:
    Tabel1 : has 200,000 rows of data. Has the fields to be looked up- "Serial_Number" and "Ship_Date"

    Table2: has the field :"Serial_No"

    I want table3 to Lookup Serial_No (tabel2) in the Serial_Number in table1 and return the corresponding row value of Ship_Date

    Thanks

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    28,114
    Is Serial_Number unique in Table1? Is Serial_Number a text field in both tables?

    Why not use a query that joins the tables? What is it you are really trying to accomplish?

    DLookup("Ship_Date", "Table1", "Serial_Number='" & [Serial_No] & "'")
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    No function necessary. The query would just include both tables, be joined on the Serial_Number to Serial_No field and a criteria of a combo box to select serial number would display the value.

    A third table is not something you should be even thinking about. Why would you even think of that? The query is actually all you would need.

    In fact, the more I am seeing it, I don't know what Table2 has to do with it anyway as well. What is Table2 and where does it come from?

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    I was slow typing on this one for sure.

  5. #5
    acharyagautam is offline Novice
    Windows Vista Access 2003
    Join Date
    Feb 2012
    Posts
    6
    Hi June7,
    Yes the Serial_Number and the Serial_No both are text and unique. Yes joining the tables together will work. How do i do this?
    I am trying to return the ship date of a particular serial number from the Table1(master data) . Tabe2 contains the 100 or so Serial numbers that need to be matched in Table1 to return the ship date from table1.

  6. #6
    acharyagautam is offline Novice
    Windows Vista Access 2003
    Join Date
    Feb 2012
    Posts
    6
    Thanks Bob,
    Table 2 is actually from Excel. Since i am unable to query from Excel into Access(tabe1), i am importing the data from excel to Access and calling it Table2. If i could directly query from excel, then great.

    Thanks
    Gautam

  7. #7
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    All you would have to do is link to the Excel file and then use that as a table in Access along with the other table.

    Select * From Table1 Inner Join Table2 On Table2.Serial_No = Table1.Serial_Number

    (Not 100% sure of the syntax as I don't hand write queries too often. I use the QBE grid where I can just put both tables in there, drag the link over from one field to the applicable other one in the other table and put my fields I want in the grid below.

  8. #8
    acharyagautam is offline Novice
    Windows Vista Access 2003
    Join Date
    Feb 2012
    Posts
    6
    Thanks Bob. I did this, and it asked me to enter a Serial no and when i copied and pasted a serial no from Table2, it returned all the 200,000 rows from table. I need it to return only the 50 or odd ship dates in table 2.

  9. #9
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by acharyagautam View Post
    Thanks Bob. I did this, and it asked me to enter a Serial no and when i copied and pasted a serial no from Table2, it returned all the 200,000 rows from table. I need it to return only the 50 or odd ship dates in table 2.
    If you just do the select query which I gave, it should only return what is in the dates for those in table 2.

  10. #10
    acharyagautam is offline Novice
    Windows Vista Access 2003
    Join Date
    Feb 2012
    Posts
    6
    Bob,
    I used the
    Select * From Table1 Inner Join Table2 On Table2.Serial_No = Table1.Serial_Number

    It is asking me to enter the "Parameter Value" for Serial No, what should i enter? When i enter a particular serial no from Tabe2, it is giving me 200,000 rows from Table1.

    Thanks
    Gautam

  11. #11
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Okay, I messed up the SQL slightly. I just made a couple of tables so I could use the QBE grid and get it. So, if you gave the actual name of the tables and the actual name of the fields, the SQL should look like this:
    Code:
     
    SELECT Table1.*
    FROM Table1 INNER JOIN Table2 ON Table1.Serial_Number = Table2.Serial_No;

  12. #12
    acharyagautam is offline Novice
    Windows Vista Access 2003
    Join Date
    Feb 2012
    Posts
    6
    Hi Bob,
    Tired the new query. It again asked for the Parameter Value and when i entered a Serial Nuumber, it returned all the 200,000 rows. The actual neames for the tables are Table1 and Table2.

    Thanks
    Gautam

  13. #13
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by acharyagautam View Post
    Hi Bob,
    Tired the new query. It again asked for the Parameter Value and when i entered a Serial Nuumber, it returned all the 200,000 rows. The actual neames for the tables are Table1 and Table2.

    Thanks
    Gautam
    If it is asking for a parameter then you spelled something wrong or didn't do it correctly. It should NOT be asking you for a parameter.

    Post some screenshots (upload them here and not on a public photo sharing site as those are blocked by my workplace) of your query in Design view and your tables showing in the query so we can hopefully spot what you are doing wrong.

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

Similar Threads

  1. Excel VLookup vs Access IIF statement
    By Huddle in forum Queries
    Replies: 9
    Last Post: 02-02-2012, 09:00 AM
  2. Vlookup in Access with SQL
    By jogunjobi in forum Queries
    Replies: 1
    Last Post: 01-30-2012, 05:25 PM
  3. Query to work as a Vlookup function
    By dharmik in forum Queries
    Replies: 21
    Last Post: 01-04-2012, 06:12 AM
  4. working as a vlookup function
    By cleon in forum Queries
    Replies: 3
    Last Post: 12-16-2011, 12:51 PM
  5. Vlookup function in access
    By rici7 in forum Forms
    Replies: 1
    Last Post: 10-16-2010, 03: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
  •  
Tech Forums: Microsoft Office Forums