RISOLTO: Aggregazione dati

di il
8 risposte

RISOLTO: Aggregazione dati

Ciao a tutti.
Ho un problema che non so definire, provo a descriverlo
Devo fare un conteggio relativo ai materiali infiammabili presenti in azienda per calcolare il carico di incendio, devo arrivare a calcolare la quantità media (sugli ultimi 3 anni) del peso di questi materiali, uno per uno.
Ho una tabella dei materiali con:

  • codice articolo;
  • peso netto unitario.


Inoltre ho accesso alla tabella del gestionale in cui sono salvati i materiali ingresso, ossia le varie bolle di ingresso (dall'inizio dei tempi), questa tabella ha i campi:

  • data bolla ingresso;
  • quantità del materiale;
  • codice articolo.

Incrociando le due tabelle e filtrando solo i dati degli anni 2020, 2021, 2022 (il filtro è dinamico sulla base della data di interrogazione) riesco a stabilire i pesi totali, articolo per articolo ad ogni ingresso.

Ora, per fare i calcoli successivi, devo sommare le quantità di ogni articolo per ogni anno e fare la media, ad esempio:

ArticoloAnnoTotali
A202050
A202160
A202240
Media50

questo valore medio (per ogni articolo) è la base per fare altri calcoli dei carichi di incendio e le classificazione del rischio di ogni locale.

Anni fa avevo fatto un DB che creava un report in automatico area per area e restituiva il livello di rischio, ma avevo una tabella delle quantità aggiornata a mano in cui per ogni articolo inserivo l'anno e la quantità annua.
Avendo tutti i dati dal database del gestionale vorrei automatizzare il lavoro per non dover ripetere i calcoli a mano tutte le volte, infatti gli articoli infiammabili sono diverse centinaia e il lavoro diventa tedioso.

Cerco qualche suggerimento per superare lo scoglio del dato mancante ed inserirlo nella serie di altre query e report che terminano la valutazione.  

Il calcolo successivo è un po' macchinoso, ma ero riuscito ad avere un report con la valutazione del rischio incendio sia locale per locale che complessiva, in base ai criteri stabiliti dal ministero (mi hanno girato un documento che ho studiato e ho fatto il DB). Devo modificare il DB ma così posso avere un aggiornamento in tempo reale dei dati.

Grazie in anticipo

8 Risposte

  • Re: RISOLTO: Aggregazione dati

    Mi è venuta un'idea, ma è molto macchinosa, ditemi se ha senso:

    1. Creo una tabella di appoggio vuota;
    2. analizzo la query che incrocia e filtra i dati con del codice VBA in cui calcolo i totali che mi servono;
    3. alimento la tabella di appoggio con il valore medio calcolato al punto precedente;
    4. continuo con l'elaborazione come nella versione con i dati aggiornati a mano;
    5. genero il report;
    6. alla chiusura del report cancello i dati della tabella di appoggio che resta vuota.

    nel punto 2 ciclo record per record, una volta ottenuti i totali per anno (magari creo un array con i totali n-1, n-2, n-3 e poi la media su questi), al termine dei questa elaborazione scrivo il record nella tabella di appoggio e passo all'articolo successivo.

    Come dicevo è macchinoso, ma in linea di principio dovrebbe funzionare.

    Nel cercare informazioni ho trovato le query a campi incrociati, ma non credo che possano fare al caso mio, visto che devo poi collegare questa query ad un'altra query per continuare l'elaborazione.

  • Re: RISOLTO: Aggregazione dati

    Il gestionale non estrapola l'indice di rotazione e la giacenza media per periodo?

  • Re: RISOLTO: Aggregazione dati

    Non credo, penso che faccia qualcosa a livello di interfaccia grafica, ma non rende disponibile il dato nelle tabelle. Io ho accesso solo a quelle via odbc

  • Re: RISOLTO: Aggregazione dati

    Ti manca qualche informazione, oppure hai espresso male quello che vuoi ottenere

    Prima scrivi che vuoi ottenere “..conteggio relativo ai materiali infiammabili presenti in azienda..”

    Poi scrivi che hai accesso alle informazioni di ‘ingresso’ di questi materiali

    Ma le informazioni di ‘uscita’ non ci sono?  Voglio dire, questi materiali vengono solamente aggiunti alle esistenze attuali, o c'e' qualche procedura per sottrarli (vendita, rifiuto, ecc…) ?

    O forse sono materiali tipo solventi, colle, ecc..  che vengono usati durante la produzione?

    Ad ogni modo, se hai sia i prodotti che gli acquisti basterebbe semplicemente aggiungere un campo su tabella prodotti per distinguere quelli infiammabili rispetto agli altri

    Una volta che riesci a selezionare solo i prodotti che ti interessano, ti basta selezionare le righe acquisti solo prodotti infiammabili, raggruppando per codice prodotto, e poi per anno della data documento

  • Re: RISOLTO: Aggregazione dati

    Ciao, grazie per la risposta.

    Il problema non sono i materiali in uscita, la giacenza media di questi la calcolo io a posteriori. Inoltre considera che le tabelle del gestionale io le posso solo vedere (sono in lettura) non posso aggiungere campi. La selezione di ciò he è infiammabile o meno la faccio io con una tabella di appoggio e una query. 

    Quindi in questo momento il conteggio della merce in uscita non è influente.

    Io devo conteggiare la quantità annua di alcuni materiali, riesco a farlo ad esempio con una query a campi incrociati (che in excel è chiamata pivot), poi devo fare la media sui 3 anni e questo valore usarlo per fare altri calcoli.

  • Re: RISOLTO: Aggregazione dati

    Non si capisce qual è il problema.

    Una query restituisce una tabella e può aggiungere campi.

    Magari se metti un esempio dei campi delle tabelle.

  • Re: RISOLTO: Aggregazione dati

    27/07/2023 - oierpa ha scritto:


    Ciao, grazie per la risposta.

    Il problema non sono i materiali in uscita, la giacenza media di questi la calcolo io a posteriori. Inoltre considera che le tabelle del gestionale io le posso solo vedere (sono in lettura) non posso aggiungere campi. La selezione di ciò he è infiammabile o meno la faccio io con una tabella di appoggio e una query. 

    Quindi in questo momento il conteggio della merce in uscita non è influente.

    Io devo conteggiare la quantità annua di alcuni materiali, riesco a farlo ad esempio con una query a campi incrociati (che in excel è chiamata pivot), poi devo fare la media sui 3 anni e questo valore usarlo per fare altri calcoli.

    Va be, che il flag ‘materiale infiammabile’ sia nel db principale o nella tua tabella appoggio poco importa, l'importante e' che riesci a distinguere le righe acquisti con materiale infiammabile dalle altre, e da quel che scrivi mi sembra tu lo possa fare

    A questo punto, se raggruppi per codice_prodotto puoi vedere tante righe, ognuna con un prodotto diverso, e per ogni riga avrai la quantita (sum(quantita) as qta_complesiva)

    Se raggruppi per codice_prodotto e per year(data_documento) otterrai la quantita di ogni prodotto in ogni anno, esattamente un risultato simile alla tabellina che hai messo al primo posto, eccetto ultima riga con la media

    Se questo risultato lo metti in un report, con qualche riga di codice puoi conteggiare in una variabile locale il numero righe delle stesso codice_prodotto, ti puoi calcolare e visualizzare anche la riga con la media

  • Re: RISOLTO: Aggregazione dati

    Ciao a tutti.

    Ho risolto applicando il metodo del mio secondo post, riporto il codice nel caso potesse servire ad altri o per chi ha voglia di proporre migliorie/ osservazioni/ suggerimenti:

    
    Public Sub CalcolaMediaMaterialiInfiammabiliR1()
        On Error GoTo ErrorHandler
    	'creo un file di testo così posso studiarlo con calma se quelcosa non va, avendo tanti dati debug.print è limitativo
        Dim fP As String
        fP = "c:\tmp\debugoutput.txt"
        SvuotaFileDiTesto fP
        'variabili per monitorare il tempo di elaborazione
        Dim stTime As Double
        Dim endTime As Double
        Dim deltaTime As Double
            
        Dim rsMaterials As ADODB.Recordset 'tab elenco dei materiali
        Dim rsBEM As ADODB.Recordset       'tab elenco bolle mat. ingresso
        Dim rsTabTmp As ADODB.Recordset    'tabella di appoggio
        Dim strSQL1 As String              'sql1
        Dim strSQL2 As String			   'sql2
      
        
        MsgBox "L'elaborazione è in corso...", vbInformation, "Avviso"
        
        stTime = Timer 			'salvo l'inizio elaborazione
        
        ' Imposta il nome delle tabelle e dei campi
        Const nomeTabellaMateriali As String = "SFTMaterialiDati" 'tabella con elenco materiali
        Const nomeTabellaBEM As String = "QQuantBEMR1"			  'query fra tab. bolle e tab materiali
        Const nomeTabellaTemporanea As String = "tblTMPMat"		  'tab. temp.	
        Const campoCodiceArticoloMateriali As String = "ESI_ref"  'codice articolo  
        Const campoCodiceArticoloBEM As String = "C_ARTICOLO"	  'campo codice articolo in tabella bolle
        
        ' Ottieni la connessione corrente al database (si può eliminare)
        Dim conn As ADODB.Connection
        Set conn = CurrentProject.Connection
        
        ' Ottieni l'anno corrente
        Dim annoCorrente As Integer
        annoCorrente = Year(Date)
        
        ' Ottieni i dati dei materiali infiammabili di tipo 2 dalla tabella dei materiali
        strSQL1 = "SELECT * FROM " & nomeTabellaMateriali & " WHERE Tipo = 2"
        Set rsMaterials = New ADODB.Recordset
        rsMaterials.Open (strSQL1), CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        
        'cancello tutti i dati della tabella temporanea
        CurrentDb.Execute "DELETE * FROM  " & nomeTabellaTemporanea
        
        ' connessione alla tabella temporanea
        Set rsTabTmp = New ADODB.Recordset
        rsTabTmp.Open (nomeTabellaTemporanea), CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        
    	'connessione alla tabella (o query) delle bolle   
        strSQL2 = "SELECT * FROM " & nomeTabellaBEM & " WHERE year(DT_BEM)>=" & Year(Date) - 3 & " AND year(DT_BEM)< " & Year(Date)
        Set rsBEM = New ADODB.Recordset
        rsBEM.Open (strSQL2), CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        'controllo del numero di bolle filtrate, queste tre righe si possono cancellare
        rsBEM.MoveLast
        Debug.Print "Conteggio bolle: " & rsBEM.RecordCount
        WFile "Conteggio bolle: " & rsBEM.RecordCount
        
        ' Inizializza l'array delle quantità totali a zero (potrebbe non servire)
        For i = 0 To 2
            qtaTotali(i) = 0
        Next i
        
        ' Calcola gli anni precedenti a quello corrente
        Dim annoNmeno1 As Integer
        Dim annoNmeno2 As Integer
        Dim annoNmeno3 As Integer
    
        annoNmeno1 = annoCorrente - 1
        annoNmeno2 = annoCorrente - 2
        annoNmeno3 = annoCorrente - 3
    
        ' Cicla attraverso i materiali infiammabili di tipo 2
        Do While Not rsMaterials.EOF
            Dim codiceArticolo As String
    
            codiceArticolo = rsMaterials(campoCodiceArticoloMateriali).Value
            Debug.Print "Articolo: " & codiceArticolo
            WFile "Articolo: " & codiceArticolo
        
        ' Inizializza l'array delle quantità per ogni anno a zero
        Dim qtaAnno(2) As Single
        For i = 0 To 2
            qtaAnno(i) = 0
            Debug.Print "Iniz. qtaAnno per art.: " & codiceArticolo & " - qtaAnno(" & i & ")= " & qtaAnno(i)
            WFile "Iniz. qtaAnno per art.: " & codiceArticolo & " - qtaAnno(" & i & ")= " & qtaAnno(i)
        Next i
          
        For i = 0 To 2
           Dim anno As Integer
           Dim qta As Single
           qta = 0
           anno = Year(Date) - (i + 1)
           
           Debug.Print "Inizio conteggio " & codiceArticolo & " - qtaAnno(" & i & ")=  " & qtaAnno(i)
           WFile "Inizio conteggio " & codiceArticolo & " - qtaAnno(" & i & ")=  " & qtaAnno(i)
           rsBEM.Filter = "ESI_Ref ='" & codiceArticolo & "' AND anno= " & anno
           'rsBEM.MoveLast 'rimosso perché se non ci sono record crea errore
           
           If Not rsBEM.EOF Then
              rsBEM.MoveFirst
              ' Calcola le quantità totali per ogni anno
              Do While Not rsBEM.EOF
                 qta = rsBEM("peso_tot").Value
                 qtaAnno(i) = qtaAnno(i) + qta
                 Debug.Print "Articolo: " & codiceArticolo & " - qta: " & qta
                 WFile "Articolo: " & codiceArticolo & " - qta: " & qta & " DDT n°" & rsBEM.Fields("N_BEM").Value & " del " & rsBEM.Fields("dt_BEM").Value & " item " & rsBEM.Fields("N_ITEM").Value
                 rsBEM.MoveNext
              Loop
              Debug.Print "Anno: " & i & " - Articolo: " & codiceArticolo & " - anno: " & anno & " - qtaAnno: " & qtaAnno(i)
              WFile "Anno: " & i & " - Articolo: " & codiceArticolo & " - anno: " & anno & " - qtaAnno: " & qtaAnno(i)
              rsBEM.Filter = ""
           Else
              rsBEM.Filter = ""
           End If
        Next i
        
            ' Calcola la quantità media per il materiale corrente
            Dim qtaMedia As Single
            qtaMedia = Round((qtaAnno(0) + qtaAnno(1) + qtaAnno(2)) / 3, 4) 'valore da inserire in tabelle temporanea
    
            Debug.Print "qta media: " & qtaMedia
    
            WFile "qta media: " & qtaMedia
    
            ' Aggiorna la tabella temporanea con la quantità media
    '
            'If qtaMedia <> 0 Then
               rsTabTmp.AddNew
               rsTabTmp(campoCodiceArticoloMateriali) = codiceArticolo
               rsTabTmp("qtamed") = qtaMedia
               rsTabTmp("utente") = atCNames(1)
               rsTabTmp("PC") = atCNames(2)
               rsTabTmp("datareg") = Now
               rsTabTmp.Update
            'End If
    
            rsMaterials.MoveNext
        Loop
        endTime = Timer
        WFile "Inizio: " & stTime & " Fine: " & endTime & " delta: " & Int((endTime - stTime) / 60) & ":" & (endTime - stTime) Mod 60    
        rsBEM.Close
        rsMaterials.Close
    
        Set conn = Nothing
    
      MsgBox "Elaborazione terminata!", vbInformation, "Avviso"
    ExitSub:
        Exit Sub
    
    ErrorHandler:
        isError = True
        MsgBox "Errore durante l'esecuzione della query: " & Err.Description & " " & Err.Number, vbExclamation, "Errore di esecuzione"
        Resume ExitSub
    End Sub    
    ' Procedure per scrivere un messaggio di debug su un file di testo
    Private Sub WFile(ByVal message As String, Optional ByVal filePath As String = "c:\tmp\debugoutput.txt")
        Dim isFOpen As Boolean
        
        'filePath = "c:\tmp\debugoutput.txt"
        If isFOpen Then
           Print #1, message
           Close #1
        Else
           Open filePath For Append As #1
           Print #1, message
           Close #1
        End If
    End Sub
    
    Sub CloseDebugFile()
        ' Controllo per verificare se il file è aperto prima di tentare di chiuderlo
        If isDebugFileOpen = True Then
            Close #debugFileNumber
            isDebugFileOpen = False
        End If
    End Sub
    
    Public Sub SvuotaFileDiTesto(ByVal filePath As String)
        On Error Resume Next
        Open filePath For Output As #1
        Close #1
        On Error GoTo 0
    End Sub
    
    
Devi accedere o registrarti per scrivere nel forum
8 risposte