SUM (campo) dove campo è STRINGA (in ACCESS)

domenica 05 marzo 2006 - 14.48

cfalduto Profilo | Newbie

ciao.
volevo sapere come poter concatenare in Access del testo con la formula group by o in qualche altro modo.

se la tabella è:

id nome
1 claudio
1 chiara
2 francesco
2 sara

SELECT ID, SUM (NOME)
FROM TABELLA
GROUP BY ID

per ottenere:
id somma
1 claudio, chiara
2 francesco, sara

chiaramente in Access non si può fare e poi come si fa a mettere la virgola tra i nomi??

attendo un'illuminazione
grazie mille
claudio

lbenaglia Profilo | Guru

Ciao claudio,

l'operazione che stai cercando di fare non è una sommatoria che può essere eseguita tramite la funzione di aggregazione SUM() ma piuttosto una concatenazione di stringhe.
Microsoft Access è in grado di estendere le funzionalità del JET-SQL mediante il linguaggio di programmazione Visual Basic for Application (VBA).
Vediamo come possiamo scrivere una funzione di concatenazione in VBA.
Nei miei esempi utilizzerò Microsoft Access 2003 in inglese.

Prima di tutto definiamo la tabella Studenti(StudenteID number, Nome Text(50)):

StudenteID Nome ------------ ------------ 1 Claudio 1 Chiara 2 Francesco 2 Sara

Ora seleziona la voce "Modules" e clicca il bottone "New" per visualizzare l'editor di VBA.
Bene, scriviamo una funzione che accetti in input i parametri StudenteID, Nome e Separatore (ovvero il carattere utilizzato per separare i nomi) e che restituirà in output la stringa concatenata:

Public Function Concatena(ByVal StudenteID As Integer, ByVal Nome As String, ByVal Separatore As String) As String Static s_intStudenteID As Integer Static s_strOutput As String ' Verifico se si tratta di un nuovo studente If s_intStudenteID <> StudenteID Then ' Memorizzo lo studente s_intStudenteID = StudenteID ' Inizializzo la variabile di output con il nome dello studente s_strOutput = Nome Else ' Concateno il nome s_strOutput = s_strOutput + Separatore + Nome End If ' Valorizzo il valore di ritorno della funzione Concatena = s_strOutput End Function

Come vedi ricorro alle variabili statiche in modo che il valore sia conservato tra una chiamata e l'altra.
Il funzionamento è molto semplice:

- prima di tutto stabilisco se si tratta di una nuova chiamata o di una rottura di codice;
in entrambi i casi devo inizializzare la stringa strOutput con il nome dello studente;

- per tutte le altre chiamate non faccio altro che concatenare i nomi separandoli con il separatore specificato.

Infine richiamerò la funzione dalla seguente query:

SELECT StudenteID, MAX(Concatena(StudenteID, Nome, ', ')) AS Nomi FROM Studenti GROUP BY StudenteID;

La funzione di aggregazione MAX() in realtà è superflua ai fini logici, ma mi serve per non specificare inutilmente la funzione Concatena() nella clausola GROUP BY.

Eseguendo la query otterrò il risultato richiesto:

StudenteID Nomi ------------ ----------------- 1 Claudio, Chiara 2 Francesco, Sara

Attenzione: il tutto funziona solo se eseguito all'interno di Microsoft Access dato che il JET Engine non è in grado di richiamare codice VBA da query JET-SQL.

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

cfalduto Profilo | Newbie

Grazie mille
Provo e ti so dire!
claudio

fiorenontaglio Profilo | Newbie

ciao a tutti
ho anch'io lo stesso problema ma con una differenza:

il mio analogo StudenteID è un campo testo, come dire:

StudenteID | Nome
A | Claudio
A | Chiara
B | Francesco
B | Sara

Chiaramente ho sostituito "string" a "integer" nella funzione ma la query non viene eseguita e il messaggio di errore è "Tipo di dati non corrispondenti nell'espressione criterio"

Chiedo gentilmente aiuto.

----------------------------
Secondo quesito: ho bisogno di eseguire la query da un ambiente esterno ad access. Come posso fare?

toni

lbenaglia Profilo | Guru

>il mio analogo StudenteID è un campo testo, come dire:
>
>StudenteID | Nome
>A | Claudio
>A | Chiara
>B | Francesco
>B | Sara
>
>Chiaramente ho sostituito "string" a "integer" nella funzione
>ma la query non viene eseguita e il messaggio di errore è "Tipo
>di dati non corrispondenti nell'espressione criterio"
>
>Chiedo gentilmente aiuto.

Ciao toni,

hai cambiato il data type sia del parametro StudenteID che della variabile statica s_strStudenteID, vero?
Guarda:

Public Function Concatena(ByVal StudenteID As String, ByVal Nome As String, ByVal Separatore As String) As String Static s_strStudenteID As String Static s_strOutput As String ' Verifico se si tratta di un nuovo studente If s_strStudenteID <> StudenteID Then ' Memorizzo lo studente s_strStudenteID = StudenteID ' Inizializzo la variabile di output con il nome dello studente s_strOutput = Nome Else ' Concateno il nome s_strOutput = s_strOutput & Separatore & Nome End If ' Valorizzo il valore di ritorno della funzione Concatena = s_strOutput End Function

La query non cambia:

SELECT StudenteID, MAX(Concatena(StudenteID, Nome, ', ')) AS Nomi FROM Studenti GROUP BY StudenteID;

Ed in output otterrai:

StudenteID Nomi ------------ ----------------- A Claudio, Chiara B Francesco, Sara

>Secondo quesito: ho bisogno di eseguire la query da un ambiente
>esterno ad access. Come posso fare?

Non puoi. Jet non permette di utilizzare funzioni VBA associate a query SQL.
Questo trucchetto funziona solo all'interno di Access.
Se hai questa necessità devi crearti una funzione analoga lato client.

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

fiorenontaglio Profilo | Newbie

Caro Lorenzo,

ho provato la funzione con il database di esempio e gira bene.
Putroppo non è così dove mi serve veramente, ecco alcune deduzioni:

Nel mio database ci sono molti valori Null. Ho provato ad inserire valori Null nel database di esempio (sia nel campo StudenteID che in Nome) e mi viene restituito il medesimo errore che ottengo con il vero database: "Tipi di dati non corrispondenti".

Come fare con questi valori? Dunque, i record con ID nullo (tipo StudenteID = Null) possono anche essere esclusi dal raggruppamento mentre i valori Null di Nome vanno concatenati (ad esempio "Valeria & Null" dovrebbe restituire "Valeria")

Nota: la sintassi corretta nella query è Concatena( ... ; ... ; ... ) e non Concatena ( ... , ... , ... )

-----------------------

Altra domanda: In che modo posso usare come separatore il carattere di invio (testo a capo)? Ho provato con Chr$(13) ma non funziona. Ho provato ad battere nel testo separatore [Ctrl+invio] ma niente....

grazie mille
ciao
toni

lbenaglia Profilo | Guru

>Nel mio database ci sono molti valori Null. Ho provato ad inserire
>valori Null nel database di esempio (sia nel campo StudenteID
>che in Nome) e mi viene restituito il medesimo errore che ottengo
>con il vero database: "Tipi di dati non corrispondenti".
>
>Come fare con questi valori? Dunque, i record con ID nullo (tipo
>StudenteID = Null) possono anche essere esclusi dal raggruppamento
>mentre i valori Null di Nome vanno concatenati (ad esempio "Valeria
>& Null" dovrebbe restituire "Valeria")

Beh toni,

tu stai utilizzando un DBMS come se fosse un foglio Excel
Tabelle con valori NULL, tabelle senza chiavi primarie... insomma, una cosa brutta

>Nota: la sintassi corretta nella query è Concatena( ... ; ...
>; ... ) e non Concatena ( ... , ... , ... )

Devi semplicemente apportare una piccola modifica sia alla funzione che alla alla query.
Supponiamo di avere la tabella Studenti popolata con le seguenti righe:

StudenteID Nome ----------- -------- NULL Chiara A Claudio B NULL B Sara B Andrea NULL Giordano B NULL B NULL B Alfonso C Luca C NULL C NULL NULL Gianluca C Roberto

La funzione Contatena dovrà prevedere il caso in cui Nome sia NULL:

Public Function Concatena(ByVal StudenteID As String, ByVal Nome As String, ByVal Separatore As String) As String Static s_strStudenteID As String Static s_strOutput As String ' Verifico se si tratta di un nuovo studente If s_strStudenteID <> StudenteID Then ' Memorizzo lo studente s_strStudenteID = StudenteID ' Inizializzo la variabile di output con il nome dello studente s_strOutput = Nome Else ' Se al precedente ciclo non avevo valorizzato ' la stringa di output, non metto il separatore If Len(s_strOutput) = 0 Then s_strOutput = s_strOutput & Nome ' Se il Nome è valorizzato, allora effettuo la concatenazione ' Altrimenti ciccia ElseIf Len(Nome) > 0 Then s_strOutput = s_strOutput & Separatore & Nome End If End If ' Valorizzo il valore di ritorno della funzione Concatena = s_strOutput End Function

E la query diventa:

SELECT StudenteID, MAX(Concatena(StudenteID, IIF(Nome IS NULL, '', Nome),'; ')) AS Nomi FROM Studenti WHERE StudenteID IS NOT NULL GROUP BY StudenteID;

Eseguendola otterrai il seguente risultato:

StudenteID Nomi ----------- -------- A Claudio B Sara; Andrea; Alfonso C Luca; Roberto

esattamente come richiesto.

>Altra domanda: In che modo posso usare come separatore il carattere
>di invio (testo a capo)? Ho provato con Chr$(13) ma non funziona.
>Ho provato ad battere nel testo separatore [Ctrl+invio] ma niente....

Ma che cosa stai cercando di fare?
Ad ogni modo, modifica la query nel seguente modo:

Il codice sorgente non è stato renderizzato qui
perchè non c'è sufficiente spazio.
Clicca qui per visualizzarlo in una nuova finestra

In output otterrai:

StudenteID Nomi ----------- -------- A Claudio B Sara Andrea Alfonso C Luca Roberto

>grazie mille
Prego, ma per me non stai lavorando bene

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

fiorenontaglio Profilo | Newbie

eheh, le tue osservazioni sono più che giustificate. Ma se tu sapessi cosa vuol dire mettere le mani ai database alfanumerici del catasto e collegarli ad altri database geografici catastali...
Guarda, sono in una situazione critica (software incompleti, database enormi ma poco integrati, ecc.) e mi barcameno. In ogni caso successivamente opererò una razionalizzazione dei database (indicizzazione, chiavi dove posso, pulizia generale, limitazione di bytes), ora ho fretta...

Ho provato la funzione (è ok, a posto anche la questione per evitare il separatore quando la stringa precedente è '') sul database di esempio e andava.
Ma su quello di lavoro ho ancora qualche problema.

Access esegue la query (infatti noto la progressione nella spia verde in basso a sinistra) ma in fondo mi dà errore:
"codice -1038. Errore generico, non ci sono spiegazioni". Cliccando sull'help di messaggio la spiegazione prosegue: "errore di <voce> codice: 3000, contattare supporto tecnico bla bla bla"

Ho provato ad accorciare la tabella a circa 200 record (la tabella intera ne ha oltre 24000) e questa volta ha funzionato!

Se hai ancora tempo da perdere...

Per la "nota", adesso ci sono: i punto e virgola sono per il generatore di espressioni, le virgole per SQL

Quante complicazioni Access, però! Una banale aggregazione SQL su campi testuali... Siamo già alla versione 2003 (oltre?) e ancora non esistono certe funzioni nel pacchetto di base

lbenaglia Profilo | Guru

>Access esegue la query (infatti noto la progressione nella spia
>verde in basso a sinistra) ma in fondo mi dà errore:
>"codice -1038. Errore generico, non ci sono spiegazioni". Cliccando
>sull'help di messaggio la spiegazione prosegue: "errore di <voce>
>codice: 3000, contattare supporto tecnico bla bla bla"

Questo errore è troppo generico.
Potresti debuggare la funzione per capire il motivo dell'eccezione.

>Per la "nota", adesso ci sono: i punto e virgola sono per il
>generatore di espressioni, le virgole per SQL

Che significa?

>Quante complicazioni Access, però! Una banale aggregazione SQL
>su campi testuali... Siamo già alla versione 2003 (oltre?) e
>ancora non esistono certe funzioni nel pacchetto di base

E stai traquillo che non esisterà mai una funzione di concatenazione, dato che non rispetta nessun canone dell'algebra relazionale... in tre parole "è una vaccata"

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

fiorenontaglio Profilo | Newbie

Si è vero: è un po' una vaccata, ma non posso fare altrimenti...

Non ho mai utilizzato il VBA fino ad oggi (pur sapendo programmare, non ad oggetti però). Ti scoccia dirmi come eseguo un debug di una funzione? Ho provato a smanettare un po' ma non ne vado fuori. Al massimo mandami un link di una guida on line

Ciao, grazie ancora

lbenaglia Profilo | Guru

>Ti scoccia dirmi come eseguo un debug
>di una funzione? Ho provato a smanettare un po' ma non ne vado
>fuori.

In Access seleziona il modulo che contiene la funzione, posizionati sulla riga che intendi debuggare e premi F9 per inserire un breakpoint. Esegui la query e vedrai che l'esecuzione si interromperà su quella riga. Premi F8 per scorrere le singole istruzioni e buon divertimento

>Ciao, grazie ancora
Prego.

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

fiorenontaglio Profilo | Newbie

Ho l'errore.
Dopo i 255 caratteri concatenati sballa. Ho bisogno di migliaia di caratteri!

Non lo avrei mai pensato perchè avevo verificato che il data type string andasse bene per stinghe lunghe...

Mi sa che basta cambiare a MEMO il campo della tabella su cui viene eseguita la query... suggerimenti?

lbenaglia Profilo | Guru

>Ho l'errore.
>Dopo i 255 caratteri concatenati sballa. Ho bisogno di migliaia
>di caratteri!

OK.

>Mi sa che basta cambiare a MEMO il campo della tabella su cui
>viene eseguita la query... suggerimenti?

Il db ce l'hai tu. Fai un backup e non aver paura a "smanettare"
Attendiamo il resoconto.

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

fiorenontaglio Profilo | Newbie

i

fiorenontaglio Profilo | Newbie

Non ne vado fuori. Il problema dovrebbe essere proprio questo: quando la stringa di output oltrepassa 255 caratteri e viene assegnata a fine ciclo alla funzione si verifica errore.

Spedisco un estratto del database con tabella di esempio (100 righe), query e funzione.

Cambiare tipo di dati da 'testo' a 'memo' non ha prodotto nessun risultato.

Ho provato a cercare altri data type, ma niente. (mannaggia alla guida in linea che indica la capienza di string fino a 2^16 bytes)

Ho provato a dimensionare le variabili in questo modo:

Static s_OUTPUT As String * 5000

ma non è corretto perchè la stringa assume da subito lunghezza 5000 e non va bene.

E' possibile aumentare 'sti byte o no?! :-)

Ciao

lbenaglia Profilo | Guru

>Non ne vado fuori. Il problema dovrebbe essere proprio questo:
>quando la stringa di output oltrepassa 255 caratteri e viene
>assegnata a fine ciclo alla funzione si verifica errore.

Esatto, il problema è della funzione di aggregazione MAX().
L'help in linea riporta:"

Min, Max Functions
Return the minimum or maximum of a set of values contained in a specified field on a query.

Syntax
Min(expr)

Max(expr)

The expr placeholder represents a string expression identifying the field that contains the data you want to evaluate or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate functions)".

Come puoi notare l'espressione è una stringa che per definizione non può superare i 255 caratteri.
La verifica è presto fatta:

SELECT tabella.IDF, max(string(255,'a')) FROM tabella GROUP BY tabella.IDF;

Questa query ti scatenerà l'errore "Reserved error (-1038); there is non message for this error."

SELECT tabella.IDF, max(string(256,'a')) FROM tabella GROUP BY tabella.IDF;

>E' possibile aumentare 'sti byte o no?! :-)
Temo proprio di no.
Ti conviene effettuare questa aggregazione lato client eseguendo un loop sul recordset.

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

fiorenontaglio Profilo | Newbie

Ti ringrazio molto, mi sei stato veramente di aiuto.
Perchè tutta questa fatica? Perchè volevo evitare di passare per un altro software. Il punto è che riuscivo già da prima ad eseguire questa operazione (tra l'altro con un programma che ricorre al formato mdb quando viene creata una nuova warehouse). Riuscire ad eseguire questo passaggio in Access avrebbe fatto comodo a chi in un futuro utilizzerà il mio lavoro.

E' brutto che un programma come Access, utilizzato da tutti, esperti e soprattutto meno esperti, abbia queste limitazioni...

ciao

lbenaglia Profilo | Guru

>E' brutto che un programma come Access, utilizzato da tutti,
>esperti e soprattutto meno esperti, abbia queste limitazioni...

Vero, ma puoi sempre utilizzare Access come front-end e SQL Server 2005 Express come back-end; in questo modo non avresti alcuna limitazione di questo tipo, sfruttando tutta la potenza di un VERO DBMS lato server

Ciao!

--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

fiorenontaglio Profilo | Newbie

Ti comunico che con un vero...

espediente ALL'ITALIANA

sono riuscito a portare a casa il risultato!

Ascoltami:

1) Faccio eseguire la query SENZA il raggruppamento per IDF (ex studenteID) . In questo modo è una semplice query di selezione, ma ad ogni record viene aggiunta una stringa alla stringa precedente.... in pratica un'esecuzione a manovella! Chiaramente l'ultimo record di uno stesso dominio di IDF conterrà tutti i dati!

2)Sbatto la query in una tabella (query di accodamento) con... ed eccolo... il famigerato campo MEMO a contenere il testo

3)Creo un'altra query che raggruppa IDF e trova il max di SUB_T (artificio per "beccare" l'ultimo record di ogni dominio IDF, che è quello che mi interessa)

4)La query definitiva è un left join tra la tabella al punto 3 e la tabella "accodata" e il gioco è fatto!

E vai di macro...
PAZZESCO!!!!!!

fiorenontaglio Profilo | Newbie

/

Alexia75 Profilo | Newbie

Ciao a tutti,
è probabile che una function di un modulo richiamata in una query non funzioni nel caso di tabelle access collegate a file .xls esterni?
Non ho alcun errore di compilazione.
Se includo la gruop by con la max ho errore di overflow (popup message), se escluso la goup by e elimino la max, la colonna calcolata visualizza i valori #error.

Grazie per l'aiuto!

Alessia
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !
Copyright © dotNetHell.it 2002-2023
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5