Apply Dlookup "automatically" on an entire column

[beat]

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.

[Robeen]

Can you tell me the field names in your two tables?

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

[beat]

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.

[Robeen]

Sorry for the delay - I have been ill & only just got back to my computer.

Create two queries something like this:
QryTotals_1:

 
SELECT Table1.ArticleNumber, Nz([012012],0)+Nz([022012],0)+Nz([032012],0) AS TotalOct
FROM Table1;

QryTotals_2:

 
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:

 
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!!

[beat]

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.