Query/Table fields based off value of another field.

[MPXJohn]

Ah. Those ID's do not match up because the ID's in ASSEMBLY for a specific AssemblyName are not repeated. But in ProductionHeaders, The ItemName appears multiple times, so the ID's do not correspond one-to-one.

[MPXJohn]

Ah, Those do not match up. Most likely because AssemblyName in ASSEMBLY is used only once (It is just like a master list say), whereas the ItemName in ProductionHeaders is used many times. So the same ItemName can be in multiple rows. So the correspondence is not one-to-one.

[Bulzie]

It is ok to have 1 record in ASSEMBLY and multiple records in ProductionHeaders. So in ASSEMBLY, you might have one record with ID 100, and in ProducationHeaders you could have 3 records all with IDs of 100 that relate back to that 1 ASSEMBLY record right?

From your ProductionHeaders table, is it ItemID you need to link to ID in ASSEMBLY or is ID right? Look in your tables at the values to make sure.

[MPXJohn]

We need to link ItemDes in ProductionHeaders to ItemDes in ASSEMBLY so the ASSEMBLY ItemDes will update the ProductionHeaders ItemDes. The thing is the 3 records we could have would not all have IDs of 100. They would each have their own ID. But in ASSEMBLY the ID would be unique.

However, I just found out that the data I need is not actually located in ProductionHeaders. Unbelievable!! It is actually in a query called ProductionData_MAIN. So I rebuilt the update query to reflect that. Image inserted below. I may not fully understand how they need to be linked. Right now I have ItemDes linked to ItemDes, but it is still not updating.Update Query2.pdf

[Bulzie]

I don't think that will work. You are linking ItemDesc in both tables, so it will only consider records in both tables where those values are equal. But in your update, you are trying to make ItemDesc the same in both tables but they are already the same value based on linking ItemDesc.

Is there only 1 record in ProductionData_MAIN that could match the record in ASSEMBLY?

[MPXJohn]

Correct, I am trying to make ItemDes the same, but right now they are not the same. Right now, the are completely blank in Production_MAIN. I'd like to update them to the description found in ASSEMBLY. So as data is added to Production_MAIN, the ItemDes can be updated based off ItemDes in ASSEMBLY.

No, There will be multiple records in Production_MAIN that could match the one record in ASSEMBLY.

[Bulzie]

I feel like we are going in circles. Your first query design might be the right one.

In ASSEMBLY, what is your key unique field for each record? In Production_MAIN, what is the field that links to the key field in ASSEMBLY_Main? Seems like it would be that ID field but can you check and verify. You need to link the 2 tables by that field.

[MPXJohn]

OK.
In ASSEMBLY there are five columns (ID, ASSEMBLY, ItemDes, AssemblyName) Every field should be unique in this table. None are repeated.
Right now I have ItemDes linked to ItemDes. But that is only in the update query. Otherwise these two (ASSEMBLY table & Production_MAIN query) are totally independent of each other and not necessarily "linked".

[Bulzie]

Can you give an example of the actual data in both tables.

[MPXJohn]

Let me know if you need something different.

[Bulzie]

See below, I think that query will work.

[Bulzie]

Played around alittle more and I think this query will work. Check table names and field names to what yours need to be. Pop this code into a Query SQL window, then go to design view to see. So both tables are in the query but not linked. Always make a backup of your data tables before doing any updates or testing incase it is not what you want.

UPDATE ASSEMBLY, Production_Main SET Production_Main.ItemDes = [ASSEMBLY].[ItemDes]
WHERE (((Production_Main.ItemName) Like "*" & [ASSEMBLY].[AssemblyName] & "*"));

[MPXJohn]

OK Bulzie. Thanks for all your help.

[MPXJohn]

I am receiving this error. "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect" This is what the SQL looks like:

SELECT
FROM ASSEMBLY, ProductionData_MAIN;
UPDATE ASSEMBLY, ProductionData_MAIN SET ProductionData_MAIN.ItemDes = [ASSEMBLY].[AssemblyDescription]
WHERE (((ProductionData_MAIN.ItemName) Like "*" & [ASSEMBLY].[AssemblyName] & "*"));

[Bulzie]

Go to new Query SQL window and paste this, does it give you same error?

UPDATE ASSEMBLY, Production_Main SET Production_Main.ItemDes = [ASSEMBLY].[AssemblyName]
WHERE (((Production_Main.ItemName) Like "*" & [ASSEMBLY].[AssemblyName] & "*"));