Update query - at my wits end

[Db396]

Hi

I have written a select query that works well and gives the correct reults. SQL below:

SELECT tblFactSalesAndStock.Line_Code, tblFactSalesAndStock.Week, tblLoadCoverBand.Cover_Band_Code
FROM tblLoadCoverBand INNER JOIN tblFactSalesAndStock ON (tblLoadCoverBand.Week = tblFactSalesAndStock.Week) AND (tblLoadCoverBand.Line_Code = tblFactSalesAndStock.Line_Code);


When I then change it to an update query it returns the correct number of records but there are no values in the field that needs to be updated.

UPDATE tblLoadCoverBand INNER JOIN tblFactSalesAndStock ON (tblLoadCoverBand.Week = tblFactSalesAndStock.Week) AND (tblLoadCoverBand.Line_Code = tblFactSalesAndStock.Line_Code) SET tblFactSalesAndStock.ClsBrchCovU_Band_Code = [tblLoadCoverBand].[Cover_Band_Code];

I have checked both of the source tables and the field properties are identical.

Am I missing something really obvious????

Thanks
David

 

[pr2-eugin]

You need to Run the Query.. If you 'view', you will not see what you are expecting to see..

 

[Brianwarnock]

Possibly, you did run it (red !) rather than just click the datasheet view?

Brian

Edit Damn that Paul he's too quick, these peskey youngsters.

 

[boblarson]

If you click the Datasheet view from Design View, as mentioned, it won't show the data that will be updated. But if you click the red exclamation point (!), then it will run the query and the values will be updated in the table.

 

[Db396]

You're right - pressed Run now - and it runs perfectly. Thank you.

But how do you check if an update query is safe to run before running it? Why on earth would it not show the correct results in Datasheet View?

Thanks
David

 

[boblarson]

But how do you check if an update query is safe to run before running it?

You can change it to a Select query temporarily to view.

Why on earth would it not show the correct results in Datasheet View?

Possibly to keep from confusing people. And by that I mean that - what value do you show? The current one, or the one after the update is applied? That could be dangerous if you didn't know which. So, maybe it is better it doesn't show so you know that it is an update query, hmm?

 

[Db396]

Good point - thank you.

I love Access but am going through a steep learning curve. Thanks for all your comments

David

 

[pr2-eugin]

Edit Damn that Paul he's too quick, these peskey youngsters.

Ha ha..