Results 1 to 12 of 12
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    recopy without dupes (2000 sr.1)

    ... i have 2 table TAB1 and TAB2 on the same MDB named PROVA.MDB
    I wolud want to recopy from TAB1 in to TAB2 only the value of TAB1 with no duplicates.

    TAB1
    aaaa
    aaaa
    aaaa
    bbbb
    ccccc
    bbbb
    dddd
    dddd

    final result in TAB2

    aaaa
    bbbb
    ccccc
    dddd

    with DAO or ADO i must use this in a macro for VBA for Excel

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: recopy without dupes (2000 sr.1)

    If you want to append unique records from TAB1 to TAB2, you can use code for an append query:

    Dim strSQL As String
    strSQL = "INSERT INTO TAB2 SELECT DISTINCT * FROM TAB1"
    cnn.Execute strSQL

    where cnn is an ADODB.Connection object that has already been set the same way as in your other ADO code.

    If you want to replace existing records, you can use code for a make table query:

    Dim strSQL As String
    strSQL = "DROP TABLE TAB2"
    On Error Resume Next
    cnn.Execute strSQL
    On Error GoTo 0
    strSQL = "SELECT DISTINCT * INTO TAB2 FROM TAB1"
    cnn.Execute strSQL

    with cnn as above.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recopy without dupes (2000 sr.1)

    Sorry, Hans...
    But i am a stupid....
    The name of table are correct but i want to insert the value of tab1 filed GRADO and MANSIONE in to tab2 field GRADO and MANSIONE without dupes...
    Sorry...
    See the attach MDB

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: recopy without dupes (2000 sr.1)

    Try this:

    Dim strSQL As String
    strSQL = "INSERT INTO tabella2 SELECT DISTINCT GRADO, MANSIONE FROM tabella1"
    cnn.Execute strSQL

    where cnn is an ADODB.Connection to the database.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recopy without dupes (2000 sr.1)

    Good it work...
    But in field GRADO and MANSIONE are recopied many time the same value...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: recopy without dupes (2000 sr.1)

    The values in GRADO are repeated, and the values in MANSIONE are repeated, but the combinations are unique. For example, "3 AREA 1 LIV-I" occurs several times in the GRADO field, and "ADDETTO SEGRETERIA" occurs several times in the MANSIONE field, but the combination GRADO="3 AREA 1 LIV-I" and MANSIONE="ADDETTO SEGRETERIA" occurs only once in tabella2. If you want unique values only of GRADO, and unique values only of MANSIONE, you would need two separate tables, one for GRADO, and another one for MANSIONE.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recopy without dupes (2000 sr.1)

    .. if i have understand:

    Tabella2 with GRADO
    Tabella3 with MANSIONE

    I have undestand wath is the new macro with this strategy?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: recopy without dupes (2000 sr.1)

    Two separate SQL statements:

    strSQL = "INSERT INTO tabella2 SELECT DISTINCT GRADO FROM tabella1"
    cnn.Execute strSQL

    strSQL = "INSERT INTO tabella3 SELECT DISTINCT MANSIONE FROM tabella1"
    cnn.Execute strSQL

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recopy without dupes (2000 sr.1)

    Hans, it work, naturally....
    Only on clearment...
    In tabella1 the ID start from 1 to 795
    In tabella2 the ID start from 6784 to 6794
    In tabella3 the ID start from 6843 to 6912

    if i would want to start to tabella1 and tabella3 the ID from 1?

    I dont understand this increase of numbers, boh?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: recopy without dupes (2000 sr.1)

    The ID fields are AutoNumber fields. The "Auto" means that numbering is automatic. The numbers don't have any meaning, they are just unique identifiers. Do you really need them here?

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recopy without dupes (2000 sr.1)

    No, need. not is important for my project...

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: recopy without dupes (2000 sr.1)

    So you can either remove the AutoNumber fields, or ignore their values.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •