Query a campi incrociati con nomi delle colonne fissi

di
maxgrana
il
7 risposte
M
maxgrana
Pochi posts
Iscritto da
mar, 2017
Messaggi:
46

Query a campi incrociati con nomi delle colonne fissi

Ciao a tutti,
ho la seguente query che genera una tabella a campi incrociati avente come titolo delle colonne I dati del campo MONTH_LABEL_YYYYMM

TRANSFORM Sum(Qry_estrazioneFinalCmmtApproved.[QUANTITY]) AS SumOfQUANTITY
SELECT Qry_estrazioneFinalCmmtApproved.FEG, Qry_estrazioneFinalCmmtApproved.PRODUCT_CODE_OUT
FROM Qry_estrazioneFinalCmmtApproved
GROUP BY Qry_estrazioneFinalCmmtApproved.FEG, Qry_estrazioneFinalCmmtApproved.PRODUCT_CODE_OUT
ORDER BY Qry_estrazioneFinalCmmtApproved.FEG, Qry_estrazioneFinalCmmtApproved.PRODUCT_CODE_OUT, Qry_estrazioneFinalCmmtApproved.MONTH_LABEL_YYYYMM
PIVOT Qry_estrazioneFinalCmmtApproved.MONTH_LABEL_YYYYMM;

dovendo mantenere I titoli delle colonne fissi, indipendentemente dal nome del mese, per poter allegare successivamente il risultato ad un'altra tabella che andro' a visualizzare in un form, ho seguito il suggerimento del sito di Microsoft di aggiungere alla fine

IN (P01,P02,P03,...) fino a P12

Pero' spariscono tutti I valori come se avessi solamente valori "NULL", dov'e' che sbaglio?
Premetto che in alcuni casi la tabella a campi incrociati generata prima della modifica presenta in alcuni mesi valori NULL che non riesco a far sparire con la funzione Nz scritta sia come
TRANSFORM Sum(Nz(Qry_estrazioneFinalCmmtApproved.[QUANTITY])) AS SumOfQUANTITY

Che

TRANSFORM Nz(Sum(Qry_estrazioneFinalCmmtApproved.[QUANTITY])) AS SumOfQUANTITY..

Grazie

7 Risposte

  • willy55
    willy55
    Molto attivo
    Iscritto da
    nov, 2015
    Messaggi:
    836

    Re: Query a campi incrociati con nomi delle colonne fissi

    Non è chiaro se hai problemi con la sintassi o con i calcoli (nella query a campi incrociati ove vai a specificare dei valori fissi per le intestazioni di colonna).
    Visto che indichi che fai riferimento a suggerimenti sul sito Microsoft ti cito alcuni dei loro documenti di riferimento (ivi riportati):

    https://support.office.com/en-us/article/TRANSFORM-Statement-c3a2361a-9f3f-4213-bc0a-65ed70031619


    Come puoi osservare la clausola IN ... accetta un elenco di valori, ma tu, nell'esempio, indichi delle stringhe [IN (P01,P02,P03,...) fino a P12] che, quindi, vanno racchiusi fra apici (mentre se fossero numerici non sarebbe necessario) come puoi vedere anche nei diversi link sotto-riportati:
    http://stackoverflow.com/questions/12498570/crosstab-queries-using-fixed-values-to-create-additional-columns
    http://www.tek-tips.com/viewthread.cfm?qid=111182
    http://stackoverflow.com/questions/937797/how-do-you-do-a-crosstab-query-in-access-with-a-fixed-amount-of-columns
    http://blogannath.blogspot.it/2010/02/microsoft-access-tips-tricks-crosstab.html

    In merito ai calcoli nella cross-table puoi leggere:
    http://allenbrowne.com/ser-67.htm

    Inoltre qualora la query a campi incrociati debba produrre un report puoi visionare, nel Sito Comune, il seguente demo:
    4.33 Report a campi variabili a runtime da query a campi incrociati
    http://web.mclink.it/MC5884/index.htm
  • A
    Angelo_Tbp
    Utente Attivo
    Iscritto da
    gen, 2014
    Messaggi:
    439

    Re: Query a campi incrociati con nomi delle colonne fissi

    Ciao, forse sbaglio, ma credo che per eliminare i valori null (quindi mi riferisco al solo problema della funz nz) potresti considerare questo:
    Una query a campi incrociati mostra, nell'intersezione tra l'int. Di riga e l'intest di colonna un determinato valore per cui trovi una corrispondena... Quando la corrispondenza é presente ti mette il valore.. Quando non c'è ti mette null.. In considerazione del fatto che il il numero e il titolo delle colonne da visualizzare é sempre lo stesso, forse di conviene agire sulla query che origina la query a campi incrociati per far si da aggiungerti anche i record che non trovano corrispondenza e nel campo che utilizzarsi come valore metti 0. Oppure, nel caso tu abbia gia tutte le corrispondenze ma alcune hanno il campo usato come valore settato a null, in questo caso, potrai settarlo a 0 prima di farlo leggere dalla query a campi incrociati. Spero di non essere stato troppo complicato...
  • M
    maxgrana
    Pochi posts
    Iscritto da
    mar, 2017
    Messaggi:
    46

    Re: Query a campi incrociati con nomi delle colonne fissi

    Grazie a tutti.
    Siete una fonte preziossima di insegnamenti.

    Sono riuscito a far sparire I campi con valore "null" utilizzando il metodo di Angelo_Tbp (Ricerca dei record mancanti e successiva query di append), ma quando definisco il nome delle colonne che desidero vedere ("P01", "P02" etc) sia che lo faccia nelle proprieta' della query (come da sito Microsoft) che in visualizzazione SQL, ho I nomi delle colonne corrette ma non ho piu' nessun valore all'incrocio fra Riga e Colonna

    TRANSFORM Sum(FinalCmmtApproved.SumOfQUANTITY) AS SumOfSumOfQUANTITY
    SELECT FinalCmmtApproved.FEG
    FROM FinalCmmtApproved
    GROUP BY FinalCmmtApproved.FEG
    PIVOT FinalCmmtApproved.MONTH_LABEL_YYYYMM In ("P01","P02","P03","P04","P05","P06","P07","P08","P09","P10","P11","P12");

    Max
  • A
    Angelo_Tbp
    Utente Attivo
    Iscritto da
    gen, 2014
    Messaggi:
    439

    Re: Query a campi incrociati con nomi delle colonne fissi

    Ciao, sto cercando di comprendere meglio...

    Ma i valori P01, P02 ecc. Sono realmente presenti tra i valori della colonna che, nella query a campi incrociati, hai scelto come intestazione di colonna? In caso negativo: Puoi fare un esempio dei valori presenti in questa colonna?

    Se fai una normalissima query a campi incrociati come da composizione guidata ottieni dei risultati coerenti? In caso affermativo: Vi sono anche le colonne P01, P02 ecc?

    Se quanto sopra devesse essere tutto affermativo basterebbe mettere nel criterio della colonna che, nella query a campi incrociati hai settato a intestazione di colonna, "P1" or "P2 or "P3" or ... "P_num"...

    Ovviamente il criterio sarebbe da settare nella query usata come origine per la query a campi incrociati...
  • M
    maxgrana
    Pochi posts
    Iscritto da
    mar, 2017
    Messaggi:
    46

    Re: Query a campi incrociati con nomi delle colonne fissi

    La tabella a campi incrociati, nasce per il mese corrente con il nome delle colonne che identificano i prossimi dodici mesi nel formato 201704 per Aprile 2017, 201705 per Maggio etc con un formato short-text. Il mese prossimo nascerà come 201705 per Maggio, 201706 per Giugno etc.

    Nei dati di origine non ho da nessuna parte "P01" etc, sono il nome che ho dato alle colonne di un'altra tabella, alla quale dovrò allegare successivamente il risultato di questa a campi incrociati, per poter rendere indipendente dal nome dei mesi il form sul quale il mio capo lavorerà.

    Se dovessi far apparire i nomi che voglio io nei dati origine, come mi sembra di aver capito che sia necessario fare, non ho idea di come scrivere una funzione in un campo calcolato che a Marzo leghi il valore P01 con il termine 201704, ad Aprile lo leghi con il termine 201705, a Maggio con il termine 201706 e così via.
  • A
    Angelo_Tbp
    Utente Attivo
    Iscritto da
    gen, 2014
    Messaggi:
    439

    Re: Query a campi incrociati con nomi delle colonne fissi

    Scusami ...forse ho capito male... Hai bisogno di un campo calcolato che trasformi (relativamente a marzo 2017):

    201704 in P01?
    201705 in P02?
    .....
    201715 in P12 (dodici mesi)?
    Oppure il max é 201803 da far corrispondere comunque a P12?

    Hai dei campi in cui scrivi il mese corrente, l'anno.. oppure la data (e se si che valori assumono?) oppure va calcolato e ricalcolato in base alla data reale?..

    Le domande sopra sono finalizzate a capire come deve essere fatto il calcolo..

    tanto per provare a "lanciarmi" nel buio prova questo come campo calcolato da aggiungere alla query che è all'origine della query a campi incrociati: 
    IntColCustom: "P" & IIf((IIf(CInt(Mid([MONTH_LABEL_YYYYMM];1;4))=Year(Date());CInt(Mid([MONTH_LABEL_YYYYMM];5;2))-Month(Date());12-Month(Date())+CInt(Mid([MONTH_LABEL_YYYYMM];5,2))))<10;"0" & CStr(IIf(CInt(Mid([MONTH_LABEL_YYYYMM];1;4))=Year(Date());CInt(Mid([MONTH_LABEL_YYYYMM];5;2))-Month(Date());12-Month(Date())+CInt(Mid([MONTH_LABEL_YYYYMM];5,2))));CStr(IIf(CInt(Mid([MONTH_LABEL_YYYYMM];1;4))=Year(Date());CInt(Mid([MONTH_LABEL_YYYYMM];5;2))-Month(Date());12-Month(Date())+CInt(Mid([MONTH_LABEL_YYYYMM];5,2)))))
    NB:
    Year(Date()) corrisponde all'anno corrente ovvero a 2017
    Month(Date()) corrisponde al mese corrente ovvero a 3
    quindi se per esempio il mese corrente e l'anno corrente variano in base alla selezione di una combobox non devi fare altro che sostituire solo la relativa parte di codice relativa al mese e anno corrente con il riferimento nella maschera... solitamente non mi applico nel codice... ma questa mi intrigava un po...
  • M
    maxgrana
    Pochi posts
    Iscritto da
    mar, 2017
    Messaggi:
    46

    Re: Query a campi incrociati con nomi delle colonne fissi

    Grazie infinite.

    Sei riuscito ad interpretare perfettamente le parti buie della mia spiegazione perche' il risultato e' proprio quello che cercavo.
    Ora devo dar fondo alla mia calma per interpretare il codice ed imparare.

    Grazie ancora
Devi accedere o registrarti per scrivere nel forum
7 risposte