SQL 'IGNORE' ?

Lon McPhail 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
nature):

	>Dataset #1:						Dataset #2:
	>_platform_entity_F14,2.1.225.4.2.1.2.1		F14,2.1.225.4.2.1.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

KPLUG /ROCKS!/


Cheers,
Lon





-> [snip]
-> 
-> 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 
-> Applications).
-> 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.
-> 
-> Gus




More information about the KPLUG-List mailing list