Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2009
    Thanked 0 Times in 0 Posts
    I have the following query:

    SELECT System.Vendor, System.Name, System.Address_1, System.Address_2, System.City, System.State, System.ZIP, System.Country, System.Classification, System.DateOff
    FROM System INNER JOIN Vendors ON System.Vendor = Vendors.Vendor
    WHERE (((System.Name)<>[Vendors].[VendorName])) OR (((System.Address_1)<>[Vendors].[Address1])) OR (((System.Address_2)<>[Vendors].[Address2])) OR (((System.City)<>[Vendors].[City])) OR (((System.State)<>[Vendors].[State])) OR (((System.ZIP)<>[Vendors].[Zip])) OR (((System.Country)<>[Vendors].[Country])) OR (((System.Classification)<>[Vendors].[Class])) OR (((System.DateOff)<>[Vendors].[DateOff]));

    I'd like to somehow take into account case when comparing the two tables. For example: Joe Smith versus JOE SMITH. At the moment, the record is not displayed. I'd like it to display if it is different. I then have a query that updates the vendor table with the record. Any insight would be much appreciated!

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Mt Macedon, Victoria, Australia
    Thanked 45 Times in 44 Posts
    So you have two tables with similar information ( System and Vendors), and you want to update the data Vendors to the data in Sytsem if any of the details are different?

    Why not just run the update query for all records? if they are the same nothing will be changed, and if they are different the update will take place.

    Are you saying that when you joing the tables on VendorName you don't get a match if the Case is different? That is not standard behaviour. (or are you saying the exact opposite?)
    You could run an Update query on both tables to convert all the Vendor Names to Proper Case

    Update VendorName to StrConv([VendorName],3)
    3 says to convert to Proper Case - first letter of each word is upper case, rest is lower.

    If you look up StrConv in VBA Help you can see the other alternatives.

    (PS: Presumably there is some good reason for having two tables with the same info...standard practice is to avoid this situation.)

  3. #3
    Star Lounger
    Join Date
    Mar 2010
    Winston-Salem, NC USA
    Thanked 10 Times in 9 Posts
    You didn't specify which type of database you were using. Here's how you do a case sensitive match in TSQL;
    Do your part!!! Report SPAM to

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts