1

I'm working on a database that includes two different tables that need records matched and updated. Basically if two string fields are equal to each other, update a different field.

I can't quite figure it all out.

The first table, IngredientDB, includes two string fields, Item Number and Ingredient Name

The second table, Table Material Label, also includes two string fields, MaterialCode and MaterialDescription

Ingredient Name and MaterialDescription has a lot of the same records, but not all of them are the same.
I want to be able to

  • match MaterialDescription and Ingredient Name
  • update the MaterialCode to be the exact same as the item number that corresponds to that Ingredient Name.

So I tried using a update query and an if statement, this is what it looked like:

  • Field: MaterialCode
  • Table: Table Material Label
  • Update To: iif([MaterialDescription]=[Ingredient Name],[Item Number],"NotFound")
  • Criteria: BLANK
  • or: BLANK

The current issue is that all of the MaterialCode records get filled with "Not Found", meaning its not finding any matches for some reason...

Is there an easier way to do all of this? Am I missing something? Sorry I'm kind of a novice when it comes to Access stuff. Still learning!

Edit: Here is an image of where I'm currently at. The arrows indicate what I'm trying to change.

Code from image, currently getting syntax error

UPDATE [Table Material Label] JOIN [IngredientsDB] on [Table Material Label.MaterialDescription] = [IngredientsDB.Item Number] 
SET [MaterialCode] = [Item Number] 
WHERE [MaterialDescription] = [Ingredient Name]
Reid
  • 25
  • 3
  • https://stackoverflow.com/questions/12882212/sql-updating-from-an-inner-join – Joe C Jun 09 '17 at 15:27
  • Possible duplicate of [SQL Updating from an inner join](https://stackoverflow.com/questions/12882212/sql-updating-from-an-inner-join) – Joe C Jun 09 '17 at 15:27

2 Answers2

1

Based on your 'I want to be able to' list, the correct SQL would be:

UPDATE [Table Material Label] INNER JOIN [IngredientsDB] 
ON [Table Material Label].[MaterialDescription] = [IngredientsDB].[Ingredient Name] 
SET [Table Material Label].[MaterialCode] = [IngredientsDB].[Item Number];

If you want to match MaterialDescription to Ingredient Name, JOIN on these fields, then you can set MaterialCode = Item Number.

kismert
  • 1,662
  • 1
  • 13
  • 19
0

This is dependent on what version of access you are using. I'm not quite familiar with the front end portion of access however if you click on your update query from the designer, then right click on the query you an select SQL view. Based on the information you provided you would want something like the following UPDATE Table Material Label JOIN 'other table' on Table Material Label.'Here you would put your value that is found on both tables' = 'Other table'.'that column that contains the value' SET MaterialCode = Item Number WHERE MaterialDescription = Ingredient Name

Which should do the trick. Like I stated I'm not much of an access guy, I'm a younger SQL guy haha.

This article http://www.fmsinc.com/microsoftaccess/query/snytax/update-query.html#Update_Query_Examples Should provide further help.

Hope this helps!

SithApprentice
  • 43
  • 9
  • Thanks @SithApprentice -- Also,I'm using Access 2016 unsure if that makes a difference. I think I'm close, I've made changes to the code you've sent me to fit my tables. Although I'm currently getting a syntax error. – Reid Jun 09 '17 at 16:23
  • @Reid Answer below shows whats up. I just saw the code you posted. You must Explicitly call the tables, meaning provide the tables names. Example TableName.TableField is an explicit table call. kismert illustrates this beautifully below – SithApprentice Jun 09 '17 at 17:43