Updating table with query of 2 other tables?

[Guest]

Greetings

I apologize in advance if this seems too easy to do- but I am missing something.

I have 8000 records in an Access DB (I know- it's going into SQL soon) in which all  the departmental office IDs need to be updated.

The column in the main table  is named "q_office_ID".

The user table includes  user_ID &  office_ID.

The office table also includes office_ID.

This UPDATE sets all the values to the number 10?:

<cfquery name="update" datasource="#Request.BaseDSN#">

SELECT     queue_ID, q_user_ID, user_ID, office_ID

FROM     main_helpdesk, lookup_user

WHERE  q_user_ID = user_ID

</cfquery>

<cfloop query="update"><cfquery name="updatedata" datasource="#Request.BaseDSN#">

UPDATE main_helpdesk

SET q_office_ID = #office_ID#

</cfquery></cfloop>

I know there is something obviously wrong with the update- any help would be greatly appreciated.

Thank You

[Dan_Bracuk]

The lack of a where clause is pretty significant.    

[Guest]

Dan

Whether I missed something as obvious as that or I already tried the "where" clause I guess is not clear- but I only asked because I tried it many different ways with no good result.

[Guest]

Actually, it was:

<cfquery name="update" datasource="#Request.BaseDSN#">

SELECT queue_ID, q_user_ID, user_ID, office_ID

FROM main_helpdesk, lookup_user

</cfquery>

<cfloop query="update">

<cfquery name="updatedata" datasource="#Request.BaseDSN#">

UPDATE main_helpdesk

SET q_office_ID = #office_ID#

WHERE q_user_ID = #user_ID#

</cfquery></cfloop>

My mistake.

[Dan_Bracuk]

Did you ever try a single query?  This page describes how to do it.  http://www.techonthenet.com/access/queries/update2.php