Query to Update a Query 1

[CajunCenturion]

I have taken over an Access project started by another individual, and have across the following situation.

There is an update query in the form

UPDATE Query_Name SET Query_Name.Field1=Null

Where Query_Name is the name of Select Query in the form

SELECT Table_Name.Field1, etc,etc
FROM Table_Name
WHERE Table_Name.Field1 = [Forms]![FormName]![TextBox]

I have never encountered a situation where an Update Query is updating a Select Query. Can anyone shed some light on just what this type of construct actually does? (or is my first impression correct?)

Thanks

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[scriverb]

As long the query to be updated is an updateable query you certainly can update a field/column. The update just passes the update info to the underlying table and updates the field.

Post back with any questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[kraxmo]

It is possible to have one query update another query. This is the same thing as telling a dumb friend to paint your house instead of you doing it yourself.

A SELECT query is really just a view of a table (when you open a table, an implicit SELECT query returns the data to you). When setting up the queries, it is very important that the underlying table has a primary key and the query that uses it is updatable (no totals, DISTINCTS, etc.) Think of it as verifying that the paint can is open before handing it to your dumb friend (you can't update the house's paint if the can is closed).

Jim Kraxberger
Developing Access solutions since 1995
jkraxberger@scp4me.com

 

[CajunCenturion]

OK, but why not simply update the table directly from the Update query. What's the advantage of using the Select Query as an intermediate object in the process? Simply to piggy back on the Where clause in the select query?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[scriverb]

You have hit it right on the head. No real advantage. Just using the WHERE statement from the Select rather than putting the same in the Update query.

krasmo: I love your analogies.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[CajunCenturion]

Upon reflection, I can see one advantage. If you can guarantee over the life of the app that the Where clause in the Select query will always be the same as the Where clause in the Update query, then by having the Update query use the Select query, you only have 1 where clause to maintain. And if the Where clause is somewhat complex, this could be worthwile. On the flip side, several other things must remain constant as well between the two queries.

Thanks for the insight. I've met my quota for today - learned something new.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein