Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I use the tipical ADO conn n VBA for Excel.
    Have a table named mytable with 5 fileds
    I want to group by first filed the value in first filed and summ the value in filed 4 and filed 5 how to?
    Note:
    In first filed possible duplicates value.

    strucutre of table:
    filed1 .... filed4 filed5
    4500 45 15
    4500 61 8
    4501 88 11
    6800 1 8
    ...
    7900 145 55
    8500 5 21
    reslut of query:
    4500 106 23
    4501 88 11
    ....

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The SQL string would be

    SELECT Filed1, Sum(Filed4), Sum(Filed5) FROM MyTable GROUP BY Filed1


  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    The SQL string would be

    SELECT Filed1, Sum(Filed4), Sum(Filed5) FROM MyTable GROUP BY Filed1
    ok! tks.

    But i need, now, to integrate a where clausole in the your sql:

    where field3='opt1' or filed3='opt2'

    how to?
    sorry me.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The WHERE part goes between the FROM part and the GROUP BY part:

    SELECT Filed1, Sum(Filed4), Sum(Filed5) FROM MyTable WHERE Filed3='opt1' OR Filed3='opt2' GROUP BY Filed1

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    The WHERE part goes between the FROM part and the GROUP BY part:

    SELECT Filed1, Sum(Filed4), Sum(Filed5) FROM MyTable WHERE Filed3='opt1' OR Filed3='opt2' GROUP BY Filed1
    ACCCCCCCCCCCCC...........
    Error in preposition FROM


    SQL1 = "SELECT UO_RADICAMENTO, Sum(CLIENTI_CORRENTISTI), Sum(CLI_POSSES_BANCOMAT) FROM '& NOME_TABELLA &' WHERE COD_MERCATO='INDV' OR COD_MERCATO='PRIV' GROUP BY UO_RADICAMENTO"

    Note:
    NOME_TABELLA is a string variable

    and the statement AS Summ1 is needed after sum(filed)???
    and when i use:

    While Not RS1.EOF
    TEST = RS1!UO_RADICAMENTO
    Wend

    instead to have a distinct value of field1 have all value of not gruoing fields!!!

    I need to have the distinct value of field filtered and the relative summ

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The quotes are not correct. It should be

    SQL1 = "SELECT UO_RADICAMENTO, Sum(CLIENTI_CORRENTISTI), Sum(CLI_POSSES_BANCOMAT) FROM " & NOME_TABELLA & " WHERE COD_MERCATO='INDV' OR COD_MERCATO='PRIV' GROUP BY UO_RADICAMENTO"

    If you don't specify an alias AS Summ1, ADO will assign a field name such as Expr1001. If you want to assign the names yourself, you can use AS ...

    If you want to loop through the records, you must include a MoveNext inside the loop:

    Code:
    Do While Not RS1.EOF
     TEST = RS1!UO_RADICAMENTO
     RS1.MoveNext
    Loop

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    The quotes are not correct. It should be

    SQL1 = "SELECT UO_RADICAMENTO, Sum(CLIENTI_CORRENTISTI), Sum(CLI_POSSES_BANCOMAT) FROM " & NOME_TABELLA & " WHERE COD_MERCATO='INDV' OR COD_MERCATO='PRIV' GROUP BY UO_RADICAMENTO"

    If you don't specify an alias AS Summ1, ADO will assign a field name such as Expr1001. If you want to assign the names yourself, you can use AS ...

    If you want to loop through the records, you must include a MoveNext inside the loop:

    Code:
    Do While Not RS1.EOF
     TEST = RS1!UO_RADICAMENTO
     RS1.MoveNext
    Loop
    As usual

Posting Permissions

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