Multiple queries on a MS access database in VB 6.0

[awyeah]

Hello,

I am trying to do some data filtering using sql query in VB 6.0, but cannot find the correct logic for implementation. Currently I am thinking to use sql to integrate all things into a sql line and execute it:

(1) Select entire table from excel worksheet
(2) Remove the rows in the table corresponding to duplicates in the CustomerNo column
(3) Remove the blank lines in the excel file (where there is no CustomerNo)

I have found a way to execute (1) and (3):

'Do not copy empty rows at the end of the excel file
'(1) sqlstr = "Select * from [" & xlSName & "$]"
'(3) sqlstr = "DELETE FROM " & sqlstr & " WHERE CustomerNo IS NULL"

'Opening Sheet in Excel Sheet Like a Table
      rec.Open " & sqlstr & ", cn, adOpenKeyset

For 2, if anyone can give me a starter, id be delighted. I am still brainstorming the use of sql select for this type of filtering.. Example of (2) how it should be like:

Input table: Table_1
--------------------------------------------------------------------
StnCode CustomerNo Name1 Name2
0121 00200002 HARWANT REALTY SDN BHD (G.S. GILL)
0121 00200003 EUROPE KITCHEN STATION SDN BHD 168
0121 00200004 COLISEUM CAFE & HOTEL
0121 00200005 SKT V K KALYANASUNDRAM SDN BHD (KOWLOON)
0121 00200006 MAJLIS AMANAH RAKYAT (M.A.R.A)
0121 00200006 MAJLIS RAKYAT SDN
0121 00200007 F T LAND SDN BHD
0121 00200008 CYCLE & CARRIAGE BINTANG BHD.
0121 00200010 YAYASAN UBAIDI (MYDIN)
0121 00200011 MALAYAN BANKING BHD.


Output: Table_2
--------------------------------------------------------------------
StnCode CustomerNo Name1 Name2
0121 00200002 HARWANT REALTY SDN BHD (G.S. GILL)
0121 00200003 EUROPE KITCHEN STATION SDN BHD 168
0121 00200004 COLISEUM CAFE & HOTEL
0121 00200005 SKT V K KALYANASUNDRAM SDN BHD (KOWLOON)
0121 00200007 F T LAND SDN BHD
0121 00200008 CYCLE & CARRIAGE BINTANG BHD.
0121 00200010 YAYASAN UBAIDI (MYDIN)
0121 00200011 MALAYAN BANKING BHD.

i.e. if any duplicates (or more than 2) are found in CustomerNo column, remove the rows in the table corresponding to those duplicates in the CustomerNo column.

[EddiRae]

You can do a GROUP by stncode ,customerno, first(name1) as name1, first(name2) as name2.

This will give you one row using the first name1 and name2