Ways of Solving for Access Query Case Sensitivity/Insensitivity (1 Viewer)

[Isaac]

If I have two tables that need to be compared, on 15 columns which are shared between tables. The criteria is extremely simple. There is NO join between the tables. The only criteria is basically, where:
"
table1.column1 = table2.column1 or table1.column1 is null
and
table1.column2 = table2.column2 or table1.column2 is null"
and
table1.column3 = table2.column3 or table1.column3 is null"
"
...etc

(the goal is to sort of "explode" the values from table1: for each row in table1, there may be numerous matching rows from table2, we want all of them).

The query worked nice & fast, as I wish. It produces the required output, except:
The client just informed me that to be considered a "match", I MUST take case into account: Case matters.

I've come up with 3 possible approaches:

1. Switch the process to a VPS where he has SQL Server available, to use a COLLATE tag after each = comparison
2. Use StrComp() in the Access query, which is an awful solution and so slow it couldn't be used
3. When importing the input data, create 15 extra columns to denote the ASCI-characters-value of each value - then run an Access query to compare that normally, should run as fast as the original query. The import process might be slowed, but probably not NEARLY as slow as the overall slowing from #2

He's thinking of dropping the case sensitivity requirement, which would be nice.

However, are there approaches I've missed or haven't thought of?

 

[CJ_London]

a 4th method, similar to your asci idea, would be to hash values. It;s a technique I use for comparing records which I've found is fast enough. hash all the fields except the PK

 

[Isaac]

a 4th method, similar to your asci idea, would be to hash values. It;s a technique I use for comparing records which I've found is fast enough. hash all the fields except the PK

Thank you CJ, I'll start reading up