Sunday, February 12, 2012

beginner - case sensitive query

Hi

We are taking case sensitive data, and loading it into an Access 2000 database using an OBDC. We then use VB 6 to produce an SQL .exe file which manipulates the data into new tables for the CR.

We have a a select statement where we have
SELECT ....
INTO A_POS
FROM POS LEFT JOIN CHTAB ON POS.STATUS = CHTAB.STATUS

The trouble is the client has the codes "F" and "f" in the CHTAB.STATUS, so I am producing duplicate A_POS records for any POS record with a status of "F".

Any suggestions as to how I can check the case sensitivity in Access so I create clean tables?

Thanks.
_Do you want to make all "f" to "F"?
Post some sample data and the result you want|||Unfortuneately, we are a software house, so we can't go and demand our clients change the codes. One client had all their company structure codes in upper case, and when they changed the company structure, they kept all the same codes, but changed them to lower case. Thus on the database "MELB" and "melb" could appear in one historic view, and have totally different properties.
I have an Organisation chart file ORG, which is keyed by the field OFFICE. I also have an employee positions file, POS. One of its attributes is OFFICE. Both "MELB" and "melb" are valid, but distinct, office codes.
My select statment is:-

select ... from POS LEFT JOIN ORG WHERE POS.OFFICE = ORG.OFFICE

And I need to somehow tell the script that I want the matching to be case sensitive, so I only pick up the correct upper (or lower) case office, not both.

Thanks

No comments:

Post a Comment