Hi Richard,

I do apologies and will try to explain myself with the two tables below. I have a forecast table and a History table. What I need is to search for
records in the History table based on (RSP and Media)...and only copy over the TotalSales records into Forecast Table and insert this under the
field heading (TotalEstimate)...


Forecast Table

ItemCode ItemDescription CustGroup SRP Media TotalEstimate
93317 COMFORT FAB SFT SKY BLUE 400ML A 2.99 CAT
93317 COMFORT FAB SFT SKY BLUE 400ML A 2.99 Not Adv
93325 COMFORT FAB SFT S/SHN DAY400ML B 2.99 CAT
93325 COMFORT FAB SFT S/SHN DAY400ML A 2.99 Not Adv
93333 COMFORT FAB SFT ROSY BLSH400ML B 2.99 CAT
93333 COMFORT FAB SFT ROSY BLSH400ML A 2.99 Not Adv
363403 COMFORT FAB SFT PURE WHT 400ML B 2.99 CAT
363403 COMFORT FAB SFT PURE WHT 400ML A 2.99 Not Adv



Week ItemNumber Group YearAdded ItemDescription SRP TotalSales Media
30 93317 A 2012 COMFORT FAB SFT SKY BLUE 400ML 2.99 56 CAT
30 93317 A 2012 COMFORT FAB SFT SKY BLUE 400ML 2.99 36 Not Adv
23 93325 B 2012 COMFORT FAB SFT S/SHN DAY400ML 2.99 56 CAT
23 93325 A 2012 COMFORT FAB SFT S/SHN DAY400ML 2.99 35 Not Adv
23 93333 B 2011 COMFORT FAB SFT ROSY BLSH400ML 2.99 62 CAT
23 93333 A 2011 COMFORT FAB SFT ROSY BLSH400ML 2.99 30 Not Adv
45 363403 B 2011 COMFORT FAB SFT PURE WHT 400ML 2.99 50 CAT
45 363403 A 2012 COMFORT FAB SFT PURE WHT 400ML 2.99 40 Not Adv



Again you expert advice is much appreciated.


Regards

Hamish

---

Reply from Richard Rost:

This just looks like a complex DLOOKUP statement. In a query field, you could say something like:

TotSales: DLOOKUP("TotalSales","HistoryT","SRP='" & SRP & "' AND Media='" & Media & "'")

Then if you want to make that permanent you could just feed that query into a MakeTable query.