How to retrieve column data using sql query

[svuyyuru]

I'm trying to retrieve data based on the contents of one column.

sample table

Description    EID        Basecode        
       -----------    ----       ---------    
      ssdad          1001       S2378797     
      gfd            1002       S1164478     
      gfdsffsdf      1003       R1165778     
      ssdad          1004       M0007867     
      gfd            1005       N7765111     
      gfdsffsdf      1006       W5464111     
      gfd            1005       N7765111     
      gfdsffsdf      1006       A4000011 
      gfdsffsdf      1006       W5464111     
      ssdad          1001       2378797     
      gfd            1002       1164478  
      ssdad          1001       965000
      gfd            1002       780000
      yjgk           4456       540000
      kjhkh          2009       150000
      ddd            1004       1040
      d88jg          1004       14C676
       fsa           6565       158 
       fdh           1004       2Khlm
       ggdg          2009       967

I'm retrieving all **Basecode** column data starts with only letters other than 'W', 'N' by this query

SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 WHERE Not 
     IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N");

And retrieving all **Basecode** if column data length >6 and with numbers '96', '78','54','15' by this query

SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 
    WHERE (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
    (((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR 
    (((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR 
    (((Len([Basecode]))>6) AND  ((Left([Basecode],2))='96'));

How do i get other data which won't retrieve based on above queries, other than data mentioned on these queries like this

Description       EID        Basecode        
       -----------    ----       ---------    

      ssdad          1001       2378797     
      gfd            1002       1164478  
      ddd            1004       1040
      d88jg          1004       14C676
      fsa            6565       158 
      fdh            1004       2Khlm
       ggdg          2009       967

Third query not working

SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 
    WHERE (IsNumeric(Left(Basecode,1)) AND Left(Basecode,1) Not In ("W","N"))
    AND NOT (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR
    (((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR 
    (((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR 
    (((Len([Basecode]))>6) AND  ((Left([Basecode],2))='96'));

 

[plog]

I would take the 2 queries you have, bump them against your table and return those from the table that don't have a match in either query.

You didn't provide query names for the 2 queries you posted SQL for, so lets call the first one (Basecode starts with letter other than 'W' and 'N') 'Q1' and the second (Basecode length>6, first 2 numbers are in list) 'Q2'. Now to get the third query you want, you would use this SQL:

SELECT *
FROM tbl1
LEFT JOIN Q1 ON Q1.Basecode = tbl1.Basecode
LEFT JOIN Q2 ON Q2.Basecode = tbl1.Basecode
WHERE (Q1.Basecode Is Null) AND (Q2.Basecode Is Null)

 

[svuyyuru]

Access says syntax error (Missing operator). I guess we need to give some operator between two left joins? I gave AND it is the same error.

 

[plog]

This SQL should do it:

SELECT tbl1.*
FROM (tbl1 LEFT JOIN Q1 ON tbl1.Basecode = Q1.Basecode) LEFT JOIN Q2 ON tbl1.Basecode = Q2.Basecode
WHERE (Q1.Basecode Is Null) AND (Q2.Basecode Is Null);

 

[svuyyuru]

Getting lots of duplicates i.e same records from Q1 and Q2 to Q3. Result seems wrong.

 

[plog]

There should be no records that are in Q1 or Q2 in Q3. Can you post some sample data?