Queries - Update Query Issue

I have a fairly simple Update Query.
On the Table "NewWork", if it finds a key search term in the OrderDescription field, then it updates the "ProductID" to a certain product.
I have a table called "Products" with 2 columns, "ProductID" and "Employee".
Currently, the query also updates the "AssignedTo" field in "NewWork" with an Employee name that I typed in.

What I WANT it to do is Update the "AssignedTo" field by selecting "Employee" from the "Products" table, WHERE the row contains the previously assigned ProductID.

Basically I don't know how to write the "rowsource"? type of command in design view of an update query.

This is what I have in SQL view currently:
UPDATE NewWork SET NewWork.ProductID = "Advance Tracker", NewWork.[Assigned To] = "Varner, K"
WHERE (((NewWork.ProductID)="Unassigned") AND ((NewWork.Order_Descripton) Like "*Advance*"));

Just want to change where it says "Varner, K" to something where it can find the name itself in that Products table.

I know this should be simple.....ANY help GREATLY appreciated!!!
Thank you!

By 'previously assigned' you mean assigned to the employee? Because you are updating unassigned those records do not have a previously assigned ID, right?

The ProductID is a unique key in Products table? So why save the employee name to NewWork table? Do a join of these tables when you want to report out the employee name. If you have to retrieve the employee name so it is permanently associated with the NewWork record regardless of staff changes then should be able to use DLookup or a subquery. Something like: DLookup("Employee", "Products", "ProductID='Advance Tracker'")

Product ID is a unique value in the Products table.
The reason for saving the Employee name in that table is for multiple reasons. 1, being that employee turnaround id very high, and what employee is handling what product changes on a frequent basis.
And being that I won't be around here for ever (or for long!) a manager who has very little familiarity with Access needs to be able to update it.
So, clicking on Tables, Products, and just changing the name in the "Employee" column next to the Product name is the simplest way i could think of for someone to be able to maintain it without having to modify queries, or know any coding.

And I know that I have seen a way to specify this before, I just can't find it now. It seems like it was just basically SELECT "Employee" FROM "Products" WHERE **something**="ProductID"
I want to say Rowsource, but I don't think that's it.

RowSource is a property of combo and list boxes. RowSource can be used to limit data entry choices.

Now confused. I thought you wanted to record the employee name in NewWork table, for which I gave a suggestion. Sounds even more like you should not be saving the employee name in NewWork table. Odd name for a table, doesn't work eventually get to be 'Old'?

If you want to update the Employee associated with a ProductID in the Products table, that is simple. Yes, can update directly in the table if you want to allow users full permissions to do what they wish in the project. If you want to control users, then set up menu forms that will execute opening of data entry forms or reports. Set up the project to not display the navigation pane and to open to a particular form. Hold down the shift key when opening a project and will override these settings.

The reason is that records are added to the "NewWork" table about 3000 at a time, imported actually. And when they are imported there is no Product ID or Assigned To field.
Therefore, the query searches the text string of a field that is there (order description) and based on that, assigns it a Product ID. And of course what I'm trying to do is have it fill that "Assigned To" field based off the "employee" that is listed next to that Product ID in the Products table.
No one will have access to the query or the Products table except the Manager, and me of course. They are protected.
The "Users" open a form, select their name, or the product they are working on and it filters the records based on that.
No one needs to see all 5984 records that are in the table (speaking from a general user sense).

So, that's why it needs to assign the field automatically. No manager, or me, is going to sit there and do it for all 3000 records every week when they are imported.

Okay, so you have the ProductID extracted for the order description and populate into the ProductID field. Still not seeing that you need to populate the employee name into the NewWork table. You can have the form that users see have a RecordSource that is a join of the two tables. As long as you don't care about history of who used to be assigned to work/product, what you want to do is make sure the Products table is updated with the current employee assigned to that product, which will then be reflected in all work records for that product.

Currently, the "Assigned To" field in the form is a combo box, the row source is a SELECT DISTINCT of the Employee column in the Products table.
It is a drop down so that it can be changed if need be. For example if I were to take over a record for someone else. It may not be my regularly assigned product, someone could be out sick and I handle something for them.
So how do I change it to what you've suggested?

Would the substitute assume responsibility for ALL assigned records of the sick employee? No need to change the employee assignment. The substitute could just query for sick employee's records. If you haven't already, set up the Users Search/Filter form so that it filters a join query of both NewWork and Products table, linked by the ProductID field, display the employee name from the Products table, no need for AssignedTo field from NewWork table.

If you want to be able to set work assignments case-by-case then that would require the AssisgnedTo field in NewWork table to be maintained. So far, from your descriptions, I don't understand this to be the need. If I am off track, let me know and will go back to the one I started in my first post.

The Assign To field can be changed via a drop down in the main form that all work is being done in, so changing it for just one record or two is not a problem. And is done that way for just that reason.
I have a search form that they can use to bring up the needed records by searching by Assigned To, or ProductID, or Contract number, or whatever info the person on the phone or via email is giving you to search by.
By having the Assigned To drop down in the main work form, records can be changed on an individual basis, there are times that this is needed. But the 3000 imported records still have to be updated automatically, to save, well, countless, hours updating them.
The reason for the Assigned To field at all is mainly for reporting purposes. when work is completed, check boxes on the form feed dates into the table, then management pulls reports based on Employee, Product, number of contracts, and what dates different stages of the work process is done. This way they can see how many Contracts I closed this month.
There are Employees that handle more than 1 product.

The Product ID field is generated/filled via the Update query that searches for key words, what i currently have is that it also updates the Assign To field to the text (Employee) that I have manually typed into the query. The problem is, there is no one else here that will be able to do that. (They don't understand the queries or the coding) If it could just pick the Employee based off of that Products table, then it wouldn't matter. When I'm gone, all they would have to do is open that table, and replace My name with someone else's next to the Products that I handle. Then the update queries wouldn't need to be touched.
Does that clear it up at all?

Okay, think I get it, you want your cake and eat it too!

So for the 3000 record import you need to update the AssignedTo field for these new records with an employee name but you want flexibility to reassign work regardless of ProductID/Employee associations in the Products table.

For the import, back to my original post. You certainly have options, the DLookup suggestion is one.Problem I see is how to get the ProductID into the query, surely it is not always 'Advance Tracker'? Instead of a saved query, I would build the SQL in VBA code. It is possible to pass values to a saved query when it is run but I avoid this, only couple of my saved queries have variable parameters. How is the import initiated - by a button command on a form? I would put VBA code to build and run the Update query behind that form.

Do you use macros (I don't) or VBA coding? Could you show your code? Or if you can post your project here (maybe a copy without data) I will look at.
EDIT: Can't attach files here until member for 30 days or so many posts or something so could upload to file share site like Box.net and post the file link here.

Yes, I'm greedy like that!
The DLookup worked great! Thank you! That's exactly what I was looking for.

You're right, it's not always "Advance Tracker" for the Product ID.
Currently, I have a seperate Update Query for each Product, so they have to be run one at a time. I know, that's stupid. But in my defense I am still in the building/developing stage here, primarily I just wanted to make sure I had good search terms with my Like *'s before I go consolidating them. (Don't want them getting assigned the wrong Product name!

Here is the code that this query has now:

UPDATE NewWork SET NewWork.ProductID = "Advance Tracker", NewWork.[Assigned To] = DLookUp("Employee","Products",[Product_Name]="Advance Tracker")
WHERE (((NewWork.ProductID)="Unassigned") AND ((NewWork.Order_Descripton) Like "*Advance*"));

There are 14 of these in all, all with different key words (Some have an AND) after the Like "*....... No, it doesn't take very long to run all of them, but that is a lot of clicking, and, well, it's just messy. But with the different criteria that each requires, I'm not sure how to combine them all into something I can just run that will do it all. Maybe a Macro? I don't really use them either though. In VB it can just be a Nested IF, I think (?). Maybe combine them into a VB big IF statement, then have that in the EVENT (On load? update?) when a new record is added. That way there is nothing to click on, it's just done when the records are imported?

Just talking it out here...

You won't believe me when I tell you how these are being imported for now.......figuring out a way to fix that will be my next step......
The records currently come from ORACLE, from a SQL server.....Great you say? Access can do that automatically? Oh no no no. The "powers that be" have decided that we just don't need access to the server for something as silly as this. The ONLY export from Oracle that we can do is via their crappy template that puts the records into Excel. And of course there are a million trailing spaces in most of the fields at that point, and Access just doesn't like it. Tried importing straight from Excel, no go. Too many data type and size issues, etc.
So, I get to just Copy about 1000 rows at a time, and paste them into the Table in Access.
Not reallly "that bad", stupid, but doesn't take too long....Except, that out of about 3000 records, about 400 of them have extra columns thrown in for some stupid reason, so they of course won't paste into the table, and go into Paste Errors table. Then, after all that copying and pasting, I get to go into the errors table and paste those records in either several fields at a time for some of the records, until i'm left with records that at least are a consistent format where I can just Delete the extra column or two, then just copy and paste all of those at once. Okay, now I'm just venting.....Sorry.
Like i said, fixing that is next on the list.

Anyway, current issue first.....
I said that the DLookup worked great, but there is one little problem. When I click run on the Query, it pops up the Parameter Value box for Product_Name. If I don't put anything in there, just hit OK, It still updates the correct records with the correct data......so any ideas why it's popping up? Or just how to make it stop doing that? Don't really need one more thing to have to click on at this point.....lol

Thank you for all your help, GREATLY appreciated!
(and sorry for the venting....but I'm sure you can understand my annoyance with the whole thing!)

The DLookup clause must be within quote marks:
"[Product_Name]='Advance Tracker'" and in my experience need text delimiter (apostrophe).
Odd, that it still worked, are you sure? Why using Product_Name instead of ProductID?