Results 1 to 9 of 9

Thread: GROUP BY

  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How to GROUP BY AGENZIA_4 instead select distint this query:


    SQL = "Select DISTINCT AGENZIA_4,AGENZIA_CENTRO,ZONA,MERCATO_DISTRIB,AREA _TERRIT From " & T_TABELLE & " WHERE TIPO_UO_1='21' AND DENDVAL= '" & Format(DATA, "yyyy/mm/dd") & "' AND MERCATO_PTF='F' AND NOT ZONA='N_C'"

    tks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you group by AGENZIA_4, what do you want to do with AGENZIA_CENTRO, ZONA, MERCATO_DISTRIB and AREA_TERRIT?

    In a totals query (with GROUP BY), you must specify an aggregate function such as Sum, Count, First, etc. for each field in the SELECT part on which you don't group.

  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
    If you group by AGENZIA_4, what do you want to do with AGENZIA_CENTRO, ZONA, MERCATO_DISTRIB and AREA_TERRIT?

    In a totals query (with GROUP BY), you must specify an aggregate function such as Sum, Count, First, etc. for each field in the SELECT part on which you don't group.
    in this case Count...

    If you group by AGENZIA_4, what do you want to do with AGENZIA_CENTRO, ZONA, MERCATO_DISTRIB and AREA_TERRIT?

    yes

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

    SQL = "SELECT AGENZIA_4, Count(AGENZIA_CENTRO), Count(ZONA), Count(MERCATO_DISTRIB), Count(AREA_TERRIT) FROM " & T_TABELLE & " WHERE TIPO_UO_1='21' AND DENDVAL= '" & Format(DATA, "yyyy/mm/dd") & "' AND MERCATO_PTF='F' AND NOT ZONA='N_C' GROUP BY AGENZIA_4"

  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
    Try

    SQL = "SELECT AGENZIA_4, Count(AGENZIA_CENTRO), Count(ZONA), Count(MERCATO_DISTRIB), Count(AREA_TERRIT) FROM " & T_TABELLE & " WHERE TIPO_UO_1='21' AND DENDVAL= '" & Format(DATA, "yyyy/mm/dd") & "' AND MERCATO_PTF='F' AND NOT ZONA='N_C' GROUP BY AGENZIA_4"
    WORK fine !

    About that...
    Admit have a workbook in \\serverdir\wbook.xls and a sheet named TEST.
    How to copy the entire recordset from A2 in "one shot only", possible?
    I have see in forum existis a copyrecordsetfrom command is that?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Excel VBA has a method CopyFromRecordset. It works as follows:

    1. Open a DAO or ADO recordset, say RST.

    2/ Use

    Range("A2").CopyFromRecordset RST

    See the Excel VBA help or CopyFromRecordset Method.


  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
    Excel VBA has a method CopyFromRecordset. It works as follows:

    1. Open a DAO or ADO recordset, say RST.

    2/ Use

    Range("A2").CopyFromRecordset RST

    See the Excel VBA help or CopyFromRecordset Method.
    yes, ok.
    But the workbook is into a dir of server and i dont want to open it, is possible?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    CopyFromRecordset only works in an open workbook.


  9. #9
    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
    CopyFromRecordset only works in an open workbook.
    ok....

Posting Permissions

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