Relationships

[Guest]

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

 

[Jason Lepack]

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

 

[John Spencer]

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
..

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

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

 

[Guest]

Jason,

this has partially worked thank you, but is there anyway of making the Likes
even further apart.
For example i have a Product 2500 in one table and 2500 in the other and
this does not bring it up

Thanks,
Matt

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

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

 

[Jason Lepack]

SELECT TableA.simField, TableB.simField
FROM TableA, TableB
WHERE TableB.simField Like TableA.simField & "*"
OR TableB.simField = TableA.simField;

Cheers,
Jasone Lacpk

Jason,

this has partially worked thank you, but is there anyway of making the Likes
even further apart.
For example i have a Product 2500 in one table and 2500 in the other and
this does not bring it up

Thanks,
Matt

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

- Show quoted text -

 

[Guest]

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

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
..

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

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

 

[Jason Lepack]

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

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

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
..

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
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 -

 

[John Spencer]

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
..

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

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
..

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
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

 

[Guest]

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

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

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

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
..

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
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 -

 

[Guest]

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

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
..

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

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
..

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
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

 

[Jason Lepack]

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.

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

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:

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

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
:
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
..
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
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 -

 

[John Spencer]

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
..

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

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
..

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

:

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
..

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
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