SQL 'IGNORE' ?

Lon McPhail L at macmailbox.com
Wed Jun 23 00:47:00 PDT 2004


Sorry to those wanting help, as I realize I short-changed you info-wise.
Here's where I am:

I have ~12 sets of data from different sources, all allegedly subscribing to
the same data format.  The data currently lives in Access, but I can port it
between Excel/CSV/etc. in minutes.  Each dataset has ~30k entries, all of
which have two defining elements; a name and one of these 7/8 field strings.

The ultimate goal is to be able to update each dataset independantly, then
have a simeple SQL query tell me what entries are alike across all ~12
datasets.  

E.g.-

Dataset #1:				Dataset #2:
F14,2.1.225.4.2.1.2.1		F14,2.1.225.4.2.1.2.1
F18,3.4.34.1.5.6.4.3		F18,3.4.55.4.3.2.34.4
F22,2.4.221.52.2.1.45.45	F22,2.4.221.52.2.1.45.45
==========================================================

Lines:
F14,2.1.225.4.2.1.2.1		F14,2.1.225.4.2.1.2.1
F22,2.4.221.52.2.1.45.45	F22,2.4.221.52.2.1.45.45
MATCH

Lines:
F18,3.4.34.1.5.6.4.3		F18,3.4.55.4.3.2.34.4
NOMATCH
==========================================================


The problem is that Dataset #2 only has the first 7 fields, so this is what
I'm getting:


E.g.-

Dataset #1:				Dataset #2:
F14,2.1.225.4.2.1.2.1		F14,2.1.225.4.2.1.2
F18,3.4.34.1.5.6.4.3		F18,3.4.55.4.3.2.34
F22,2.4.221.52.2.1.45.45	F22,2.4.221.52.2.1.45
==========================================================

Lines:
MATCH

Lines:
F14,2.1.225.4.2.1.2.1		F14,2.1.225.4.2.1.2
F18,3.4.34.1.5.6.4.3		F18,3.4.55.4.3.2.34
F22,2.4.221.52.2.1.45.45	F22,2.4.221.52.2.1.45
NOMATCH
==========================================================


I don't give a hoot about the last field, but I can't strip it off
completely as other functions care a great deal.  In the end, what I want is
this (and I need it to so this in Excel or Access):

E.g.-

Dataset #1:				Dataset #2:
F14,2.1.225.4.2.1.2.1		F14,2.1.225.4.2.1.2.1
F18,3.4.34.1.5.6.4.3		F18,3.4.55.4.3.2.34.4
F22,2.4.221.52.2.1.45.45	F22,2.4.221.52.2.1.45.45
==========================================================

Lines:
F14,2.1.225.4.2.1.2.1		F14,2.1.225.4.2.1.2
F22,2.4.221.52.2.1.45.45	F22,2.4.221.52.2.1.45
MATCH

Lines:
F18,3.4.34.1.5.6.4.3		F18,3.4.55.4.3.2.34
NOMATCH
==========================================================

By the way, notice how the 8th field isn't always the same length?  


TIA,

Lon McPhail

 

-> -----Original Message-----
-> From: Starren, Erick E CIV [mailto:erick.starren at navy.mil] 
-> Sent: Tuesday, June 22, 2004 4:23 PM
-> To: kplug-list at kernel-panic.org
-> Subject: RE: SQL 'IGNORE' ?
-> 
-> > Have thousands of eight-field lines like the following:
-> > 
-> > 2.1.225.4.2.1.2.1
-> > 2.4.221.52.2.1.45.45
-> > 
-> > Db lives in either Excel or Access.  I need to do a 
-> compare between 
-> > these entries and another table of entries with only seven fields.
-> 
-> Lon, what form is your data in right now?  ASCII file?  
-> Excel?  Access?
-> 
-> What software do you plan/want/need to use for your compare?
-> 
-> Erick
-> San Diego




More information about the KPLUG-List mailing list