Update Query from 2 tables (1 Viewer)

[Nevsky78]

Dear All,

I am trying to update 1 table ("tblForecastFeed" from 2 'feeder' tables ("tblCustomerList" and "CLP Forecast Feed") but I think I'm doing something very wrong. This is the code I currently have:

UPDATE (tblCustomerList INNER JOIN tblForecastData ON tblCustomerList.Customer = tblForecastData.Customer) INNER JOIN [CLP Forecast Feed] ON tblForecastData.[Item Code] = [CLP Forecast Feed].[Item Code] SET tblForecastData.[Item Code] = [tblForecastData].[Item Code], tblCustomerList.Customer = [tblForecastData].[Customer];

The "CLP Forecast Feed" is a linked Excel table. I need instances of each "Item Code" against each "Customer" in the "tblForecastFeed", and the Update Query would update the "tblForecastFeed" with any changes/additions in the "CLP Forecast Feed" and the "tblCustomerList".

Does anyone know why this may not be working properly? I had a Update Warning box saying I was updating a couple of million rows! I guess it's something to do with updating one table from two tables, but I don't know how to get round that!

Many Thanks,
Nick

 

[jdraw]

When I reformat your query I get the following

UPDATE (tblCustomerList INNER JOIN tblForecastData ON 
tblCustomerList.Customer = tblForecastData.Customer)
 INNER JOIN [CLP Forecast Feed] ON 
tblForecastData.[Item Code] = [CLP Forecast Feed].[Item Code]
 SET tblForecastData.[Item Code] = [tblForecastData].[Item Code], 

tblCustomerList.Customer = [tblForecastData].[Customer];

To the best of my knowledge you can only update 1 table in an UPDATE
query.

Seems you are trying to set the field equal to itself???? which, if you have several records, is why you are attempting to update all records

SET tblForecastData.[Item Code] = [tblForecastData].[Item Code],

and trying to update a field in the second table???

tblCustomerList.Customer = [tblForecastData].[Customer]

 

[Nevsky78]

Hi (again!) jdraw.

I guess I have 2 'base' tables - one containing item data (which is a linked Excel file) and one containing customer data.

Each customer can forecast predicted sales against each item.

I want each 'customer' to have a full list of items. There would therefore either be a 'master' table with all Items, all Customers and all customer specific data, or multiple tables with customer specific data.

Item numbers in the linked Item Table can actually change, so I need to cascade any changes throughout each customer specific data.

I'm sure there might be a better way of constructing the database but I can't think of one! So any advice would be very gratefully received.

Nick

 

[Nevsky78]

I worked out how to do it.

I ran an initial Make Table on the linked Excel table including a new Item ID field.

I then ran an intial make table to combine every instance of Items and every instance of Customers. On the new table I added Qtr1,Qtr2,Qtr3,Qtr4 fields.

Then there are three Update Queries - 1 for Items, 1 for Customers and 1 for the 'Master Table'. They all have established relationships.

Nick