Relationships

Which table has just the numbers? If the one with the numbers is
Prices then it needs to be on the right hand side of your Like
statements.

On Feb 13, 6:34 am, Matt Dawson
wrote:
I have this as my SQL and nothing

SELECT AT.MODEL_ID, Prices.MODEL
FROM [AT], Prices
WHERE (((Prices.MODEL) Like [AT].[Model_ID] & "?*")) OR (((Prices.MODEL)
Like "* " & [AT].[Model_ID])) OR (((Prices.MODEL) Like [AT].[Model_ID] & "
*"));

Any ideas why i get no results?

Matt

"Jason Lepack" wrote:
Sorry misunderstood your last one.

I see that you're getting into the dangerous world of partial
matches. My general solution was based on the fact that you had a
number followed by a letter. (and my solution would have been somewhat
flawed in that point) Now there are may more variables, for example:

table1:
200

table2:
Product 200
200b
2000c (original solution flawed in this case)
1200

To query this is hazardous. Maybe this... but it will only work for
the cases I have listed above:

SELECT TableA.simField, TableB.simField
FROM TableA, TableB
WHERE TableB.simField Like [TableA].[simField] & "?*"
OR TableB.simField Like "* " & [TableA].[simField]
OR TableB.simField Like "* " & [TableA].[simField] & " *"
OR TableB.simField Like [TableA].[simField] & " *"

Case 1: 200a (has only the number followed by a character)
Case 2: Product 200 (end with number after space)
Case 3: BLAH 200 Blah (spaces surrounding the number)
Case 4: 200 blah (start with number followed by space)

BTW,
? is used to represent a single character
* is used for any number of characters or digits

This should be enough for you to work with.

Cheers,
Jason Lepack

On Feb 12, 12:10 pm, Matt Dawson
wrote:
Sorry guys,

Still this seem to bring up some results but not the ones with longer names
ie "Product 200" to just "200". I cant change the data as is what is
extracted from the main database.
Any other ideas?
Matt

"John Spencer" wrote:
You cannot use the query grid to build a non-equi join (equi joins are those
where field(s) in tableA are equal to field(s) in tableB.

You can do this in the SQL window.

UPDATE TableB INNER JOIN TableA
ON TableB.Model LIKE TableA.Model & "*"
SET TableB.SomeField = [TableA].[SomeOtherField]

Also, you could build a query using the query grid (Design view) and
"normal" joins and then switch to SQL view and replace
ON TableB.Model =TableA.Model
With
ON TableB.Model LIKE TableA.Model & "*"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Jason Lepack" wrote in message
roups.com...
Just replace the "simField" with the name of the fields that are
similar.

SELECT TableA.simField, TableB.simField
FROM TableA, TableB
WHERE TableB.simField Like [TableA].[simField] & "*";

Cheers,
Jason Lepack

On Feb 12, 10:54 am, Matt Dawson
wrote:
Is it possible to create a like relationship?
I have two tables but one contains variations on the other ie setup per
machine and are called different models.
For example in Table A i will have a 123 and need this to be linked to
Table
B where it will have the 123a and 123b listed! I need to add one field
from
table A to table B.
Is there anyway of doing this?
Many Thanks,
Matt- Hide quoted text -

- Show quoted text -

Try replacing the "*" with "%".

The wildcard is "*" for Access and the JET database engine, however, if you
are using an Access Project (.adb or .ade) the wildcards are the ANSI
standard "%" (for any number of characters) and "_" for one character.

SELECT AT.MODEL_ID, Prices.MODEL
FROM [AT] INNER JOIN Prices
ON Prices.Model LIKE "%" & AT.Model_ID & "%"

If that fails try reversing the join and the criteria.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Matt Dawson" wrote in message
...
SELECT AT.MODEL_ID, Prices.MODEL
FROM [AT] INNER JOIN Prices ON Prices.Model LIKE "*" & AT.Model_ID & "*"

Is the SQL i have used and it brigns me zero results unfortunately.
Any other ideas.
Thanks
Matt

"John Spencer" wrote:

Perhaps you could use

ON TableB.Model LIKE "*" & TableA.Model & "*"

But this will probably give you false matches - that is matches you don't
want. Partial matches are troublesome.

With the above and TableA.Model being "200" you would get matches in
TableB
for
Model 200
200A
Project 200A
Product 200000A1 R9
200

Also, it would match every value in table B if any record in tableA had a
blank Model field. As a matter of fact that would happen with almost all
the solutions proposed so far.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Matt Dawson" wrote in message
...
Sorry guys,

Still this seem to bring up some results but not the ones with longer
names
ie "Product 200" to just "200". I cant change the data as is what is
extracted from the main database.
Any other ideas?
Matt

"John Spencer" wrote:

You cannot use the query grid to build a non-equi join (equi joins are
those
where field(s) in tableA are equal to field(s) in tableB.

You can do this in the SQL window.

UPDATE TableB INNER JOIN TableA
ON TableB.Model LIKE TableA.Model & "*"
SET TableB.SomeField = [TableA].[SomeOtherField]

Also, you could build a query using the query grid (Design view) and
"normal" joins and then switch to SQL view and replace
ON TableB.Model =TableA.Model
With
ON TableB.Model LIKE TableA.Model & "*"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Jason Lepack" wrote in message
oups.com...
Just replace the "simField" with the name of the fields that are
similar.

SELECT TableA.simField, TableB.simField
FROM TableA, TableB
WHERE TableB.simField Like [TableA].[simField] & "*";

Cheers,
Jason Lepack

On Feb 12, 10:54 am, Matt Dawson
wrote:
Is it possible to create a like relationship?
I have two tables but one contains variations on the other ie setup
per
machine and are called different models.
For example in Table A i will have a 123 and need this to be linked
to
Table
B where it will have the 123a and 123b listed! I need to add one
field
from
table A to table B.
Is there anyway of doing this?
Many Thanks,
Matt