Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Vlookup In Access

  1. #1
    Join Date
    2nd April 2006
    Posts
    87

    Vlookup In Access

    Hello,

    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

    -----------------------------------------------------------

    Query1:
    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

    Query2:
    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
    Location
    Cyprus
    Posts
    2,252

    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.

    HTH
    Carl

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th October 2006
    Location
    Australia
    Posts
    287

    Re: Vlookup In Access

    Here's the MS Access SQL:

    SELECT
    tblRevisedNumbers.Revised_Project_Number,
    Sum(tblData.Qty) AS SumOfQty,
    Sum(tblData.Dollars) AS SumOfDollars
    FROM
    tblData
    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.

    Regards,

    G.
    Last edited by GeorgS; November 13th, 2006 at 09: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 HELP
    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, 20:03

Bookmarks

Posting Permissions

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