SQL for Update Query

[WeeTerrier]

Hi,



I have a basic knowledge of Access but am struggling with getting update queries to run. Sometimes they work perfectly and other times I can't seem to the the syntax quite right and don't know enough about SQL to spot the mistakes, can anyone help?

I basically want to update a field on a table, but to ensure the right value is taken from the reference table I need to make sure that two fields on the update table match those on the reference table.

I have tried creating a query in design view but it doesn't seem to work. Therefore I've been playing around with the SQL and have the following, but it only returns values from the update table which I entered manually:

UPDATE tblOperatorsInstallations, Operations
SET Operations.MSReference = [tblOperatorsInstallations]![MSReference]
WHERE (((Operations.Installation)=[tblOperatorsInstallations]![Installation]) AND ((Operations.Operator)=[tblOperatorsInstallations]![Operator]));


Any advice appreciated!

[hertfordkc]

Hi,

I have a basic knowledge of Access but am struggling with getting update queries to run. Sometimes they work perfectly and other times I can't seem to the the syntax quite right and don't know enough about SQL to spot the mistakes, can anyone help?

I basically want to update a field on a table, but to ensure the right value is taken from the reference table I need to make sure that two fields on the update table match those on the reference table.

I have tried creating a query in design view but it doesn't seem to work. Therefore I've been playing around with the SQL and have the following, but it only returns values from the update table which I entered manually:

UPDATE tblOperatorsInstallations, Operations
SET [Operations]![MSReference] = [tblOperatorsInstallations]![MSReference]
WHERE ((([Operations]![Installation])=[tblOperatorsInstallations]![Installation]) AND (([Operations]![Operator])=[tblOperatorsInstallations]![Operator]));

Any advice appreciated!

I admit that the syntax of table/field references is somewhat mystical to me.

[WeeTerrier]

Thanks for your help. I've tried the amended code you gave me but it gives me the same result. It's almost like it can't find the other table. Am I doing somethnig wrong in the "update" line??

[hertfordkc]

http://http://www.techonthenet.com/sql/update.php

The second example seems to cover exactly what you are trying to do. It also suggests that the syntax which I suggested is moot.
HTH

[WeeTerrier]

Thanks for the link. I think I must be pretty close with the coding but there's still something not quite right as the query is still giving me the same results... I'll keep trying and see how it goes.

Thanks again for your help.

[orange]

UPDATE tblOperatorsInstallations, Operations
SET Operations.MSReference = [tblOperatorsInstallations]![MSReference]
WHERE (((Operations.Installation)=[tblOperatorsInstallations]![Installation]) AND ((Operations.Operator)=[tblOperatorsInstallations]![Operator]));

Try

UPDATE Operations INNER JOIN tblOperatorsInstallations ON
tblOperatorsInstallations.Installation = Operations.Installation AND
tblOperatorsInstallations.Operator = Operations.Operator
SET Operations.MSReference = tblOperatorsInstallations.MSReference ;

Post back.

[JenEveAle]

I'm pretty good with update queries but not with SQL. If you can't figure it out, paste a screenshot of your query design view and I might be able to help.

[WeeTerrier]

Same result as before - the MSReference column from the Operations table. I was playing around earlier using the the following code:

UPDATE Operations
SET Operations.MSReference = tblOperatorsInstallations!MSReference
WHERE (((Operations.Installation)=tblOperatorsInstallati ons!Installation) And ((Operations.Operator)=tblOperatorsInstallations!O perator));

Admittedly it still asks for parameter values, but when I enter them it brings up the right number of rows for the data I entered, the only problem is that they're blank...

[orange]

Show us your table definitions. If it's asking for parameters, then it seems you have a spelling mistake in my view.

Updates when 2 tables are involved can be tricky. I have found you must name the table to be updated first in the query.

This

UPDATE Operations
SET Operations.MSReference = tblOperatorsInstallations!MSReference
WHERE (((Operations.Installation)=tblOperatorsInstallati ons!Installation) And ((Operations.Operator)=tblOperatorsInstallations!O perator));

will NOT work.

It's SQL and you have 2 tables -- they must be joined in some manner.
You are using !, which is vba not SQL.

Did you try the query I posted?

[WeeTerrier]

Yes, I tried the query you posted but it came back with the same result as before - the MSReference column from the operations table.

Ah ok, I didn't realise ! was VBA. Thanks.

I don't know if it makes things easier, but I've attached the updated design view of the query. I tried it in design view originally and couldn't get it to work so resorted to SQL (which might have been a bad idea due to my lack of knowledge!).

As for the table definitions there are three fields that are contained on both tables, these are:

MSReference
Operator
Installation

The operator and installation are joint primary keys on both tables. The only problem I noticed is that when I created the relationship between the tables, Access made it one-to-one rather than one-to-many. Could this make a difference?

Again, thanks for your help.

[orange]

You only have 1 table in your query grid. You say there are 2? Why do the tables have the same fields?
Perhaps we should start with what is the application?
What are you trying to do?

[WeeTerrier]

Ah ok, I thought that by linking in the second table (tblOperatorsInstallations) through the criteria that this was enough to get the two tables to talk to one another. (I guess not - I'm still getting to grips with update queries.)

I was treating my "tblOperatorsInstallations" like a reference table:
there are a number of operators who potentially deal with more than one installation. So operators may be on this table more than once, but installations are only dealt with by one operator. (Different operators do not deal with the same installation).


e.g. "tblOperatorsInstallations"

MSReference = 1
Operator = Albert
Installation = Blue

MSReference = 2
Operator = Albert
Installation = Pink

MSReference = 3
Operator = Roy
Installation = Green

The MSReference is the unique number for that operator and installation. To avoid duplicating the installation and operator columns on the "Operations" table permanently, it seemed more sensible to create the MSReference and use this to link the two tables (one to many relationship). (Does that make sense so far?)

Rather than inserting the MSReference into the "Operations" table by hand, I wanted to run an update query so Access would do it for me.
Therefore I added the Operator and Installation columns into the "Operations" table temporarily (I will delete them later), to try and create a query which said something along the lines of:

WHERE:
"Operator" in the "Operations" table is the same as "Operator" in the "tblOperatorsInstallations"

AND:
"Installation" in the "Operations" table is the same as "Installation" in the "tblOperatorsInstallations"

UPDATE
"MSReference" in the "Operations" table to be the same as "MSReference" in the in the "tblOperatorsInstallations" table

So when both fields are the same in both tables then the correct MSReference number should be entered, and after this is done, I can delete the Installation and Operator columns from the "Operations" table.

(There is other data in the operations table, but it's not relevant to this query other than making sure it is associated with the correct MSReference.)

I hope this helps and doesn't confuse things further!