Results 1 to 5 of 5
  1. #1
    beat is offline Novice
    Windows Vista Access 2003
    Join Date
    Dec 2011
    Posts
    9

    Arrow Apply Dlookup "automatically" on an entire column

    Hello everybody. I am currently doing internship in a supply chain company. My problem is the following:

    I two tables, one with sales order for january from october and one with the the sales order for january from november. Now I want Access to put together one query, to compare the order changes over time -
    e.g. in october we forecasted order for january of product x, quantity=1000, in november we only forecasted order for januray quantity=500. -->decrease 50%

    I d like to have a query as following

    ID ArticleNumer QuantityJan(Oct) QuantityJan(Nov)


    unfortunately not all products from the november list are included in the october list. I only want to include those products who can be found in both periods.



    I dunt now how i can apply a certain function to a whole column similiar to vlookup in excel.

    Can anybody help me? Please.

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Can you tell me the field names in your two tables?

    Is there one common field in the two tables [ID?? ArticleNumber?]?

  3. #3
    beat is offline Novice
    Windows Vista Access 2003
    Join Date
    Dec 2011
    Posts
    9
    Thanks for your answer. I have the following columns

    Table 1 (Data from October forecast for the months Jan till May):
    Article Number, 012012, 022012, 032012,...,052012
    CA2342, 5, 10, 5, 21
    ., ., ., ., .
    ., ., ., ., .
    Table 2(Data from November forecast for the months Jan til May):
    Article Number, 012012, 022012, 032012,...,052012
    CA2342, 6, 8, 10, 20
    . . . . .
    . . . . .

    The columns article is the contains the common fields. The new table (query) should include all the article which have been ordred in october as well as in november. So Access should match the article numbers from november with the one in october.

    If any further question just let me know.

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Sorry for the delay - I have been ill & only just got back to my computer.

    Create two queries something like this:
    QryTotals_1:
    Code:
     
    SELECT Table1.ArticleNumber, Nz([012012],0)+Nz([022012],0)+Nz([032012],0) AS TotalOct
    FROM Table1;
    QryTotals_2:
    Code:
     
    SELECT Table2.ArticleNumber, Nz([012012],0)+Nz([022012],0)+Nz([032012],0) AS TotalNov
    FROM Table2;
    Then create a third query using the above two as your source in Query Design View:
    Join The ArticleNumber fields in the two queries [I'm assuming the Article Numbers will be the same from Oct to Nov to Dec . . .].
    Drag ArticleNumber from Table1, Total Oct and TotalNov into your query.

    Under TotalOct and TotalNov in the Criteria field put > 0.
    Both > 0 should be on the same Criteria Row.

    In SQL View, this should look something like this:
    Code:
     
    SELECT QryTotals_1.ArticleNumber, QryTotals_1.TotalOct, QryTotals_2.TotalNov
    FROM QryTotals_1 INNER JOIN QryTotals_2 ON QryTotals_1.ArticleNumber = QryTotals_2.ArticleNumber
    WHERE (((QryTotals_1.TotalOct)>0) AND ((QryTotals_2.TotalNov)>0));
    When you run your third query, you will only see totals for those ArticlesNumbers that have Quantities > 0 in both months.

    I hope this helps!!

  5. #5
    beat is offline Novice
    Windows Vista Access 2003
    Join Date
    Dec 2011
    Posts
    9
    hi robeen.

    thanks for your effort. i could solve the problem. acctually i only need to define the suitable relationship between the two tables in the query then it worked perfectly. and i used your nz formula to fill the blank cells with 0.

    thanks again. cheers.

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

Similar Threads

  1. Replies: 6
    Last Post: 07-25-2012, 06:42 AM
  2. "Automatically" populate UserID & Password for ODBC
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 06-02-2011, 11:18 AM
  3. ComboBox "Select" and "DLookUp"
    By witooldas in forum Forms
    Replies: 0
    Last Post: 03-23-2011, 03:31 AM
  4. Replies: 3
    Last Post: 03-21-2011, 05:29 PM
  5. Creating "Edit Record" link in table column
    By joshearl in forum Forms
    Replies: 1
    Last Post: 12-25-2009, 11:17 AM

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