case sensitive join

[Guest]

I have a query joining 2 SQL 2000 database tables (ODBC connection) the
tables are both case sensitive, however when I return the results in the
query it is matching records where the capitalization is not the same. ie.
One table has Ab and the other table has records Ab and AB the query matches
both and returns double records. The database was created in Access 2000 and
is still in Access 2000 format, I am now running Access 2007. Has anyone else
encountered this?

 

[Guest]

You could try going into Design View and either checking the format of the
column to see if you can force the display to ignore case and see (and
display) your records of that column in a uniform format. You could also try
high-lighting the column (still in design view) to group your data with
totals. If you still get duplicate results, check your query results to see
if other data in your query returned in other colums with Ab and AB are
different. That would force this column to display seemingly duplicate
results.

Hope this helps

 

[Guest]

The problem is it is a 1 to 1 join. The join shouldn't be making a match.
"Ab" should not match "AB" in the second table. I know the fields are case
sensitive, becuase if I filter them individually "Ab" does not return result
"AB" This query functioned as intended when running Access 2000 and 2003.

 

[Guest]

Are there other fields that could be joined besides Ab and AB? It sounds like
the join properties are forcing the match between Ab and AB.

 

[John W. Vinson]

The problem is it is a 1 to 1 join. The join shouldn't be making a match.
"Ab" should not match "AB" in the second table. I know the fields are case
sensitive, becuase if I filter them individually "Ab" does not return result
"AB" This query functioned as intended when running Access 2000 and 2003.

That's very odd indeed, because Access 2000 and 2003 have *NEVER* been case
sensitive, and it's been quite difficult to force a case sensitive join. I
don't know what provision A2007 makes for such joins, but I'd suggest you
recheck how your previous versions implemented this!

John W. Vinson [MVP]

 

[Guest]

The tables are SQL tables linked into Access, and since SQL has been
configured to be case sensitive Access does not ignore that fact. Well it
didn't ignore it untill 2007. I know my tables are case sensitive becuase if
I filter them I have to capitalize correct.

 

[Michel Walsh]

Make a VIEW in MS SQL Server that will make the join. From Access, pump the
data from the MS SQL Server view, (or from a store procedure, or even from a
ODBC-direct query).


If you are using a dot-mdb file, JET is in charge and JET does not make
comparison case sensitive (by default). Jet has to be in charge, since the
linked tables can be from MS SQL Server, or from Excel, or from ... name
who. Since Jet is in charge, it asks to other 'engine' about their data, but
in a way Jet can understand (here, not case sensitive) since, consider for
a moment, the join may involves a table from MS SQL Server and a table from
Excel.

If you are using a dot-adp file, then MS SQL Server is in charge, and JET is
not present at all. Maybe you convert your dot-adp application into a new
dot-accdb file? That would explain what you described: before, MS SQL Server
was indeed, in charge, but now, it is Jet ! If so, again, have the join
being made by MS SQL Server, through a view, a stored proc, an ODBC-direct
query, ...


Vanderghast, Access MVP

 

[Alex Sharp]

You said - in your response to a question from last year - that it'd been
quite difficult to force a case sensitive join. Have you had any success
doing this?

 

[Michel Walsh]

Untested, but have you tried:

SELECT *
FROM a INNER JOIN b
ON 0 = StrComp(a.field1, b.field2, 0)



Vanderghast, Access MVP

 

[Tom van Stiphout]

Not too tough. Remember a join can be expressed two ways, once with a
table join expression, and once with a where-clause. I am using the
latter:
SELECT Employees.[First Name], Employees_1.[First Name],
StrComp([Employees].[First Name],[Employees_1].[First Name],0) AS
Expr1
FROM Employees, Employees AS Employees_1
WHERE (((StrComp([Employees].[First Name],[Employees_1].[First
Name],0))=0));

I added a few new employees with various casing of Fred's firstname,
and it showed the expected case-sensitive matches.

-Tom.
Microsoft Access MVP

 

[Alex Sharp]

Thanks that's interesting. I'm looking at differentiating between values in
imported data that have similar content but with different meanings, eg value
"137S" (which has a positive meaning) versus "137s" (which means the
opposite). Unfortunately the number / position of the characters will vary
with different code-use occasions (like "9B3LX" v. "9B3Lx") but I will
experiment with one alpha-numeric sequence see if I can get it to return the
right results first.

Not too tough. Remember a join can be expressed two ways, once with a
table join expression, and once with a where-clause. I am using the
latter:
SELECT Employees.[First Name], Employees_1.[First Name],
StrComp([Employees].[First Name],[Employees_1].[First Name],0) AS
Expr1
FROM Employees, Employees AS Employees_1
WHERE (((StrComp([Employees].[First Name],[Employees_1].[First
Name],0))=0));

I added a few new employees with various casing of Fred's firstname,
and it showed the expected case-sensitive matches.

-Tom.
Microsoft Access MVP

You said - in your response to a question from last year - that it'd been
quite difficult to force a case sensitive join. Have you had any success
doing this?

 

[John Spencer]

I had to test it. It works for me in Access 2003.

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

 

[Alex Sharp]

Success! Tested the StrComp function ok, and working throught the logic it
should be applicable on multiple occasions. Many thanks (and to John Spencer
whose version was a little more applicable to my scenario).

Alex.

Thanks that's interesting. I'm looking at differentiating between values in
imported data that have similar content but with different meanings, eg value
"137S" (which has a positive meaning) versus "137s" (which means the
opposite). Unfortunately the number / position of the characters will vary
with different code-use occasions (like "9B3LX" v. "9B3Lx") but I will
experiment with one alpha-numeric sequence see if I can get it to return the
right results first.

Not too tough. Remember a join can be expressed two ways, once with a
table join expression, and once with a where-clause. I am using the
latter:
SELECT Employees.[First Name], Employees_1.[First Name],
StrComp([Employees].[First Name],[Employees_1].[First Name],0) AS
Expr1
FROM Employees, Employees AS Employees_1
WHERE (((StrComp([Employees].[First Name],[Employees_1].[First
Name],0))=0));

I added a few new employees with various casing of Fred's firstname,
and it showed the expected case-sensitive matches.

-Tom.
Microsoft Access MVP

You said - in your response to a question from last year - that it'd been
quite difficult to force a case sensitive join. Have you had any success
doing this?

:

The problem is it is a 1 to 1 join. The join shouldn't be making a match.
"Ab" should not match "AB" in the second table. I know the fields are case
sensitive, becuase if I filter them individually "Ab" does not return result
"AB" This query functioned as intended when running Access 2000 and 2003.

That's very odd indeed, because Access 2000 and 2003 have *NEVER* been case
sensitive, and it's been quite difficult to force a case sensitive join. I
don't know what provision A2007 makes for such joins, but I'd suggest you
recheck how your previous versions implemented this!

John W. Vinson [MVP]