SQL 'IGNORE' ?
L at macmailbox.com
Wed Jun 23 15:17:56 PDT 2004
Thanks Gus, Paul, et al.
-I used Excel to swap all "." with "," (basically 'cause I could do it in
~1.6 seconds that way, no offense kings-of-sed).
-Then, pushed the dataset back into Access, where by using kplug's
suggestions, the data self-separated into nifty little fields (I don't know
how I missed this brilliant concept the first eighty times around).
- Next, using Gus' SQL SELECTs, I am left with very usable data.
To answer Paul's question regarding the type of data actually /in/ the
datasets, this is an actual line of data (numbers changed due to the
>Dataset #1: Dataset #2:
As Elvis would say, "ahThank you, ahThank you very much."
Oh, and BTW (for all you two-timers on the WIN2003 list), note I didn't even
/bother/ asking /that/ group. :-D
-> The problem is that you really only have two columns: a CHAR
-> column for the name and a CHAR column for the dotted
-> numbers. Comparisons using SQL will only result in using all
-> of the characters for the comparisons.
-> To get around this you will need to write a program that
-> does the comparisons for you, or you can rework the data
-> once for each table to explode each dotted number into its
-> own column to allow direct comparisons.
-> I was thinking that maybe a SUBSTRING in the SELECT
-> statement would work, but that's not specific enough. The
-> SQL SELECT statement would then go something like:
-> SELECT D1.* FROM dataset1 D1, dataset2 D2 WHERE
-> D1.Name=D2.Name AND D1.Field1=D2.Field1 AND
-> D1.Field2=D2.Field2 AND D1.Field3=D2.Field3 AND
-> D1.Field4=D2.Field4 AND D1.Field5=D2.Field5 AND
-> D1.Field6=D2.Field6 AND D1.Field7=D2.Field7
-> This will give you everything where all the fields match.
-> To get everything where they DON'T match you could do:
-> SELECT D1.*, D2.* FROM dataset1 D1, dataset2 D2 WHERE
-> D1.Name=D2.Name AND ( D1.Field1<>D2.Field1 OR
-> D1.Field2<>D2.Field2 OR D1.Field3<>D2.Field3 OR
-> D1.Field4<>D2.Field4 OR D1.Field5<>D2.Field5 OR
-> D1.Field6<>D2.Field6 OR D1.Field7<>D2.Field7 )
-> In Excel you can write a program in VBA (Visual Basic for
-> Access has its own language but it's still a form of Basic
-> (although it might be the same by now, my last reference is
-> Access 2.0). I could do something like this in Delphi in
-> about 10 lines of code. Pertinent functions in Basic are
-> InStr, Len, Left.
More information about the KPLUG-List