Comparing first 5 characters

  • Thread starter Thread starter Dave Redmond
  • Start date Start date
D

Dave Redmond

I'm sure there's a simple answer to this question. I'm
new to Access.

I have 2 tables, each with a column titled "Company
Name." I would like to use a query to compare the two
columns and find matches.

However, I only want to compare the first 5 letters of
each company name, because I know that companies may be
spelled a little differently at the end.

For example, "Microsoft" might appear in one table
and "Microsoft, Inc." might appear in another table. If I
force the query to only return exact names, I won't be
able to catch this match.

How do I tell the query to compare only the first 5
letters of the fields in each column?

Many thanks,
Dave Redmond
 
You can use the Left() function to truncate the Field values before
comparing them. For example:

Left([YourField], 5) will truncate your Field values to the first 5
characters.
 
Van,

I tried your suggestion. For some reason, the Left()
function only returns company names that have less than 5
characters total.

For example, it returned a match between "TSI" and "TSI,"
but it did not return a match between "Microsoft"
and "Microsoft."

How can I improve this function to return company names
containing the same first 5 characters but which have
longer company names?

Thanks,
Dave
-----Original Message-----
You can use the Left() function to truncate the Field values before
comparing them. For example:

Left([YourField], 5) will truncate your Field values to the first 5
characters.

--
HTH
Van T. Dinh
MVP (Access)




I'm sure there's a simple answer to this question. I'm
new to Access.

I have 2 tables, each with a column titled "Company
Name." I would like to use a query to compare the two
columns and find matches.

However, I only want to compare the first 5 letters of
each company name, because I know that companies may be
spelled a little differently at the end.

For example, "Microsoft" might appear in one table
and "Microsoft, Inc." might appear in another table. If I
force the query to only return exact names, I won't be
able to catch this match.

How do I tell the query to compare only the first 5
letters of the fields in each column?

Many thanks,
Dave Redmond
Click to expand...


.
Click to expand...
 
Did you use the Left function on both of the fields that you're comparing?
You need to do that if you want a match for words that exceed 5 characters.

--

Ken Snell
<MS ACCESS MVP>

Dave Redmond said:
Van,

I tried your suggestion. For some reason, the Left()
function only returns company names that have less than 5
characters total.

For example, it returned a match between "TSI" and "TSI,"
but it did not return a match between "Microsoft"
and "Microsoft."

How can I improve this function to return company names
containing the same first 5 characters but which have
longer company names?

Thanks,
Dave
-----Original Message-----
You can use the Left() function to truncate the Field values before
comparing them. For example:

Left([YourField], 5) will truncate your Field values to the first 5
characters.

--
HTH
Van T. Dinh
MVP (Access)




I'm sure there's a simple answer to this question. I'm
new to Access.

I have 2 tables, each with a column titled "Company
Name." I would like to use a query to compare the two
columns and find matches.

However, I only want to compare the first 5 letters of
each company name, because I know that companies may be
spelled a little differently at the end.

For example, "Microsoft" might appear in one table
and "Microsoft, Inc." might appear in another table. If I
force the query to only return exact names, I won't be
able to catch this match.

How do I tell the query to compare only the first 5
letters of the fields in each column?

Many thanks,
Dave Redmond
Click to expand...


.
Click to expand...
Click to expand...
 
Van,

I tried your suggestion. For some reason, the Left()
function only returns company names that have less than 5
characters total.

For example, it returned a match between "TSI" and "TSI,"
but it did not return a match between "Microsoft"
and "Microsoft."

How can I improve this function to return company names
containing the same first 5 characters but which have
longer company names?

Thanks,
Dave
-----Original Message-----
You can use the Left() function to truncate the Field values before
comparing them. For example:

Left([YourField], 5) will truncate your Field values to the first 5
characters.

--
HTH
Van T. Dinh
MVP (Access)




I'm sure there's a simple answer to this question. I'm
new to Access.

I have 2 tables, each with a column titled "Company
Name." I would like to use a query to compare the two
columns and find matches.

However, I only want to compare the first 5 letters of
each company name, because I know that companies may be
spelled a little differently at the end.

For example, "Microsoft" might appear in one table
and "Microsoft, Inc." might appear in another table. If I
force the query to only return exact names, I won't be
able to catch this match.

How do I tell the query to compare only the first 5
letters of the fields in each column?

Many thanks,
Dave Redmond
Click to expand...


.
Click to expand...
Click to expand...

Instead of having us play 20 questions, why don't you copy and paste
the exact Where clause you are using.
 
Fred and Ken,

Thanks for your help. Here's the Where clause.

Query in Design View. I dragged two fields, one from each
table, down to the lower grid.

FIRST BOX:
Field: Company 1
Table: Current Clients
Sort: N/A
Show: checked
Criteria: Left([Current Clients]![Company 1],5)

SECOND BOX:
Field: Company 2
Table: Potential Clients
Sort: N/A
Show: checked
Criteria: Left([Potential Clients]![Company 2],5)

Ken, is this what you mean by using "the Left function on
both of the fields that you're comparing"?

Any help would be appreciated.

Thanks,
Dave Redmond
-----Original Message-----
Van,

I tried your suggestion. For some reason, the Left()
function only returns company names that have less than 5
characters total.

For example, it returned a match between "TSI" and "TSI,"
but it did not return a match between "Microsoft"
and "Microsoft."

How can I improve this function to return company names
containing the same first 5 characters but which have
longer company names?

Thanks,
Dave
-----Original Message-----
You can use the Left() function to truncate the Field values before
comparing them. For example:

Left([YourField], 5) will truncate your Field values
Click to expand...
to
the first 5
characters.

--
HTH
Van T. Dinh
MVP (Access)




I'm sure there's a simple answer to this question. I'm
new to Access.

I have 2 tables, each with a column titled "Company
Name." I would like to use a query to compare the two
columns and find matches.

However, I only want to compare the first 5 letters of
each company name, because I know that companies may be
spelled a little differently at the end.

For example, "Microsoft" might appear in one table
and "Microsoft, Inc." might appear in another table. If I
force the query to only return exact names, I won't be
able to catch this match.

How do I tell the query to compare only the first 5
letters of the fields in each column?

Many thanks,
Dave Redmond


.
Click to expand...
Click to expand...

Instead of having us play 20 questions, why don't you copy and paste
the exact Where clause you are using.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
Hi Dave,

PMFBI

you only need one column

Field: MatchMe: Left([Current Clients].[Company 1],5)
Table:
Sort: N/A
Show: unchecked
Criteria: Left([Potential Clients].[Company 2],5)



Fred and Ken,

Thanks for your help. Here's the Where clause.

Query in Design View. I dragged two fields, one from each
table, down to the lower grid.

FIRST BOX:
Field: Company 1
Table: Current Clients
Sort: N/A
Show: checked
Criteria: Left([Current Clients]![Company 1],5)

SECOND BOX:
Field: Company 2
Table: Potential Clients
Sort: N/A
Show: checked
Criteria: Left([Potential Clients]![Company 2],5)

Ken, is this what you mean by using "the Left function on
both of the fields that you're comparing"?

Any help would be appreciated.

Thanks,
Dave Redmond
-----Original Message-----
Van,

I tried your suggestion. For some reason, the Left()
function only returns company names that have less than 5
characters total.

For example, it returned a match between "TSI" and "TSI,"
but it did not return a match between "Microsoft"
and "Microsoft."

How can I improve this function to return company names
containing the same first 5 characters but which have
longer company names?

Thanks,
Dave

-----Original Message-----
You can use the Left() function to truncate the Field
values before
comparing them. For example:

Left([YourField], 5) will truncate your Field values to
the first 5
characters.

--
HTH
Van T. Dinh
MVP (Access)




"Dave Redmond" <[email protected]>
wrote in message
I'm sure there's a simple answer to this question. I'm
new to Access.

I have 2 tables, each with a column titled "Company
Name." I would like to use a query to compare the two
columns and find matches.

However, I only want to compare the first 5 letters of
each company name, because I know that companies may be
spelled a little differently at the end.

For example, "Microsoft" might appear in one table
and "Microsoft, Inc." might appear in another table.
If I
force the query to only return exact names, I won't be
able to catch this match.

How do I tell the query to compare only the first 5
letters of the fields in each column?

Many thanks,
Dave Redmond


.
Click to expand...

Instead of having us play 20 questions, why don't you copy and paste
the exact Where clause you are using.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
Click to expand...
 
Gary is correct.

--

Ken Snell
<MS ACCESS MVP>

Gary Walter said:
Hi Dave,

PMFBI

you only need one column

Field: MatchMe: Left([Current Clients].[Company 1],5)
Table:
Sort: N/A
Show: unchecked
Criteria: Left([Potential Clients].[Company 2],5)



Fred and Ken,

Thanks for your help. Here's the Where clause.

Query in Design View. I dragged two fields, one from each
table, down to the lower grid.

FIRST BOX:
Field: Company 1
Table: Current Clients
Sort: N/A
Show: checked
Criteria: Left([Current Clients]![Company 1],5)

SECOND BOX:
Field: Company 2
Table: Potential Clients
Sort: N/A
Show: checked
Criteria: Left([Potential Clients]![Company 2],5)

Ken, is this what you mean by using "the Left function on
both of the fields that you're comparing"?

Any help would be appreciated.

Thanks,
Dave Redmond
-----Original Message-----
On Fri, 25 Jun 2004 17:32:16 -0700, Dave Redmond wrote:

Van,

I tried your suggestion. For some reason, the Left()
function only returns company names that have less than 5
characters total.

For example, it returned a match between "TSI" and "TSI,"
but it did not return a match between "Microsoft"
and "Microsoft."

How can I improve this function to return company names
containing the same first 5 characters but which have
longer company names?

Thanks,
Dave

-----Original Message-----
You can use the Left() function to truncate the Field
values before
comparing them. For example:

Left([YourField], 5) will truncate your Field values to
the first 5
characters.

--
HTH
Van T. Dinh
MVP (Access)




"Dave Redmond" <[email protected]>
wrote in message
I'm sure there's a simple answer to this question. I'm
new to Access.

I have 2 tables, each with a column titled "Company
Name." I would like to use a query to compare the two
columns and find matches.

However, I only want to compare the first 5 letters of
each company name, because I know that companies may be
spelled a little differently at the end.

For example, "Microsoft" might appear in one table
and "Microsoft, Inc." might appear in another table.
If I
force the query to only return exact names, I won't be
able to catch this match.

How do I tell the query to compare only the first 5
letters of the fields in each column?

Many thanks,
Dave Redmond


.


Instead of having us play 20 questions, why don't you copy and paste
the exact Where clause you are using.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
Click to expand...
Click to expand...
 
Gary and Ken,

Thanks for the help.

However, it looks like that expression is still returning
only fields that match exactly.

If one field has "Microsoft Inc." and the other field
has "Microsoft Corp.", it's not catching the match. My
idea was that by focusing on the first 5 characters, the
idea query would see that both fields start with "Micro"
and would pull that record.

What do you think?

Thanks,
Dave Redmond

-----Original Message-----
Gary is correct.

--

Ken Snell
<MS ACCESS MVP>

Hi Dave,

PMFBI

you only need one column

Field: MatchMe: Left([Current Clients].[Company 1],5)
Table:
Sort: N/A
Show: unchecked
Criteria: Left([Potential Clients].[Company 2],5)



Fred and Ken,

Thanks for your help. Here's the Where clause.

Query in Design View. I dragged two fields, one from each
table, down to the lower grid.

FIRST BOX:
Field: Company 1
Table: Current Clients
Sort: N/A
Show: checked
Criteria: Left([Current Clients]![Company 1],5)

SECOND BOX:
Field: Company 2
Table: Potential Clients
Sort: N/A
Show: checked
Criteria: Left([Potential Clients]![Company 2],5)

Ken, is this what you mean by using "the Left function on
both of the fields that you're comparing"?

Any help would be appreciated.

Thanks,
Dave Redmond

-----Original Message-----
Van,

I tried your suggestion. For some reason, the Left ()
function only returns company names that have less
than 5
characters total.

For example, it returned a match between "TSI"
and "TSI,"
but it did not return a match between "Microsoft"
and "Microsoft."

How can I improve this function to return company
names
containing the same first 5 characters but which have
longer company names?

Thanks,
Dave

-----Original Message-----
You can use the Left() function to truncate the Field
values before
comparing them. For example:

Left([YourField], 5) will truncate your Field values
to
the first 5
characters.

--
HTH
Van T. Dinh
MVP (Access)




"Dave Redmond"
Click to expand...
wrote in message
I'm sure there's a simple answer to this question.
I'm
new to Access.

I have 2 tables, each with a column titled "Company
Name." I would like to use a query to compare the two
columns and find matches.

However, I only want to compare the first 5 letters
of
each company name, because I know that companies may
be
spelled a little differently at the end.

For example, "Microsoft" might appear in one table
and "Microsoft, Inc." might appear in another table.
If I
force the query to only return exact names, I won't
be
able to catch this match.

How do I tell the query to compare only the first 5
letters of the fields in each column?

Many thanks,
Dave Redmond


.


Instead of having us play 20 questions, why don't you
copy and paste
the exact Where clause you are using.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
Click to expand...


.
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
"Dave Redmond" worte
Gary and Ken,

Thanks for the help.

However, it looks like that expression is still returning
only fields that match exactly.

If one field has "Microsoft Inc." and the other field
has "Microsoft Corp.", it's not catching the match. My
idea was that by focusing on the first 5 characters, the
idea query would see that both fields start with "Micro"
and would pull that record.

What do you think?

Thanks,
Dave Redmond
Click to expand...
Hi Dave,

You must have an ON clause that is making
this WHERE criteria irrelevant.

What is the full SQL of your current query?

try

SELECT
[Current Clients].*,
[Potential Clients].*
FROM
[Current Clients]
INNER JOIN
[Potential Clients]
ON
Left([Current Clients].[Company 1],5)=Left([Potential Clients].[Company 2],5)
 
Post the SQL of the query. Open the query in design view, change to SQL view
(see toolbar icon on top left), copy the entire text that you'll see there,
and post it here.

--

Ken Snell
<MS ACCESS MVP>

Dave Redmond said:
Gary and Ken,

Thanks for the help.

However, it looks like that expression is still returning
only fields that match exactly.

If one field has "Microsoft Inc." and the other field
has "Microsoft Corp.", it's not catching the match. My
idea was that by focusing on the first 5 characters, the
idea query would see that both fields start with "Micro"
and would pull that record.

What do you think?

Thanks,
Dave Redmond

-----Original Message-----
Gary is correct.

--

Ken Snell
<MS ACCESS MVP>

Hi Dave,

PMFBI

you only need one column

Field: MatchMe: Left([Current Clients].[Company 1],5)
Table:
Sort: N/A
Show: unchecked
Criteria: Left([Potential Clients].[Company 2],5)



Fred and Ken,

Thanks for your help. Here's the Where clause.

Query in Design View. I dragged two fields, one from each
table, down to the lower grid.

FIRST BOX:
Field: Company 1
Table: Current Clients
Sort: N/A
Show: checked
Criteria: Left([Current Clients]![Company 1],5)

SECOND BOX:
Field: Company 2
Table: Potential Clients
Sort: N/A
Show: checked
Criteria: Left([Potential Clients]![Company 2],5)

Ken, is this what you mean by using "the Left function on
both of the fields that you're comparing"?

Any help would be appreciated.

Thanks,
Dave Redmond

-----Original Message-----
Van,

I tried your suggestion. For some reason, the Left ()
function only returns company names that have less
than 5
characters total.

For example, it returned a match between "TSI"
and "TSI,"
but it did not return a match between "Microsoft"
and "Microsoft."

How can I improve this function to return company
names
containing the same first 5 characters but which have
longer company names?

Thanks,
Dave

-----Original Message-----
You can use the Left() function to truncate the Field
values before
comparing them. For example:

Left([YourField], 5) will truncate your Field values
to
the first 5
characters.

--
HTH
Van T. Dinh
MVP (Access)




"Dave Redmond"
Click to expand...
wrote in message
I'm sure there's a simple answer to this question.
I'm
new to Access.

I have 2 tables, each with a column titled "Company
Name." I would like to use a query to compare the two
columns and find matches.

However, I only want to compare the first 5 letters
of
each company name, because I know that companies may
be
spelled a little differently at the end.

For example, "Microsoft" might appear in one table
and "Microsoft, Inc." might appear in another table.
If I
force the query to only return exact names, I won't
be
able to catch this match.

How do I tell the query to compare only the first 5
letters of the fields in each column?

Many thanks,
Dave Redmond


.


Instead of having us play 20 questions, why don't you
copy and paste
the exact Where clause you are using.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...


.
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
Here's the SQL. Note that the table and field names are
not exactly the same as the ones I earlier mentioned (I
had simplified them for my example).

SELECT Left([Companies in Redmond Territory].[Company
Name],5) AS MatchMe
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON [Companies in Redmond Territory].[Company
Name] = [Current Clients].Company
WHERE (((Left([Companies in Redmond Territory].[Company
Name],5))=Left([Current Clients].[Company],5)));

Thanks again for your help,
Dave

-----Original Message-----
Post the SQL of the query. Open the query in design view, change to SQL view
(see toolbar icon on top left), copy the entire text that you'll see there,
and post it here.

--

Ken Snell
<MS ACCESS MVP>

Gary and Ken,

Thanks for the help.

However, it looks like that expression is still returning
only fields that match exactly.

If one field has "Microsoft Inc." and the other field
has "Microsoft Corp.", it's not catching the match. My
idea was that by focusing on the first 5 characters, the
idea query would see that both fields start with "Micro"
and would pull that record.

What do you think?

Thanks,
Dave Redmond

-----Original Message-----
Gary is correct.

--

Ken Snell
<MS ACCESS MVP>

Hi Dave,

PMFBI

you only need one column

Field: MatchMe: Left([Current Clients].[Company 1],5)
Table:
Sort: N/A
Show: unchecked
Criteria: Left([Potential Clients].[Company 2],5)



Fred and Ken,

Thanks for your help. Here's the Where clause.

Query in Design View. I dragged two fields, one
Click to expand...
from
each
table, down to the lower grid.

FIRST BOX:
Field: Company 1
Table: Current Clients
Sort: N/A
Show: checked
Criteria: Left([Current Clients]![Company 1],5)

SECOND BOX:
Field: Company 2
Table: Potential Clients
Sort: N/A
Show: checked
Criteria: Left([Potential Clients]![Company 2],5)

Ken, is this what you mean by using "the Left function on
both of the fields that you're comparing"?

Any help would be appreciated.

Thanks,
Dave Redmond

-----Original Message-----
Van,

I tried your suggestion. For some reason, the
Click to expand...
Left
()
function only returns company names that have less
than 5
characters total.

For example, it returned a match between "TSI"
and "TSI,"
but it did not return a match between "Microsoft"
and "Microsoft."

How can I improve this function to return company
names
containing the same first 5 characters but
Click to expand...
which
have
longer company names?

Thanks,
Dave

-----Original Message-----
You can use the Left() function to truncate the Field
values before
comparing them. For example:

Left([YourField], 5) will truncate your Field values
to
the first 5
characters.

--
HTH
Van T. Dinh
MVP (Access)




"Dave Redmond"
Click to expand...
wrote in message
I'm sure there's a simple answer to this question.
I'm
new to Access.

I have 2 tables, each with a column titled "Company
Name." I would like to use a query to compare the two
columns and find matches.

However, I only want to compare the first 5 letters
of
each company name, because I know that companies may
be
spelled a little differently at the end.

For example, "Microsoft" might appear in one table
and "Microsoft, Inc." might appear in another table.
If I
force the query to only return exact names, I won't
be
able to catch this match.

How do I tell the query to compare only the first 5
letters of the fields in each column?

Many thanks,
Dave Redmond


.


Instead of having us play 20 questions, why don't you
copy and paste
the exact Where clause you are using.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.





.
Click to expand...
Click to expand...


.
Click to expand...
Click to expand...
 
OK - slight changes to your SQL (you need to use a non-equi join query).
Note that you will NOT be able to see this query in design view -- only in
SQL view. So don't try switching to design view after you paste this into
the SQL window and save it.


SELECT Left([Companies in Redmond Territory].[Company
Name],5) AS MatchMe
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON Left([Companies in Redmond Territory].[Company
Name],5))=Left([Current Clients].[Company],5);



If you want to show both companies' full names:

SELECT [Companies in Redmond Territory].[Company
Name], [Current Clients].[Company]
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON Left([Companies in Redmond Territory].[Company
Name],5))=Left([Current Clients].[Company],5);

--

Ken Snell
<MS ACCESS MVP>


Dave Redmond said:
Here's the SQL. Note that the table and field names are
not exactly the same as the ones I earlier mentioned (I
had simplified them for my example).

SELECT Left([Companies in Redmond Territory].[Company
Name],5) AS MatchMe
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON [Companies in Redmond Territory].[Company
Name] = [Current Clients].Company
WHERE (((Left([Companies in Redmond Territory].[Company
Name],5))=Left([Current Clients].[Company],5)));

Thanks again for your help,
Dave

-----Original Message-----
Post the SQL of the query. Open the query in design view, change to SQL view
(see toolbar icon on top left), copy the entire text that you'll see there,
and post it here.

--

Ken Snell
<MS ACCESS MVP>

Gary and Ken,

Thanks for the help.

However, it looks like that expression is still returning
only fields that match exactly.

If one field has "Microsoft Inc." and the other field
has "Microsoft Corp.", it's not catching the match. My
idea was that by focusing on the first 5 characters, the
idea query would see that both fields start with "Micro"
and would pull that record.

What do you think?

Thanks,
Dave Redmond


-----Original Message-----
Gary is correct.

--

Ken Snell
<MS ACCESS MVP>

message
Hi Dave,

PMFBI

you only need one column

Field: MatchMe: Left([Current Clients].[Company 1],5)
Table:
Sort: N/A
Show: unchecked
Criteria: Left([Potential Clients].[Company 2],5)



Fred and Ken,

Thanks for your help. Here's the Where clause.

Query in Design View. I dragged two fields, one from
each
table, down to the lower grid.

FIRST BOX:
Field: Company 1
Table: Current Clients
Sort: N/A
Show: checked
Criteria: Left([Current Clients]![Company 1],5)

SECOND BOX:
Field: Company 2
Table: Potential Clients
Sort: N/A
Show: checked
Criteria: Left([Potential Clients]![Company 2],5)

Ken, is this what you mean by using "the Left
function on
both of the fields that you're comparing"?

Any help would be appreciated.

Thanks,
Dave Redmond

-----Original Message-----
Van,

I tried your suggestion. For some reason, the Left
()
function only returns company names that have less
than 5
characters total.

For example, it returned a match between "TSI"
and "TSI,"
but it did not return a match between "Microsoft"
and "Microsoft."

How can I improve this function to return company
names
containing the same first 5 characters but which
have
longer company names?

Thanks,
Dave

-----Original Message-----
You can use the Left() function to truncate the
Field
values before
comparing them. For example:

Left([YourField], 5) will truncate your Field
values
to
the first 5
characters.

--
HTH
Van T. Dinh
MVP (Access)




"Dave Redmond"
<[email protected]>
wrote in message
I'm sure there's a simple answer to this
question.
I'm
new to Access.

I have 2 tables, each with a column
titled "Company
Name." I would like to use a query to compare
the two
columns and find matches.

However, I only want to compare the first 5
letters
of
each company name, because I know that
companies may
be
spelled a little differently at the end.

For example, "Microsoft" might appear in one
table
and "Microsoft, Inc." might appear in another
table.
If I
force the query to only return exact names, I
won't
be
able to catch this match.

How do I tell the query to compare only the
first 5
letters of the fields in each column?

Many thanks,
Dave Redmond


.


Instead of having us play 20 questions, why don't
you
copy and paste
the exact Where clause you are using.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.





.
Click to expand...


.
Click to expand...
Click to expand...
 
Ken,

When I paste either into the SQL window and click save, I
get a message that says, "Join expression not supported,"
and it highlights Left([Companies in Redmond Territory].
[CompanyName],5).

Sorry for the hassle. Any suggestions?

Thanks,
Dave
-----Original Message-----
OK - slight changes to your SQL (you need to use a non- equi join query).
Note that you will NOT be able to see this query in design view -- only in
SQL view. So don't try switching to design view after you paste this into
the SQL window and save it.


SELECT Left([Companies in Redmond Territory].[Company
Name],5) AS MatchMe
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON Left([Companies in Redmond Territory]. [Company
Name],5))=Left([Current Clients].[Company],5);



If you want to show both companies' full names:

SELECT [Companies in Redmond Territory].[Company
Name], [Current Clients].[Company]
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON Left([Companies in Redmond Territory]. [Company
Name],5))=Left([Current Clients].[Company],5);

--

Ken Snell
<MS ACCESS MVP>


Here's the SQL. Note that the table and field names are
not exactly the same as the ones I earlier mentioned (I
had simplified them for my example).

SELECT Left([Companies in Redmond Territory].[Company
Name],5) AS MatchMe
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON [Companies in Redmond Territory].[Company
Name] = [Current Clients].Company
WHERE (((Left([Companies in Redmond Territory].[Company
Name],5))=Left([Current Clients].[Company],5)));

Thanks again for your help,
Dave

-----Original Message-----
Post the SQL of the query. Open the query in design view, change to SQL view
(see toolbar icon on top left), copy the entire text that you'll see there,
and post it here.

--

Ken Snell
<MS ACCESS MVP>

Gary and Ken,

Thanks for the help.

However, it looks like that expression is still returning
only fields that match exactly.

If one field has "Microsoft Inc." and the other field
has "Microsoft Corp.", it's not catching the match. My
idea was that by focusing on the first 5 characters, the
idea query would see that both fields start with "Micro"
and would pull that record.

What do you think?

Thanks,
Dave Redmond


-----Original Message-----
Gary is correct.

--

Ken Snell
<MS ACCESS MVP>

message
Hi Dave,

PMFBI

you only need one column

Field: MatchMe: Left([Current Clients].[Company 1],5)
Table:
Sort: N/A
Show: unchecked
Criteria: Left([Potential Clients].[Company 2],5)



Fred and Ken,

Thanks for your help. Here's the Where clause.

Query in Design View. I dragged two fields, one from
each
table, down to the lower grid.

FIRST BOX:
Field: Company 1
Table: Current Clients
Sort: N/A
Show: checked
Criteria: Left([Current Clients]![Company 1],5)

SECOND BOX:
Field: Company 2
Table: Potential Clients
Sort: N/A
Show: checked
Criteria: Left([Potential Clients]![Company 2],5)

Ken, is this what you mean by using "the Left
function on
both of the fields that you're comparing"?

Any help would be appreciated.

Thanks,
Dave Redmond

-----Original Message-----
Van,

I tried your suggestion. For some reason,
Click to expand...
the
Left
()
function only returns company names that
Click to expand...
have
less
than 5
characters total.

For example, it returned a match between "TSI"
and "TSI,"
but it did not return a match between "Microsoft"
and "Microsoft."

How can I improve this function to return company
names
containing the same first 5 characters but which
have
longer company names?

Thanks,
Dave

-----Original Message-----
You can use the Left() function to truncate the
Field
values before
comparing them. For example:

Left([YourField], 5) will truncate your Field
values
to
the first 5
characters.

--
HTH
Van T. Dinh
MVP (Access)




"Dave Redmond"
<[email protected]>
wrote in message
[email protected]...
I'm sure there's a simple answer to this
question.
I'm
new to Access.

I have 2 tables, each with a column
titled "Company
Name." I would like to use a query to compare
the two
columns and find matches.

However, I only want to compare the first 5
letters
of
each company name, because I know that
companies may
be
spelled a little differently at the end.

For example, "Microsoft" might appear in one
table
and "Microsoft, Inc." might appear in another
table.
If I
force the query to only return exact names, I
won't
be
able to catch this match.

How do I tell the query to compare only the
first 5
letters of the fields in each column?

Many thanks,
Dave Redmond


.


Instead of having us play 20 questions, why don't
you
copy and paste
the exact Where clause you are using.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.





.



.
Click to expand...
Click to expand...


.
Click to expand...
 
I think I left in an extra ) before the = sign:

SELECT Left([Companies in Redmond Territory].[Company
Name],5) AS MatchMe
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON Left([Companies in Redmond Territory].[Company
Name],5)=Left([Current Clients].[Company],5);


--

Ken Snell
<MS ACCESS MVP>

Dave Redmond said:
Ken,

When I paste either into the SQL window and click save, I
get a message that says, "Join expression not supported,"
and it highlights Left([Companies in Redmond Territory].
[CompanyName],5).

Sorry for the hassle. Any suggestions?

Thanks,
Dave
-----Original Message-----
OK - slight changes to your SQL (you need to use a non- equi join query).
Note that you will NOT be able to see this query in design view -- only in
SQL view. So don't try switching to design view after you paste this into
the SQL window and save it.


SELECT Left([Companies in Redmond Territory].[Company
Name],5) AS MatchMe
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON Left([Companies in Redmond Territory]. [Company
Name],5))=Left([Current Clients].[Company],5);



If you want to show both companies' full names:

SELECT [Companies in Redmond Territory].[Company
Name], [Current Clients].[Company]
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON Left([Companies in Redmond Territory]. [Company
Name],5))=Left([Current Clients].[Company],5);

--

Ken Snell
<MS ACCESS MVP>


Here's the SQL. Note that the table and field names are
not exactly the same as the ones I earlier mentioned (I
had simplified them for my example).

SELECT Left([Companies in Redmond Territory].[Company
Name],5) AS MatchMe
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON [Companies in Redmond Territory].[Company
Name] = [Current Clients].Company
WHERE (((Left([Companies in Redmond Territory].[Company
Name],5))=Left([Current Clients].[Company],5)));

Thanks again for your help,
Dave


-----Original Message-----
Post the SQL of the query. Open the query in design
view, change to SQL view
(see toolbar icon on top left), copy the entire text
that you'll see there,
and post it here.

--

Ken Snell
<MS ACCESS MVP>

"Dave Redmond" <[email protected]>
wrote in message
Gary and Ken,

Thanks for the help.

However, it looks like that expression is still
returning
only fields that match exactly.

If one field has "Microsoft Inc." and the other field
has "Microsoft Corp.", it's not catching the match. My
idea was that by focusing on the first 5 characters,
the
idea query would see that both fields start
with "Micro"
and would pull that record.

What do you think?

Thanks,
Dave Redmond


-----Original Message-----
Gary is correct.

--

Ken Snell
<MS ACCESS MVP>

message
Hi Dave,

PMFBI

you only need one column

Field: MatchMe: Left([Current Clients].[Company
1],5)
Table:
Sort: N/A
Show: unchecked
Criteria: Left([Potential Clients].[Company 2],5)



Fred and Ken,

Thanks for your help. Here's the Where clause.

Query in Design View. I dragged two fields, one
from
each
table, down to the lower grid.

FIRST BOX:
Field: Company 1
Table: Current Clients
Sort: N/A
Show: checked
Criteria: Left([Current Clients]![Company 1],5)

SECOND BOX:
Field: Company 2
Table: Potential Clients
Sort: N/A
Show: checked
Criteria: Left([Potential Clients]![Company 2],5)

Ken, is this what you mean by using "the Left
function on
both of the fields that you're comparing"?

Any help would be appreciated.

Thanks,
Dave Redmond

-----Original Message-----
Van,

I tried your suggestion. For some reason, the
Left
()
function only returns company names that have
less
than 5
characters total.

For example, it returned a match between "TSI"
and "TSI,"
but it did not return a match
between "Microsoft"
and "Microsoft."

How can I improve this function to return
company
names
containing the same first 5 characters but
which
have
longer company names?

Thanks,
Dave

-----Original Message-----
You can use the Left() function to truncate the
Field
values before
comparing them. For example:

Left([YourField], 5) will truncate your Field
values
to
the first 5
characters.

--
HTH
Van T. Dinh
MVP (Access)




"Dave Redmond"
<[email protected]>
wrote in message
[email protected]...
I'm sure there's a simple answer to this
question.
I'm
new to Access.

I have 2 tables, each with a column
titled "Company
Name." I would like to use a query to compare
the two
columns and find matches.

However, I only want to compare the first 5
letters
of
each company name, because I know that
companies may
be
spelled a little differently at the end.

For example, "Microsoft" might appear in one
table
and "Microsoft, Inc." might appear in another
table.
If I
force the query to only return exact names, I
won't
be
able to catch this match.

How do I tell the query to compare only the
first 5
letters of the fields in each column?

Many thanks,
Dave Redmond


.


Instead of having us play 20 questions, why don't
you
copy and paste
the exact Where clause you are using.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.





.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Ken, unfortunately it's giving me "Syntax error in JOIN
operation", and then it highlights the last instance of
[Current Clients].

-----Original Message-----
I think I left in an extra ) before the = sign:

SELECT Left([Companies in Redmond Territory].[Company
Name],5) AS MatchMe
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON Left([Companies in Redmond Territory]. [Company
Name],5)=Left([Current Clients].[Company],5);


--

Ken Snell
<MS ACCESS MVP>

Ken,

When I paste either into the SQL window and click save, I
get a message that says, "Join expression not supported,"
and it highlights Left([Companies in Redmond Territory].
[CompanyName],5).

Sorry for the hassle. Any suggestions?

Thanks,
Dave
-----Original Message-----
OK - slight changes to your SQL (you need to use a
Click to expand...
non-
equi join query).
Note that you will NOT be able to see this query in design view -- only in
SQL view. So don't try switching to design view after you paste this into
the SQL window and save it.


SELECT Left([Companies in Redmond Territory].[Company
Name],5) AS MatchMe
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON Left([Companies in Redmond Territory]. [Company
Name],5))=Left([Current Clients].[Company],5);



If you want to show both companies' full names:

SELECT [Companies in Redmond Territory].[Company
Name], [Current Clients].[Company]
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON Left([Companies in Redmond Territory]. [Company
Name],5))=Left([Current Clients].[Company],5);

--

Ken Snell
<MS ACCESS MVP>


Here's the SQL. Note that the table and field names are
not exactly the same as the ones I earlier mentioned (I
had simplified them for my example).

SELECT Left([Companies in Redmond Territory]. [Company
Name],5) AS MatchMe
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON [Companies in Redmond Territory]. [Company
Name] = [Current Clients].Company
WHERE (((Left([Companies in Redmond Territory]. [Company
Name],5))=Left([Current Clients].[Company],5)));

Thanks again for your help,
Dave


-----Original Message-----
Post the SQL of the query. Open the query in design
view, change to SQL view
(see toolbar icon on top left), copy the entire text
that you'll see there,
and post it here.

--

Ken Snell
<MS ACCESS MVP>

"Dave Redmond"
Click to expand...
wrote in message
Gary and Ken,

Thanks for the help.

However, it looks like that expression is still
returning
only fields that match exactly.

If one field has "Microsoft Inc." and the other field
has "Microsoft Corp.", it's not catching the
Click to expand...
match.
My
idea was that by focusing on the first 5 characters,
the
idea query would see that both fields start
with "Micro"
and would pull that record.

What do you think?

Thanks,
Dave Redmond


-----Original Message-----
Gary is correct.

--

Ken Snell
<MS ACCESS MVP>

message
Hi Dave,

PMFBI

you only need one column

Field: MatchMe: Left([Current Clients]. [Company
1],5)
Table:
Sort: N/A
Show: unchecked
Criteria: Left([Potential Clients].[Company 2],5)



Fred and Ken,

Thanks for your help. Here's the Where clause.

Query in Design View. I dragged two fields, one
from
each
table, down to the lower grid.

FIRST BOX:
Field: Company 1
Table: Current Clients
Sort: N/A
Show: checked
Criteria: Left([Current Clients]![Company 1],5)

SECOND BOX:
Field: Company 2
Table: Potential Clients
Sort: N/A
Show: checked
Criteria: Left([Potential Clients]![Company 2],5)

Ken, is this what you mean by using "the Left
function on
both of the fields that you're comparing"?

Any help would be appreciated.

Thanks,
Dave Redmond

-----Original Message-----
Van,

I tried your suggestion. For some reason, the
Left
()
function only returns company names that have
less
than 5
characters total.

For example, it returned a match between "TSI"
and "TSI,"
but it did not return a match
between "Microsoft"
and "Microsoft."

How can I improve this function to return
company
names
containing the same first 5 characters but
which
have
longer company names?

Thanks,
Dave

-----Original Message-----
You can use the Left() function to
Click to expand...
truncate
the
Field
values before
comparing them. For example:

Left([YourField], 5) will truncate your Field
values
to
the first 5
characters.

--
HTH
Van T. Dinh
MVP (Access)




"Dave Redmond"
<[email protected]>
wrote in message
[email protected]...
I'm sure there's a simple answer to this
question.
I'm
new to Access.

I have 2 tables, each with a column
titled "Company
Name." I would like to use a query to compare
the two
columns and find matches.

However, I only want to compare the
Click to expand...
first
5
letters
of
each company name, because I know that
companies may
be
spelled a little differently at the end.

For example, "Microsoft" might appear
Click to expand...
in
one
table
and "Microsoft, Inc." might appear in another
table.
If I
force the query to only return exact names, I
won't
be
able to catch this match.

How do I tell the query to compare only the
first 5
letters of the fields in each column?

Many thanks,
Dave Redmond


.


Instead of having us play 20 questions, why don't
you
copy and paste
the exact Where clause you are using.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.





.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Post the actual *full* SQL that you have in the query. Something is not
right with what you have, as this syntax works for me in testing.

--

Ken Snell
<MS ACCESS MVP>

Dave Redmond said:
Ken, unfortunately it's giving me "Syntax error in JOIN
operation", and then it highlights the last instance of
[Current Clients].

-----Original Message-----
I think I left in an extra ) before the = sign:

SELECT Left([Companies in Redmond Territory].[Company
Name],5) AS MatchMe
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON Left([Companies in Redmond Territory]. [Company
Name],5)=Left([Current Clients].[Company],5);


--

Ken Snell
<MS ACCESS MVP>

Ken,

When I paste either into the SQL window and click save, I
get a message that says, "Join expression not supported,"
and it highlights Left([Companies in Redmond Territory].
[CompanyName],5).

Sorry for the hassle. Any suggestions?

Thanks,
Dave

-----Original Message-----
OK - slight changes to your SQL (you need to use a non-
equi join query).
Note that you will NOT be able to see this query in
design view -- only in
SQL view. So don't try switching to design view after
you paste this into
the SQL window and save it.


SELECT Left([Companies in Redmond Territory].[Company
Name],5) AS MatchMe
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON Left([Companies in Redmond Territory].
[Company
Name],5))=Left([Current Clients].[Company],5);



If you want to show both companies' full names:

SELECT [Companies in Redmond Territory].[Company
Name], [Current Clients].[Company]
FROM [Companies in Redmond Territory] INNER JOIN [Current
Clients] ON Left([Companies in Redmond Territory].
[Company
Name],5))=Left([Current Clients].[Company],5);

--

Ken Snell
<MS ACCESS MVP>


"Dave Redmond" <[email protected]>
wrote in message
Here's the SQL. Note that the table and field names are
not exactly the same as the ones I earlier mentioned (I
had simplified them for my example).

SELECT Left([Companies in Redmond Territory]. [Company
Name],5) AS MatchMe
FROM [Companies in Redmond Territory] INNER JOIN
[Current
Clients] ON [Companies in Redmond Territory]. [Company
Name] = [Current Clients].Company
WHERE (((Left([Companies in Redmond Territory]. [Company
Name],5))=Left([Current Clients].[Company],5)));

Thanks again for your help,
Dave


-----Original Message-----
Post the SQL of the query. Open the query in design
view, change to SQL view
(see toolbar icon on top left), copy the entire text
that you'll see there,
and post it here.

--

Ken Snell
<MS ACCESS MVP>

"Dave Redmond"
Click to expand...
wrote in message
Gary and Ken,

Thanks for the help.

However, it looks like that expression is still
returning
only fields that match exactly.

If one field has "Microsoft Inc." and the other
field
has "Microsoft Corp.", it's not catching the match.
My
idea was that by focusing on the first 5 characters,
the
idea query would see that both fields start
with "Micro"
and would pull that record.

What do you think?

Thanks,
Dave Redmond


-----Original Message-----
Gary is correct.

--

Ken Snell
<MS ACCESS MVP>

"Gary Walter" <[email protected]>
wrote in
message
Hi Dave,

PMFBI

you only need one column

Field: MatchMe: Left([Current Clients]. [Company
1],5)
Table:
Sort: N/A
Show: unchecked
Criteria: Left([Potential Clients].[Company 2],5)



Fred and Ken,

Thanks for your help. Here's the Where clause.

Query in Design View. I dragged two fields, one
from
each
table, down to the lower grid.

FIRST BOX:
Field: Company 1
Table: Current Clients
Sort: N/A
Show: checked
Criteria: Left([Current Clients]![Company 1],5)

SECOND BOX:
Field: Company 2
Table: Potential Clients
Sort: N/A
Show: checked
Criteria: Left([Potential Clients]![Company
2],5)

Ken, is this what you mean by using "the Left
function on
both of the fields that you're comparing"?

Any help would be appreciated.

Thanks,
Dave Redmond

-----Original Message-----
On Fri, 25 Jun 2004 17:32:16 -0700, Dave
Redmond
wrote:

Van,

I tried your suggestion. For some reason,
the
Left
()
function only returns company names that
have
less
than 5
characters total.

For example, it returned a match
between "TSI"
and "TSI,"
but it did not return a match
between "Microsoft"
and "Microsoft."

How can I improve this function to return
company
names
containing the same first 5 characters but
which
have
longer company names?

Thanks,
Dave

-----Original Message-----
You can use the Left() function to truncate
the
Field
values before
comparing them. For example:

Left([YourField], 5) will truncate your
Field
values
to
the first 5
characters.

--
HTH
Van T. Dinh
MVP (Access)




"Dave Redmond"
<[email protected]>
wrote in message
[email protected]...
I'm sure there's a simple answer to this
question.
I'm
new to Access.

I have 2 tables, each with a column
titled "Company
Name." I would like to use a query to
compare
the two
columns and find matches.

However, I only want to compare the first
5
letters
of
each company name, because I know that
companies may
be
spelled a little differently at the end.

For example, "Microsoft" might appear in
one
table
and "Microsoft, Inc." might appear in
another
table.
If I
force the query to only return exact
names, I
won't
be
able to catch this match.

How do I tell the query to compare only
the
first 5
letters of the fields in each column?

Many thanks,
Dave Redmond


.


Instead of having us play 20 questions, why
don't
you
copy and paste
the exact Where clause you are using.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.





.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top