Query/Table Update Help

[mrmagoo_83]

I am fairly new to complex table updates, and am struggling with a query to update two tables based off another.

I have an EXPECTED and FAMILY Table.
I am trying to updated the EXPECTED.FAMILY to equal the FAMILY.FAMILY field based on the EXPECTED.MODEL field matching the FAMILY.MODEL field.

Additional twist is the Expected.Model can have a "/" in it, I need to compare what is to the left of the "/" to do the comparison.

I know it is a Left Outer Join, but I cannot seem to get the SQL to work out right.

Can I get some help?

Thanks.

[CJ_London]

Additional twist is the Expected.Model can have a "/" in it, I need to compare what is to the left of the "/" to do the comparison.

Not sure why you need a left join. Providing you don't use the query design window. just the sql window you can do something like this as a join

UPDATE Expected
SET Family=family.family
FROM Expected INNER JOIN Family ON left(Expected.Model, instr(Expected.Model,"/")-1)=family.model

Also have field names the same as the table name is a bad idea and may be why your sql is failing.

What sql are you using at the moment?

And think we need to see some example data with required outcome to fully understand your requirement.

[mrmagoo_83]

Thanks for the help, however, I get an error with your query as is.
I am using Access 2013 and simply the SQL design tab of the query window.
I did as suggested and changed the fields to not be identical to the table name.
Unfortunately due to the nature of the data I cannot share the actual data.

Here are my two tables:
FAMILY EXPECTED MODEL MKT Model FAMILY_NAME FAMILY_NAME

I need the Expected.[Family_Name] to match the Family.[Family_Name] where Expected.[MKT Model] equals Family.[Model]. There will be some matches, and then other that will not match.

Expected.Model sample: ABC123 / Orange Cable
Family.Model sample: ABC123

[CJ_London]

not much to go on. my query 'as is' is freetyped so may have an error. You get an error, but don't say what it is and haven't provided the sql you are actually using now you have changed names. and no example data that makes sense to me (if confidential, make some names up, provide a few records from each table and the outcome required). There is no point me speculating what the issue is.

[mrmagoo_83]

Appreciate the help, multi-tasking on a Monday morning and did miss including helpful information.

SQL as is:

UPDATE Expected
SET Expected.Family_Name=family.family_name
FROM Expected INNER JOIN Family ON left(Expected.Model, instr(Expected.Model,"/")-1)=family.model;

Error:
Syntax Error (missing operator) in query expression 'Family.Family_Name FROM Expected INNER Join Family ON Left(Expected.[MKT Model], InStr(Expected.[MKT Model],"/")-1=Family.Model'.

Family Table
MODEL	FAMILY_NAME
ORG100	Orange Hose
ORG050	Orange Hose
BLU050	Blue Hose
Expected Table
MKT Model	FAMILY_NAME
ORG100 / 100mm Orange Hose	Orange Hose
ORG050 / 50mm Orange Hose	Orange Hose
BLU050 / 50mm Blue Hose	Blue Hose

Does this help setup what I'm trying to accomplish?

Again, thanks for your patience and help.

[CJ_London]

don't understand the error - the sql says one thing, the error another

your sql ....
FROM Expected INNER JOIN Family ON left(Expected.Model, instr(Expected.Model,"/")-1)=family.model;

your error...

FROM Expected INNER Join Family ON Left(Expected.[MKT Model], InStr(Expected.[MKT Model],"/")-1=Family.Model


so looks like your error is coming from another query - and if you compare the two lines, you can see you are missing a bracket

and with regards your example data, where does '/ 50mm' etc come from?

I can't help you if you do not give me accurate and relevant information.

[mrmagoo_83]

The "/ 50mm" is part of the 'name' if you will that was given to the data by our retail IT folks when they created their table. It really is coming from the part number, i.e. ORG050, the 50 means 50mm. The "MKT Model" field in some IT database somewhere contains "ORG050 / 50mm Orange Hose", but in another table from our 3PL it is just the first half, i.e. "ORG050". I am left trying to meet in the middle to create a working setup for my department, as the company IT nor the 3PL IT will recreate a billion plus records to update things so the two systems match. I need to create a Pivot Table in Excel based off the FAMILY_NAME field as I could care less if it was 50mm, or 100mm hose, I just need to know that it was an Orange Hose. So I need the Expected.Family_Name to equal Orange_Hose so I can more easily filter down to it rather than my users trying to remember every entry between 01mm and 200mm as it is not straight forward, i.e. we have a 20, 21, & 22mm, but do not have a 31, or 32mm.

Sorry about the SQL vs. Error, I was trying to tweak it to fix it and copied the tweaked error version into my post.

Here is a screenshot of the query and the error associated.

[CJ_London]

no screenshot attached, but suggest you read my comment - and if you compare the two lines, you can see you are missing a bracket