Newsgroup: it.comp.appl.access
Titolo: Trasposizione con crosstab query

Io so passare da questa tabella:
 
            ----  Clienti  ----
Prodotti	CLI_1	CLI_2	CLI_3
---------------------------------
P1	         147	 151   	 56
P2	          45	  78     96
P1	          63	  25     78
P3	         112	  89     74
P5	         113	  98     74
P1	          69	  78    101
P5	         166	 108     90
 
 
 
a quest'altra:
 
        ----  Prodotti	-----
Clienti	  P1    P2    P3    P5
------------------------------
CLI_1	 279    45   112   279
CLI_2	 254    78    89   206
CLI_3	 235    96    74   164
 
e viceversa...
con quasi due pagine di codice VBA, passando 4 parametri:
1 - il nome della prima tabella
2 - il nome della seconda (che verrà automaticamente creata
    on-the-fly)
3 - il nome del campo categoria della prima tabella (Prodotti)
4 - e il nome del raggruppamento dei campi dati (Clienti),
    sempre della prima tabella.
 
Mi sembra strano nom possa ottenersi lo stesso risultato
con una crosstab query.
Però finora non ci sono riuscito.
Qualcuno vuole provarci?
 
Bruno
 
 

Non è facile .....
 
ci penso un pò su ...

 ci penso un pò su ...
 
OK!
Intanto io sto cercando di snellire il codice che ho scritto...
 
Bruno
 
PS
===
In Excel è tutto più semplice.
Si può fare con una Query e il Transpose di Special Copy,
oppure con un quarto del codice che ho scritto per Access.
 
 

ancora a un livello MOLTO MOLTO da migliorare e rivisitare una soluzione
rigorosamente solo SQL potrebbe essere quella sotto.
 
La crosstab viene costruita su una Query UNION .....
 
TRANSFORM Sum(C03.CLI_1) AS Somx
SELECT C03.Clienti
FROM 
(
SELECT Clienti.Prodotti, "CLI_1" AS Clienti, Clienti.CLI_1 FROM Clienti 
UNION ALL 
SELECT Clienti.Prodotti, "CLI_2" AS Clienti, Clienti.CLI_2 FROM Clienti 
UNION ALL 
SELECT Clienti.Prodotti, "CLI_3" AS Clienti, Clienti.CLI_3 FROM Clienti
) AS C03
GROUP BY C03.Clienti
PIVOT C03.Prodotti
;
 
certo che con VBA tutto diventerebbe molto piu facile e snello,
soprattutto non dovresti aggiornare la query per ogni cambiamento dei dati.
 
 
Ma ci stiamo ancora lavorando .......

 Ma ci stiamo ancora lavorando .......
Io ho fatto qualche cambiamento di... estetica, ma è già
perfetta così!
 
Complimenti, sei stato bravissimo.
 
Bruno
 
 

 le meningi, fa' lo stesso lavoro di trasposizione su questa tabella:
 
Non è tutto oro quello che luccica,
Una trasposizione ottenuta in quel modo:
A) impiega molte risorse
B) è complessa da capire
C) ceolla se rinomini un campo della tabella
D) Non è sufficientemente collaudata  ( la ho provata solo 2 volte ieri sera )
 
 
Comunque la nuova tabella proposta potrebbe essere 
 
 
TRANSFORM First(T03.Primavera) AS PrimP
SELECT T03.Stagioni
FROM 
(
SELECT Tabe.Prodotti, "1Prima" AS Stagioni, Tabe.Primavera FROM Tabe 
UNION ALL 
SELECT Tabe.Prodotti, "2Estat" AS Stagioni, Tabe.Estate FROM Tabe 
UNION ALL 
SELECT Tabe.Prodotti, "3Autun" AS Stagioni, Tabe.Autunno FROM Tabe 
UNION ALL 
SELECT Tabe.Prodotti, "4Inver" AS Stagioni, Tabe.Inverno FROM Tabe
) AS T03
GROUP BY T03.Stagioni
PIVOT T03.Prodotti
;
 
 
 

In effetti potremmo migliorare molto se riuscissimo a far funzionare questa:
( Spremiti le meningi anche te )
 
data la tabella di nome "Tabe"  
con 1 solo campo di nome "Campo1"
 
--- Campo1
    aaa
    bbb
    ccc
 
applicandogli questa query 
 
SELECT 
Tabe.Campo1, 
Tabe.Coulumn(0) AS NomCam  <<--- Questo pero NON Funziona
FROM 
Tabe
;
 
 
Dovremmo arrivare a questo risultato
 
--- Campo1 ---- NomCam
    aaa         Campo1
    bbb         Campo1
    ccc         Campo1
 
.

 che ti ho menzionato
 
Eccoci qua.
=========================================
Private Sub Command6_Click()
Dim SQL As String, Q As QueryDef, db As Database, RS As Recordset
Dim i As Integer, SourceTable As String, CategoryField As String
Dim TargetTable As String
 
'----   Definizioni   -------
SourceTable = "TN_1"
TargetTable = "TN_2"
'----------------------------
 
Set db = CurrentDb
Set RS = db.OpenRecordset(SourceTable, dbOpenDynaset)
CategoryField = RS.Fields(0).Name
 
SQL = "TRANSFORM Sum(C03.CLI_1) AS SumX "
SQL = SQL & "SELECT C03." & SourceTable & " AS " & CategoryField & " "
SQL = SQL & "FROM (SELECT " & SourceTable & "." & CategoryField & ", "
For i = 1 To RS.Fields.Count - 2
    SQL = SQL & "'" & RS.Fields(i).Name & "' AS " & SourceTable & ", " 
& SourceTable & "."
    SQL = SQL & RS.Fields(i).Name & " FROM " & SourceTable & " "
    SQL = SQL & "UNION ALL SELECT " & SourceTable & ".Prodotti, "
Next
SQL = SQL & "'" & RS.Fields(i).Name & "' AS " & SourceTable & ", "
SQL = SQL & SourceTable & "." & RS.Fields(i).Name & " FROM " & 
SourceTable & ") AS C03 "
SQL = SQL & "GROUP BY C03." & SourceTable & " PIVOT C03." & 
CategoryField & ";"
 
On Error Resume Next
DoCmd.DeleteObject acQuery, "Z"
DoCmd.DeleteObject acTable, TargetTable
On Error GoTo 0
Set Q = CurrentDb.CreateQueryDef("Z", SQL)
CurrentDb.Execute "SELECT Z.* INTO " & TargetTable & " FROM Z;"
DoCmd.DeleteObject acQuery, "Z"
 
End Sub
=====================================
 
Mi pare che funzioni bene; è stato inoltre possibile passare solo due 
parametri:
1 - la SourceTable che dovrà contenere nel primo campo
    il CategoryName (nel nostro caso Prodotti) e in tutti
    gli altri campi successivi i dati che, trasposti,
    finiranno come CategoryName nel prinmo campo della TargetTable.
2 - TargetTable, solo un nome qualunque, senz'altra precisazione.
 
Quanto all'efficienza rispetto al codice VBA, vedrò di costruirmi una 
ponderosa SourceTable poi azionerò le due routine prendendone i tempi.
 
Bruno
 
 

 ponderosa SourceTable poi azionerò le due routine prendendone i tempi.
 
Ho provato con oltre 10000 record e 33 campi in SourceTable.
 
VBA = 0.22 secondi
Crosstab Query = 4.46 secondi
 
Il codice VBA è 20 volte più veloce.
 
Bruno
 
 

 ;
 
Questa proprio non riesco a farla andare, mi crea la query nella 
griglia QBE ma poi quando vado ad aprirla Access mi sbatte fuori.
Interpreto male i parametri?
 
La mia tabella (SourceTable) si chiama TS_1 ed è la seguente:
 
            --------------- Stagioni --------------
Prodotti    Primavera   Estate   Autunno   Inverno
P1            P1p        P1e       P1a       P1i
P2            P2p        P2e       P2a       P2i
P3            P3p        P3e       P3a       P3i
P4            P4p        P4e       P4a       P4i
P5            P5p        P5e       P5a       P5i
P6            P6p        P6e       P6a       P6i
 
Io sostituisco il tuo "Tabe" col mio "TS_1"... what else?
 
Bruno
 
 

> ... what else?
Nulla,    ho aggiunto le parentesi quadre per delimitare meglio
i nomi dei campi ma il problema non può essere quello
 
a me funziona con e senza parentesi quadre
 
TRANSFORM First([T03].[Primavera]) AS PrimP
SELECT [T03].[Stagioni]
FROM 
(
SELECT [TS_1].[Prodotti], "1Prima" AS Stagioni, [TS_1].[Primavera] FROM [TS_1] 
UNION ALL 
SELECT [TS_1].[Prodotti], "2Estat" AS Stagioni, [TS_1].[Estate] FROM [TS_1] 
UNION ALL 
SELECT [TS_1].[Prodotti], "3Autun" AS Stagioni, [TS_1].[Autunno] FROM [TS_1] 
UNION ALL 
SELECT [TS_1].[Prodotti], "4Inver" AS Stagioni, [TS_1].[Inverno] FROM [TS_1]
)  AS [T03]
GROUP BY [T03].[Stagioni]
PIVOT [T03].[Prodotti]
;
 
 
 
Magari prova a spezzarla nelle sue 2 query elementari:
 
---- La prima chiamala "T03"
SELECT [TS_1].[Prodotti], "1Prima" AS Stagioni, [TS_1].[Primavera] FROM [TS_1] 
UNION ALL 
SELECT [TS_1].[Prodotti], "2Estat" AS Stagioni, [TS_1].[Estate] FROM [TS_1] 
UNION ALL 
SELECT [TS_1].[Prodotti], "3Autun" AS Stagioni, [TS_1].[Autunno] FROM [TS_1] 
UNION ALL 
SELECT [TS_1].[Prodotti], "4Inver" AS Stagioni, [TS_1].[Inverno] FROM [TS_1]
;
 
---- La seconda chiamala come vuoi
TRANSFORM First([T03].[Primavera]) AS PrimP
SELECT [T03].[Stagioni]
FROM [T03] 
GROUP BY [T03].[Stagioni]
PIVOT [T03].[Prodotti]
; 
 
 
Attento pero ti ripeto quello che ti ho detto questa mattina:
A) impiega molte risorse 
B) è complessa da capire 
C) 
D) Non è sufficientemente collaudata  ( la ho provata solo 2 volte ieri sera ) 
---- e aggiungo -----
E) SE HAI DEI PRODOTTI DUPLICATI TI CONSIDERA SOLO IL PRIMO
 
 
Io in effetti mi sto "dilettando" a fare la trasposizione solo con SQL
come dal tuo 1° messaggio,
Ma hai sperimentato che VBA è 20 volte piu veloce
 

 con quasi due pagine di codice VBA, passando 4 parametri:
 
pagine video o stampate?
Due pagine stampate sono un /sacco/ di codice.
Quante linee (vere) di codice?
 
-- 
 
ac (x=y-1) e non uso il KillFile.
La violenza e' l'ultimo rifugio degli incapaci (Salvor Hardin)

 Quante linee (vere) di codice?
Penso non siano meno di 60-65 righe, al momento non sono in grado di 
contarle: la routine non è in questo computer.
 
Devi considerare che passo solo il nome della SourceTable, dalla quale 
ricavare poi il numero dei record e il numero der campi, poi vi debbo 
raggruppare gli eventuali doppioni.
Debbo cancellare la TargetTable, se esiste, e ricrerarla senza fornire 
altro dato che il nome.
Tutte sciocchezzine che costano righe di codice.
Indi passo alla scrittura di un array bidimensionale che poi leggo con 
inversione degli indici per andare a scrivere i dati trasposti sulla 
TargetTable.
Su quest'ultima fase son già tornato un paio di volte per tentar di 
omettere il passaggio dell'array... ma ancora non ci sono riuscito.
 
Any suggestions would be much appreciated.
Bruno
 
 

 con una crosstab query.
 
scusa ma secondo me le tabelle  sono concepite male, sono una visione di 
tipo worksheet.
 
in Access devi normalizzare ulteriormente, devi avere una tabella 
"lunga" in cui per ogni transazione hai una registrazione del tipo
 
progressivo      (tipo contatore)
codice_cliente	 (tipo testo)
codice_prodotto  (tipo testo)
QuantitaTransate (tipo numerico)
 
fatto questo, puoi fare tutte le query a campi incrociati che vuoi, 
prodotti nelle righe o nelle colonne e viceversa, e sommare e analizzare 
le quantita' transate. La vista tabellare e' solo un modo di presentare 
i risultati, ma le informazioni sottostante devono essere atomiche (una 
riga, una transazione).
 
Se invece le tabelle da traspondere (?) sono fissate a priori, perche' 
arrivano da una settore dell'azienda, allora come non detto.
 
nel tuo esempio i numeri sono casuali, giusto? Altrimenti per una cella 
del tipo (CLI_1, P1) e una del tipo (P1, CLI_I) mi aspetterei che il 
valore sia identico.
 
ciao
 
-- 
Guillotin n'avait coupé trop peux

 Any suggestions would be much appreciated.
In casi come questi ho sempre usato gli array associativi (collection)


 In casi come questi ho sempre usato gli array associativi (collection)
Questione di gusti: preferisco gli array multidimensionali ancorché più 
complicati da utilizzare.
Uso collection (o dictionary) per ottenere valori unici.
 
Bruno
 
 

 identico.
No non sono casuali.
Nel mio esempio P1 appare tre volte nella SourceTable, con tre valori 
diversi P1, CLI_1.
Nella tabella trasposta CLI_1, P1 appare per la somma di tali valori.
 
Bruno
 
 

 DoCmd.DeleteObject acQuery, "Z"
 
 
 
Elimini Query  "Z"
Elimini la tabella "TN_2"
Ricrei la query "Z"
Da "Z" con SELECT....INTO RiCrei la tabella "TN_2"
Elimini la query "Z"
A peggiorare le cose diciamo che "z" è una query a CampiIncrociati
     generata su una UNION 
     ( che è il prodotto cartesiano dei record x i campi )
 
 
 
potresti migliorare un po oppure ripartire da 0
 
se vuoi solo migliorare direi invece di eliminare e ricreare "Z"
semplicemente modificagli il suo testo SQL
     CurrentDb.QueryDefs("Z").SQL = Stringa
 
se vuoi ripartire da 0 allora farei:
- Un SELECT DISTINCT sui recird della 1° colonna per avere i nuovi campi
- Crei una tabella vuota con i nuovi campi
- popoli la tabella con un ciclo
 
 
 
 
 
 
 

 muscoli (o quel che ne rimane)...
 
Capito, allora tutto come non detto.
 
In una applicazione vera, oltretutto, ogni singola transazione avrebbe 
allungato la tabella delle singole transazioni, e poi sarebbe stato 
immediato riassumere e crosstabulare.


 Però non dovrebbe essere impossibile.
 
Ci sono riuscito... però ho terminato la scorta dei moccoli!
 
Si è ridotto il codice di 9 righe (che è un'inezia).
Ho fatto la prova con 1 000 000 di record in SourceTable:
con l'array 14.59 secondi
senza l'array 14.61.
 
Con l'array è più veloce di qualche centesimo
(che è un'altra inezia) ma soprattutto non carico in memoria
un milione di dati (che è la terza inezia, visto il costo
della RAM e i Giga che abbiamo normalmente a bordo).
 
Bruno
 
 

Mancini wrote on 16-11-13 :

È esattamente quello che ho fatto nel mio VBA, quali operazioni 
preliminari.
 
Ho perso quasi tutto il pomeriggio per eliminare quell'array
dal mio codice.
 
Però farò tesoro dei tuoi suggerimenti domani: m'interessa capirci un 
po' di più su quelle Crosstab-query...
 
Ciao
Bruno
 
 

 ' ( se lo usi sul 2003 devi rivedere i riferimenti )
[...]
 
Ho provato la tua procedura e funziona perfettamente.
Però su una tabella di Record x Campi = 1 000 000 x 32
la mia impiega 13.57 sec, la tua 688.55 sec.
Credo ci sia qualcosa da migliorare
nella sezione che t'ha fatto sudare...
 
Ti allego la mia così puoi fare anche tu
prove comparate di efficienza:
================================================
Public Sub Transpose_N()
'
' Access 2013/64    11-11-2013    Trasposizione.accdb
' Form: Form1    Button: Transpose (Numeric Fields)
'
' Trasposizione
' Trasforma la tabella TN_1 nella tabella TN_2, e VICEVERSA
'
' Per il VICEVERSA, cioè se ottenuta TN_2 si vuole ritornare a TN_1,
' occorre Modificare opportunamente i Valori SourceTbale e TargetTable, 
ed inoltre
' occorre invertire i valori di CategorySourceField e DataSourceField.
'
' Se la prima operazione è coi parametri:
' SourceTable = "TN_1"
' TargetTable = "TN_2"
' CategorySourceField = "Prodotti"
' DataSourceField = "Clienti"
'
' l'operazione inversa saà coi parametri:
' SourceTable = "TN_2"
' TargetTable = "TN_1"
' CategorySourceField = "Clienti"
' DataSourceField = "Prodotti"
'
'
' Tabella TN_1 (SourceTable)
'
'            ------ Clienti -------
'Prodotti    CLI_1   CLI_2   CLI_3
'P1           147      151     56
'P2            45       78     96
'P1            63       25     78
'P3           112       89     74
'P5           113       98     74
'P1            69       78    101
'P5           166      108     90
'
' Funziona solo se i dati di SourceTable sono numerici, ancorché ZERO o 
NULL
 
 
' Tabella TN_2 (TargetTable)
'
'          ---- Prodotti ----
'Clienti   P1   P2   P3   P5
'CLI_1    279   45  112  279
'CLI_2    254   78   89  206
'CLI_3    235   96   74  164
'
'
'
Dim db As Database, RS_Source As Recordset, T As Double
Dim i As Long, j As Long, k As Long, RS_Target As Recordset
Dim TargetTableDef As TableDef, SQL As String, RS As Recordset
Dim SourceTable As String, TargetTable As String
Dim CategorySourceField As String, DataSourceField As String
 
'------   Definizioni   ----------
SourceTable = "TN_1"
TargetTable = "TN_2"
CategorySourceField = "Prodotti"
DataSourceField = "Clienti"
'---------------------------------
 
T = Timer
DoCmd.Hourglass True
Set db = CurrentDb
Set RS_Source = db.OpenRecordset(SourceTable, dbOpenDynaset)
 
On Error Resume Next
DoCmd.DeleteObject acTable, TargetTable
On Error GoTo 0
 
' Crea la nuova tabella
Set TargetTableDef = db.CreateTableDef(TargetTable)
' Crea il primo campo (text) dei record di TargetTable (quello che 
indica
' i campi relazionati dal 2° all'n° in SourceTable)
TargetTableDef.Fields.Append 
TargetTableDef.CreateField(DataSourceField, dbText, 255)
 
' Definisce i valori unici da porre in TargetTable
SQL = "SELECT " & SourceTable & "." & CategorySourceField & " FROM " & 
SourceTable & " "
SQL = SQL & "GROUP BY " & SourceTable & "." & CategorySourceField & ";"
Set RS = db.OpenRecordset(SQL, dbOpenDynaset)
 
' Crea i campi successivi (Numeric)
Do Until RS.EOF
    k = k + 1
    TargetTableDef.Fields.Append 
TargetTableDef.CreateField(RS(CategorySourceField), dbLong)
    RS.MoveNext
Loop
db.TableDefs.Append TargetTableDef
Set RS_Target = db.OpenRecordset(TargetTable, dbOpenDynaset)
 
SQL = "SELECT " & SourceTable & "." & CategorySourceField & ", "
For i = 1 To RS_Source.Fields.Count - 2
    SQL = SQL & "Sum(" & SourceTable & "." & RS_Source.Fields(i).Name & 
") "
    SQL = SQL & "AS " & RS_Source.Fields(i).Name & ", "
Next
SQL = SQL & "Sum(" & SourceTable & "." & RS_Source.Fields(i).Name & ") 
"
SQL = SQL & "AS " & RS_Source.Fields(i).Name & " "
SQL = SQL & "FROM " & SourceTable & " "
SQL = SQL & "GROUP BY " & SourceTable & "." & CategorySourceField & ";"
Set RS_Source = db.OpenRecordset(SQL, dbOpenDynaset)
'SQL_TO_QUERY SQL, "Z"    ' per vedere cosa fa...
 
' APPEND i record necessari in TargetTable compilando i campi Categoria
For i = 2 To RS_Source.Fields.Count
    RS_Target.AddNew
    RS_Target(0) = RS_Source(i - 1).Name
    RS_Target.Update
Next
 
' Compila i campi Dati in TargetTable
RS_Source.MoveFirst: k = 0
RS_Target.MoveFirst: j = 0
    Do Until RS_Target.EOF
        j = j + 1
        RS_Source.MoveFirst: k = 0
        Do Until RS_Source.EOF
            k = k + 1
            RS_Target.Edit
            RS_Target(k) = RS_Source(j)
            RS_Target.Update
            RS_Source.MoveNext
        Loop
        RS_Target.MoveNext
    Loop
 
DoCmd.Hourglass False
MsgBox "Time = " & Timer - T
 
End Sub
=================================================
 
Bruno
 
 

 prove comparate di efficienza:
 
Si volentieri,  
Immaginavo di essere basso a prestazioni perche ho usato Nz....  DSum..... 
e poi per ogni record ho fatto un INSERT distinto
 
In effetti già pensavo di ristrutturarla 
usando solamente RecordSet con Edit.....
 
Oggi pero sono SuperOccupato al recupero del server aziendale.
   Ho smontato gli HD e sto recuperando i dati  
       ( fortunatamente il problema non era negli HD )
   dopo lo riformatto integralmente
   reinserisco i dati
   Domattina deve essere pronto
 
Nei prossimi giorni tentero di superare il tuo 13.57
che mi sembra comunque ottimo per 10.000.000 di record
Ti faccio sapere...
 
 
Saluti
 
 

> Nei prossimi giorni tentero di superare il tuo 13.57
 
Mi arrendo .... ,
 
Ho fatto diversi tentativi ma sono sempre superiore (come tempo)
 
Complimenti

Mancini used his keyboard to write :

A volte capita di prenderci...
 
Grazie
Bruno