Query a campi incrociati con totali in fondo (come ulteriore riga della tabella)
Andrea Nardi
Salve a tutti
Non sono riuscito a risolvere un problema con le query campi incrociati e
spero che qualcuno possa darmi qualche suggerimento.
Questo è il problema: da una query a campi incrociati, come la seguente.
TRANSFORM Count(TAB_E_Base.Nu_Attività) AS [Il valore]
SELECT Gruppo1b.Gruppo1b AS [Inserimento commerciale],
Count(TAB_E_Base.Nu_Attività) AS [Totale esercizi]
FROM Gruppo1b RIGHT JOIN (Gruppo1a RIGHT JOIN TAB_E_Base ON
Gruppo1a.IDGruppo1a = TAB_E_Base.Fl_CarInsCom_1) ON Gruppo1b.IDGruppo1b =
TAB_E_Base.Fl_CarInsCom_2
GROUP BY Gruppo1b.IDGruppo1b, Gruppo1b.Gruppo1b
ORDER BY Gruppo1b.IDGruppo1b
PIVOT Gruppo1a.Gruppo1a;
.. ottengo la seguente tabella:
TIPO | A | B | C | TOT.
Tipo 1 | 2 | | | 2
Tipo 2 | 38 | 4 | 3 | 45
Tipo 3 | 8 | | | 8
Tipo 4 | 9 | | | 9
Il mio problema è che mi servirebbe inserire i totali delle colonne come
RIGA AGGIUNTIVA, ed un'ulteriore riga con i valori in percentuale rispetto
al totale. IL TUTTO CON UN'UNICA ISTRUZIONE SQL, o comunque il risultato
deve essere contenuto in un'unica tabella (se poi sono necessarie una serie
di istruzioni sql non mi importa)
Ma non basta. Devo inserire anche un'ulteriore COLONNA con il valore
percentuale della colonna TOT. (ma questo so comunque come farlo e non è un
problema)
Alla fine il risultato dovrebbe essere la tabella seguente:
TIPO | A | B | C | TOT. | TOT.%
Tipo 1 | 2 | | | 2 |
3%
Tipo 2 | 38 | 4 | 3 | 45 | 70%
Tipo 3 | 8 | | | 8 |
13%
Tipo 4 | 9 | | | 9 |
14%
___________|______|______|______|_______|
Totale | 57 | 4 | 3 | 65
Totale % | 88% | 6% | 5% |
Ho provato utilizzando una query di unione che mi andava ad unire alla prima
query altre due query con i campi calcolati.
Ma c'è un problema.
Le query a campi incrociati possono dare un numero di colonne variabili a
seconda dei dati.
Utilizzando la query di unione, spesso viene generato un errore perché le
colonne della prima query non corrispondono con quelle delle query che
unisco con la query di unione.
A questo punto mi si presentano modalità di soluzione estremamente
cervellotiche (far contare le colonne della prima query, ecc.)
Vorrei sapere se è possibile risolvere questo problema con un'unica SQL o,
comunque, se ci sia un modo semplice per risolvere il problema che a me
appare molto complesso.
Grazie
Stefano
--
Posted from root@[212.104.51.2]
via Mailgate.ORG Server - http://www.Mailgate.ORG
Roberto dal portatile
""Andrea Nardi"" <andrea...@gvpartner.it> ha scritto nel messaggio
news:000801c068dc$3a8c9740$093368d4@natzgul...
In teoria bisognerebbe fare una query unione che contenga tre query a campi
incrociati: ammesso che ciò sia possibile io non ci sono mai riuscito.
Una volta che avuto un problema simile al tuo, l'ho risolto facendo tre
distinte query a campi incrociati: la prima era la vera query a campi
incrociati comprensiva dei totali di riga e delle percentuali di riga, la
seconda mi produceva i totali di colonna e la terza le percentuali di
colonna. Le tre query mi restituivano naturalmente lo stesso numero di
colonne.
Ipotizziamo che le tre query siano basate su una tabella chiamata Tabella1
(ma cambiando il FROM le basi su quello che ti serve) che contiene il campo
ID (sul quale fare il conteggio), il campo Ascissa per intestare le righe ed
il campo Ordinate per intestare le colonne.
Ciò presupposto il codice SQL della prima query dovrebbe essere:
TRANSFORM Count(Tabella1.ID) AS [Il valore]
SELECT Tabella1.Ascissa, Count(Tabella1.ID) AS TOT,
TOT/DCount("[ID]","Tabella1") AS [TOT %]
FROM Tabella1
GROUP BY Tabella1.Ascissa
PIVOT Tabella1.Ordinate;
Il codice SQL della seconda query (quella dei totali di colonna) dovrebbe
essere:
TRANSFORM Count(Tabella1.ID) AS [Il valore]
SELECT "TOTALE" AS Espr1, Count(Tabella1.ID) AS TOT, Null
FROM Tabella1
GROUP BY 1
PIVOT Tabella1.Ordinate;
Il codice SQL della terza query (quella delle percentuali di colonna)
dovrebbe essere:
TRANSFORM Count(Tabella1.ID)/DCount("[ID]","Tabella1") AS [Il valore]
SELECT "TOTALE %" AS Espr1, Null, Null
FROM Tabella1
GROUP BY 1
PIVOT Tabella1.Ordinate;
Quanto al report, per me dovrebbe avere come origine record la prima query e
contenere due sottoreport che abbiano come origine record le altre due query
(naturalmente debitamente incolonnate).
Non è certo una soluzione elegante, ma secondo me funziona.
Facci sapere.
--
Roberto
---------------------------------------------
il Sito Comune di it.comp.appl.access:
http://members.xoom.it/it_db_access/
---------------------------------------------
MarcoMax
Nardi") wrote:
> TIPO | A | B | C | TOT. | TOT.%
> Tipo 1 | 2 | | | 2 |
>3%
>Tipo 2 | 38 | 4 | 3 | 45 | 70%
>Tipo 3 | 8 | | | 8 |
>13%
>Tipo 4 | 9 | | | 9 |
>14%
> ___________|______|______|______|_______|
>Totale | 57 | 4 | 3 | 65
>Totale % | 88% | 6% | 5% |
Ho risolto il problema in modo semplice... perň io non avevo le
percentuali... ma magari funge lostesso :-)
Ho usato, come dicevi te, l'unione, ma l'ho usata PRIMA di fare la
query a campi incrociati... in pratica avevo i miei dati in formato "3
colonne"
CLIENTE - FAMIGLIA DI ARTICOLI - QUANTITA
PIPPO - AAA - 10
PIPPO - BBB - 30
PIPPO - ADD - 20
PIPPO - KKA - 10
PLUTO - KKA - 50
PLUTO - QQQ - 40
PLUTO - FFF - 20
PLUTO - BBB - 50
ecc.....
calcolavo i tatali per famiglia,
FAMIGLIA DI ARTICOLI - QUANTITA
AAA - 10
BBB - 80
ADD - 20
KKA - 60
QQQ - 40
FFF - 20
unive i due risultati precedenti tramite union, per la seconda query
generavo un campo CODICE CLIENTE che conteneva sempre la stringa "ZZ
Totale"
cosě ottenevo una query che risultava
CLIENTE - FAMIGLIA DI ARTICOLI - QUANTITA
PIPPO - AAA - 10
PIPPO - BBB - 30
PIPPO - ADD - 20
PIPPO - KKA - 10
PLUTO - KKA - 50
PLUTO - QQQ - 40
PLUTO - FFF - 20
PLUTO - BBB - 50
ZZ Totale - AAA - 10
ZZ Totale - BBB - 80
ZZ Totale - ADD - 20
ZZ Totale - KKA - 60
ZZ Totale - QQQ - 40
ZZ Totale - FFF - 20
Su questa poi facevo la query a campi incrociati
la riga aggiunta va in fondo grazie alle due Z iniziali "ZZ Totale",
perque sono ordinate in modo alfabetico :-)
Poi l'ho migliorata, eliminando le 2 Z iniziali ed aggiungendo un
campo nascosto (non vosualizzato nella query finale, ma utilizzato
solo per l'ordinamento)
CLIENTE - FAMIGLIA DI ARTICOLI - QUANTITA - CAMPO NASCOSTO
PIPPO - AAA - 10 - A
PIPPO - BBB - 30 - A
PIPPO - ADD - 20 - A
PIPPO - KKA - 10 - A
PLUTO - KKA - 50 - A
PLUTO - QQQ - 40 - A
PLUTO - FFF - 20 - A
PLUTO - BBB - 50 - A
ecc.....
Qui il campo nascosto vale sempre "A"
FAMIGLIA DI ARTICOLI - QUANTITA - CAMPO NASCOSTO
AAA - 10 - B
BBB - 80 - B
ADD - 20 - B
KKA - 60 - B
QQQ - 40 - B
FFF - 20 - B
Qui il campo nascosto vale sempre "B"
Univo ed avevo:
CLIENTE - FAMIGLIA DI ARTICOLI - QUANTITA - CAMPO NASCOSTO
PIPPO - AAA - 10 - A
PIPPO - BBB - 30 - A
PIPPO - ADD - 20 - A
PIPPO - KKA - 10 - A
PLUTO - KKA - 50 - A
PLUTO - QQQ - 40 - A
PLUTO - FFF - 20 - A
PLUTO - BBB - 50 - A
Totale - AAA - 10 - B
Totale - BBB - 80 - B
Totale - ADD - 20 - B
Totale - KKA - 60 - B
Totale - QQQ - 40 - B
Totale - FFF - 20 - B
facevo la query a campi incrociato irdinando in modo crescente per i
campi CAMPO NASCOSTO (usato, ma non visualizzato), e poi per il campo
CLIENTE
cosě il record "Totale" viene per forza di cose in fondo.
Funziona :-)
Ciao, Max
Roberto dal portatile
"MarcoMax" <p...@papapa.pupupu> ha scritto nel messaggio
news:3a3f3714....@news.tin.it...
Ci sarebbe da risolvere un problemino (oltre a quello di mettere le
percentuali che comunque va risolto): Andrea ha chiesto contare record e non
di sommare valori.
Roberto dal portatile
""Andrea Nardi"" <andrea...@gvpartner.it> ha scritto nel messaggio
news:000801c068dc$3a8c9740$093368d4@natzgul...
Rielaborando adeguatamente l'idea di MarcoMax, sono riuscito a risolvere il
tuo problema con due query; la prima è una query unione sulla quale è basata
la query a campi incrociati: quest'ultima è omnicomprensiva, ovvero contiene
i totali e le percentuali di riga e i totali e le percentuali di colonna.
Ipotizziamo che la query unione, che dovrai chiamare Query3, sia basata su
una tabella chiamata Tabella1 (ma cambiando le FROM della Query3 la basi su
quello che ti serve) che contiene il campo ID, il campo Ascissa per
intestare le righe ed il campo Ordinate per intestare le colonne.
Ciò presupposto il codice SQL di Query3 sarà:
SELECT Ascissa, Ordinate, Sum(1) AS Espr0, 1 AS Pippo
FROM Tabella1
GROUP BY Ascissa, Ordinate
UNION SELECT "TOTALE" AS Espr1, Ordinate, Sum(1) AS Espr2, 2 AS Pippo
FROM Tabella1
GROUP BY Ordinate
UNION SELECT "TOTALE%" AS Espr3, Ordinate,
Sum(1/DCount("ID","Tabella1"))*100 AS Espr4, 3 AS Pippo
FROM Tabella1
GROUP BY Ordinate;
mentre il codice SQL della query a campi incrociati sarà:
TRANSFORM Sum(Espr0) AS [Il valore]
SELECT Ascissa, IIf(Pippo<>3,Sum(Espr0),0) AS TotRiga,
IIf(Pippo=1,Sum(Espr0)/DCount("ID","Tabella1"),0) AS PercRiga
FROM Query3
GROUP BY Pippo, Ascissa
PIVOT Ordinate;
Naturalmente al campo TotRiga devi dare il formato #.### e al campo PercRiga
il formato #,##%.
Facci sapere se funziona.