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

    count record distinct and summ the result (2000)

    With a vba (Excel) macro, please...
    I have a table (similar excel file attached) in access mdb
    How to count for ROSSO (same for all record in campo1) each occurence in filed 2 and summ the value from field campo3.
    Store the result in a var_ROSSO_AAAA
    example:

    ROSSO for AAA = 6
    ROSSO for BBB = 8
    ROSSO for CCC = 7

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

    Re: count record distinct and summ the result (2000)

    Create a query in the database based on the table.
    Add the three fields to the query grid.
    Select View | Totals.
    Set the Total option for the 3rd field to Sum (Summa).
    Save this query.

    You can open a recordset on this query in Excel, loop through its records and read the value of the third field (SummadiCampo3) for each value of Campo1 and Campo2.

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

    Re: count record distinct and summ the result (200

    Tks i dont know query for access.
    No prob Hans, i make tonight experiment my self wit my little knoledgement...

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

    Re: count record distinct and summ the result (200

    hI Hans welcome in my home....
    Before to test in effective on Access mdb, i test on Excel sheet.
    But have error in (See in modulo1):

    ADOrs.Open strSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText

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

    Re: count record distinct and summ the result (200

    Change the instruction that assigns a value to strSQL:

    strSQL = "SELECT TABELLA.Campo1, TABELLA.Campo2, Sum(TABELLA.Campo3) AS SumOfCampo3 " & _
    "FROM [TABELLA$] AS TABELLA " & _
    "GROUP BY TABELLA.Campo1, TABELLA.Campo2;"

    If you want to refer to a worksheet named Foglio in ADO, you must use the syntax [Foglio$]

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

    Re: count record distinct and summ the result (200

    Sorry for delay, but prob with my pc in my farm attending technical service to adjust the hardware.
    i cannot test now, but tks always.

Posting Permissions

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