Trasposizione con crosstab query

[Bruno Campanini]

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

[Mancini]

Non è facile .....

ci penso un pò su ...

[Bruno Campanini]

Mancini used his keyboard to write :

> Non è facile .....
>
> 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.

[Mancini]

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 .......

[Bruno Campanini]

Mancini formulated on Thursday :

La query la adatto alle varie tabelle, nome campi, numero campi etc
con un For...Next in VBA. Quello non è un problema.
Poi la tabella finale la creo dalla UNION query.

Mentre mi dedico al contorno (domani!) per far sì che il tutto
funzioni automaticamente dopo l'inserimento dei 4 parametri
che ti ho menzionato, tu, se vuoi nel frattempo tenere al caldo
le meningi, fa' lo stesso lavoro di trasposizione su questa tabella:

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

in modo da ottenere:

Stagioni P1 P2 P3 P4 P5 P6
---------------------------------------------
Primavera P1p P2p P3p P4p P5p P6p
Estate P1e P2e P3e P4e P5e P6e
Autunno P1a P2a P3a P4a P5a P6a
Inverno P1i p2i P3i P4i P5i P6i

> Ma ci stiamo ancora lavorando .......

Io ho fatto qualche cambiamento di... estetica, ma è già
perfetta così!

Complimenti, sei stato bravissimo.

Bruno

[Mancini]

> Mentre mi dedico al contorno (domani!) per far sì che il tutto
> funzioni automaticamente dopo l'inserimento dei 4 parametri
> che ti ho menzionato, tu, se vuoi nel frattempo tenere al caldo
> 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
;

[Mancini]

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

.

[Bruno Campanini]

Bruno Campanini wrote :

> Mancini formulated on Thursday :
>> 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.
> La query la adatto alle varie tabelle, nome campi, numero campi etc
> con un For...Next in VBA. Quello non è un problema.
> Poi la tabella finale la creo dalla UNION query.
>
> Mentre mi dedico al contorno (domani!) per far sì che il tutto
> funzioni automaticamente dopo l'inserimento dei 4 parametri
> 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

[Bruno Campanini]

Bruno Campanini wrote on 15-11-13 :

[...

> Quanto all'efficienza rispetto al codice VBA, vedrò di costruirmi una
> 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

[Bruno Campanini]

Mancini explained on 15-11-13 :

[...]

> 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
> ;

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

[Mancini]

> ... 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

[Alessandro Cara]

Il 14/11/2013 11.58, Bruno Campanini ha scritto:
[cut]
Ciao Bruno,

> 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)

[Bruno Campanini]

Alessandro Cara formulated the question :

> Il 14/11/2013 11.58, Bruno Campanini ha scritto:
> [cut]
> Ciao Bruno,
>
>> 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?

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

[Giacobino da Tradate]

Il 14/11/2013 11.58, Bruno Campanini ha scritto:

> Prodotti CLI_1 CLI_2 CLI_3
> ---------------------------------
> P1 147 151 56

> Clienti P1 P2 P3 P5
> ------------------------------
> CLI_1 279 45 112 279

> Mi sembra strano nom possa ottenersi lo stesso risultato
> 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

[Alessandro Cara]

Il 16/11/2013 0.31, Bruno Campanini ha scritto:
> Alessandro Cara formulated the question :
>> Il 14/11/2013 11.58, Bruno Campanini ha scritto:
>> [cut]
>> Ciao Bruno,
>>
>>> 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?
> 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.

Il numero di righe da te citato e' /congruente/ . Per fare tutte le
attivita' connesse so che ci vuole 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.

E questo come lo fai? Preinizializzi la lunghezza dell'array?

> 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.

Questa omissione la vedo complicata

>
> Any suggestions would be much appreciated.

In casi come questi ho sempre usato gli array associativi (collection)

[Bruno Campanini]

It happens that Alessandro Cara formulated :

> Il 16/11/2013 0.31, Bruno Campanini ha scritto:
>> Alessandro Cara formulated the question :
>>> Il 14/11/2013 11.58, Bruno Campanini ha scritto:
>>> [cut]
>>> Ciao Bruno,
>>>
>>>> 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?
>> 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.
>
> Il numero di righe da te citato e' /congruente/ . Per fare tutte le attivita'
> connesse so che ci vuole codice ;-)

Per l'esattezza, le ho contate, sono 69 (nette).

>
>> Indi passo alla scrittura di un array bidimensionale che poi leggo con
>> inversione degli indici per andare a scrivere i dati trasposti sulla
>> TargetTable.
>
> E questo come lo fai? Preinizializzi la lunghezza dell'array?

Sì, certo.
Le sue dimensioni sono date dal numero dei record (compattati) e dal
numero dei campi Dati di SourceTable.

>
>> 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.
> Questa omissione la vedo complicata

Complicata sì, almeno per me.
Però non dovrebbe essere impossibile.

>> Any suggestions would be much appreciated.
> 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

[Bruno Campanini]

Giacobino da Tradate used his keyboard to write :

> Il 14/11/2013 11.58, Bruno Campanini ha scritto:
>
>> Prodotti CLI_1 CLI_2 CLI_3
>> ---------------------------------
>> P1 147 151 56
>
>
>> Clienti P1 P2 P3 P5
>> ------------------------------
>> CLI_1 279 45 112 279
>
>> Mi sembra strano nom possa ottenersi lo stesso risultato
>> con una crosstab query.
>
> scusa ma secondo me le tabelle sono concepite male, sono una visione di tipo
> worksheet.

Che però sono l'espressione normale per le tabelle a doppia entrata

> 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).

Sì normalizzando il database sarebbe stato un gioco da ragazzi.
Sarei dovuto però ricorrere a una tabella d'appoggio o a un array.
Non ho voluto farlo.

> Se invece le tabelle da traspondere (?)

Se il punto interrogativo denota l'incertezza sul lemma, dico la mia:
trasporre.

> sono fissate a priori, perche'
> arrivano da una settore dell'azienda, allora come non detto.

Si tratta di un'esercitazione puramente teorica, per tenere allenati i
muscoli (o quel che ne rimane)...

> 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.

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

[Mancini]

> Si tratta di un'esercitazione puramente teorica, per tenere allenati i
> muscoli (o quel che ne rimane)...

Sono importanti le esercitazioni teoriche ( soprattutto se difficili )
ma proprio per questo se mi permetti ti
espongo dei dubbi sul codice che utilizzi ( Che ricopio sotto per chiarezza )

> 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"

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

[Giacobino da Tradate]

Il 16/11/2013 11.24, Bruno Campanini ha scritto:


>> scusa ma secondo me le tabelle sono concepite male, sono una visione
>> di tipo worksheet.

> Che però sono l'espressione normale per le tabelle a doppia entrata

Senz'altro, ma sono degli aggregati, dei report finali, dei punti di
vista. I dati iniziali vanno registrati in maniera diversa,
disaggregata, altrimenti tanto vale che lavori in Excel.

> traspondere (?)

> trasporre.

Porca pupazza che non mi veniva la parola. Ero fissato con i trasponder
e to traspond. grazie.

> Si tratta di un'esercitazione puramente teorica, per tenere allenati i
> 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.

[Bruno Campanini]

Bruno Campanini expressed precisely :

>>> 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.
>> Questa omissione la vedo complicata
> Complicata sì, almeno per me.
> 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

[Bruno Campanini]

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

[Bruno Campanini]

Mancini was thinking very hard :

> Anch'io mi sono allenato un po dei muscoletti che mi sono rimasti
>
> ho provato a rifarla totalmente senza query cross ( che non mi piacciono )
>
>
> Public Function Incrocia()
> ' testato con Access2013
> ' ( 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

[Mancini]

> Ti allego la mia così puoi fare anche tu
> 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

[Mancini]

> Nei prossimi giorni tentero di superare il tuo 13.57

Mi arrendo .... ,

Ho fatto diversi tentativi ma sono sempre superiore (come tempo)

Complimenti

[Bruno Campanini]

Mancini used his keyboard to write :

A volte capita di prenderci...

Grazie
Bruno