1 Update query for more then 1 table

[Cosmiccradle]

The problem is the following....I have three tables and three Update queries. Each table has fields/columns that are the same and some that are different per table. I was wondering if it's possible to make one query for all three tables that have the same fields/columns, so that I only need one update query instead of three. Any thoughts, is this possible?

 

[alvechurchdata]

You can't have one query which updates three tables but you could write a single SQL statement and plug the name of the table in at the last minute:

strSQL = "Update " & strTableName & " Set myfield = 42 ..."

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Cosmiccradle]

Is it possible then to make another table, and join this table with the other three and only change that table so it changes the other three?

 

[alvechurchdata]

Is it possible then to make another table, and join this table with the other three and only change that table so it changes the other three?

I'm afraid you've lost me. Can you give an example?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Cosmiccradle]

I was thinking of making another table with the fields of the other three, inner joining them and then changing the fields in the new table and thereby hope that it changes the same fields in the other three tables. That is to say make an update query for the new table with the information from the other three. Bit difficult to explain. But reading through the different threads over updating queries on this forum and on the microsoft site, it seems to me there must be a way, to change the same fields if they have the same values in all three tables at once.

 

[lespaul]

unfortunately, relational databases are designed so that you only store information in a single location, so no, I doubt you will find an easy solution for what you are trying to do.

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[alvechurchdata]

That is to say make an update query for the new table with the information from the other three.

As Leslie says you can't do this. You can create a new table but it will be completely separate from the original three and updates to one won't magically appear in the others.

Geoff Franklin

http://www.alvechurchdata.co.uk