Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 3 of 3

Thread: Vlookup In Access

  1. #1
    Join Date
    2nd April 2006

    Vlookup In Access


    How can you do a vlookup in a query in Access? In the attached database, I have two tables. The first table is labelled "Data", the second table is labelled "Change Project Numbers". I'd like to do a query that will list all the data in the "Data" table, but change the project number to the "revised" project number from the "Change Project Numbers" table by using a vlookup. Then, if possible, I'd like to sum the data for each (revised) project number. I did this using two queries, shown below, but it would be great if it could be done in one query using vlookup (or some other way). Thanks!

    Table1 (Data):
    Project Number Qty Dollars
    D-12123-08 5.00 10.00
    D-12123-08 34.00 26.00
    D-12123-08 12.00 560.00
    D-12456-76 23.00 1,000.00
    D-12456-76 12.00 3,444.00
    D-12789-78 34.00 6,000.00
    D-12789-78 10.00 4,000.00

    Table2 (Change Project Numbers):
    Orig Project Number Revised Project Number
    D-12789-78 D-12456-76


    Project Number Rev Proj Num Qty Dollars
    D-12123-08 D-12123-08 5.00 10.00
    D-12123-08 D-12123-08 34.00 26.00
    D-12123-08 D-12123-08 12.00 560.00
    D-12456-76 D-12456-76 23.00 1,000.00
    D-12456-76 D-12456-76 12.00 3,444.00
    D-12789-78 D-12456-76 34.00 6,000.00
    D-12789-78 D-12456-76 10.00 4,000.00

    Rev Proj Num SumOfQty SumOfDollars
    D-12123-08 51 596
    D-12456-76 79 14444

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    14th January 2005

    Re: Vlookup In Access

    One query should be possible.

    Try to create a new query, add the first two tables, join the two tables on the old progect code and then drag only the final fields )new code, qty, value) that you want in to the query. Switch to summary view and group by the new code and sum the other two fields.


    Excel Video Tutorials / Excel Dashboards Reports

  3. #3
    Join Date
    19th October 2006

    Re: Vlookup In Access

    Here's the MS Access SQL:

    Sum(tblData.Qty) AS SumOfQty,
    Sum(tblData.Dollars) AS SumOfDollars
    INNER JOIN tblRevisedNumbers
    ON tblData.Project_Number = tblRevisedNumbers.Orig_Project_Number
    GROUP BY tblRevisedNumbers.Revised_Project_Number;

    The result is:
    Revised_Project_Number SumOfQty SumOfDollars
    D-12456-76 44 10000

    I hope that the Table names are self explanatory.


    Last edited by GeorgS; November 13th, 2006 at 10:26. Reason: Took out the Code markup as it doesn't applhy to SQL

    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Vlookup Based On Results From A Vlookup
    By pzarate in forum Excel General
    Replies: 1
    Last Post: June 9th, 2007, 03:49
  2. Access: Forms vlookup function
    By johno in forum Excel and/or Access Help
    Replies: 2
    Last Post: January 27th, 2004, 21:03


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts