GROUP BY

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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”

Reply | Quote

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 \serverdirwbook.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?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

CopyFromRecordset only works in an open workbook.

Reply | Quote

CopyFromRecordset only works in an open workbook.

ok….

Reply | Quote