case sensitive join query

[rbrem]

Hello,


I want to join a database of buyers to their purchases. I am joining on a variable called ID. The IDs are a series of numbers and letters. The letters contain different cases. This is an example:

003aBcD
003ABcE

The IDs are unique based on case, so 003Aab and 003AaB are two different people, but Access recognizes them as duplicates. How do I create a join query that matches the buyers to the purchases and groups them based on the proper case sensitive Id? The case difference is not necessarily in the same position of the Id, so I don't think a string function will work; i.e. 003Aab and 003AaB or 003aAb and 003aab.

Thank you.

[orange]

I think you are making your life much more difficult than needs be. Why this string to identify records?
Why not use autonumbers for PKs?

You can do a binary compare
see http://msdn.microsoft.com/en-us/libr...=vs.60%29.aspx

[JoeM]

I agree with Orange, this design is going to be a headache that makes life miserable for you.

If you really insist on doing it this way, I suppose you could do something like create a UDF to convert the IDs into a different ID number. I was thinking of converting each character to the ASCII code representation of that character surrounded by an underscore. Then, once you have converted the IDs in each table in a query, you can join them on this new calculated field.

[rbrem]

Thanks. I wish I could take your advice, but this isn't my data and I can't change the ID naming system. Is there any way to do the join without creating new Id's?

[JoeM]

Is there any way to do the join without creating new Id's?

I was talking about replacing them, just converting them in a calculated field in a query so that they can be used in the manner that you want (003Aab is different than 003AaB).

[JoeM]

That UDF might look like this:

Public Function ConvertID(OldID As String) As String

    Dim i As Long
    Dim WorkingID As String

    If Len(OldID) > 0 Then
        For i = 1 To Len(OldID)
            WorkingID = WorkingID & Asc(Mid(OldID, i, 1)) & "_"
        Next i
        ConvertID = Left(WorkingID, Len(WorkingID) - 1)
    End If
        
End Function

So create two separate queries for each table, and apply this function to the ID fields on each.
Then, you can join these two queries on these calculated fields.