Update with like * criteria

[Andy]

I asked something similar a while ago, but things have developed and I can't
get any further.
(http://www.accessmonster.com/Uwe/Forum.aspx/access-queries/49950/Join-on).

Currently I have two tables, 'Test Table' and 'Organisations'. Where the
'Remarks' field within the 'Test Table' contains a string that can be found
in the 'Organisations Code' within the 'Organisations' table, I want to
update the 'NACS Code' field within the 'Test Table' with the 'Organisations
Code' value.

i.e. if Remarks = 'RLN LIF' and 'RLN' exists in'Organisations Code', I want
a field in the 'Test Table' to update with 'RLN'. There are crica 300 records
in 'Organisations' but where the look-up exists, I need to generate a code.

Apologies if I haven't explained very well, but please let me know if I can
clarify as I really need some help with this...

Thanks in advance.

 

[John Spencer]

You probably need a query that looks like the following.

UPDATE [Test Table] as T INNER JOIN [Organisations] as O
ON T.Remarks Like "*" & O.[Organisations Code] & "*"
SET T.[NACS Code] = O.[Organisations Code]
WHERE O.[Organisations Code] is NOT NULL
AND O.[Organisations Code] <> ""

QUESTION: What do you want to do if a record in Test Table matches up to more
than one Organisations Code in the Organisations table? The above should
randomly select one of the codes.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Andy]

Excellent, have got that to work, thank you. I will just use the random
return for now until I've tested completely as the remarks field is free text
and I can limit what values are entered without a problem. I couldn't get
this to display in design view, is that possible?

Thanks again for your help though.

You probably need a query that looks like the following.

UPDATE [Test Table] as T INNER JOIN [Organisations] as O
ON T.Remarks Like "*" & O.[Organisations Code] & "*"
SET T.[NACS Code] = O.[Organisations Code]
WHERE O.[Organisations Code] is NOT NULL
AND O.[Organisations Code] <> ""

QUESTION: What do you want to do if a record in Test Table matches up to more
than one Organisations Code in the Organisations table? The above should
randomly select one of the codes.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I asked something similar a while ago, but things have developed and I can't
get any further.
(http://www.accessmonster.com/Uwe/Forum.aspx/access-queries/49950/Join-on).

Currently I have two tables, 'Test Table' and 'Organisations'. Where the
'Remarks' field within the 'Test Table' contains a string that can be found
in the 'Organisations Code' within the 'Organisations' table, I want to
update the 'NACS Code' field within the 'Test Table' with the 'Organisations
Code' value.

i.e. if Remarks = 'RLN LIF' and 'RLN' exists in'Organisations Code', I want
a field in the 'Test Table' to update with 'RLN'. There are crica 300 records
in 'Organisations' but where the look-up exists, I need to generate a code.

Apologies if I haven't explained very well, but please let me know if I can
clarify as I really need some help with this...

Thanks in advance.

 

[John Spencer]

No, it is not possible. If you use a non-equi join then the Design view
cannot display the query.

Non-equi join is a join where the on clause is other than field equals field.

You MIGHT get this to display in design view and it might work with a
cartesian query. This query if it works will probably be much slower.

UPDATE [Test Table] as T , [Organisations] as O
SET T.[NACS Code] = O.[Organisations Code]
WHERE T.Remarks Like "*" & O.[Organisations Code] & "*"
AND O.[Organisations Code] is NOT NULL
AND O.[Organisations Code] <> ""
n
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County