CODEWITHSUNDEEP
sqlms-access
Reid
Reid

Reputation: 25

Access/SQL Update Query Help [If Statement Issue]

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

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

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]

Upvotes: 1

Views: 664

Answers (2)

kismert
kismert

Reputation: 1692

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.

Upvotes: 1

SithApprentice
SithApprentice

Reputation: 53

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!

Upvotes: 0

Related Questions