Rapport/query verschil bereken tussen 2 jaren.

[Nico84]

Ik heb een rapport die gebasseerd is op een kruistabelquery.
Het rapport laat de omzetten per maand en jaar zien.

Het rapport wordt gegroepeerd op DebiteurID
In de groep debiteurID heb ik:
Als rijkop de jaren en een totaal en als kolomkoppen de maanden.

Is het mogelijk om in de query het verschil te bereken tussen de totalen van twee jaren.
Dus als ik als rijkoppen heb
2008 Dit is het eerste jaar van de debiteur, dus geen verschil berekenen
2009 Verschil bereken tussen totaal van 2009 minus totaal 2008
2010 Verschil bereken tussen totaal 2010 minus totaal 2009
2011 Verschil bereken tussen totaal 2011 minus totaal 2010

 

[OctaFish]

Je kan het met een Lopend Totaal formule proberen, al weet ik niet of die werkt in een kruistabel.

 

[Nico84]

Heb het een en ander geprobeerd, maar krijg het nog niet helemaal voor elkaar.

Heb het volgende nu staan in me query:

Verschil: Som([BedragExcl])-DSum("[BedragExcl]";"tbl_Omzet";"Year([Factuurdatum])=" & [Jaar]-1 & " And DebiteurID = " & [tbl_Omzet].[DebiteurID])]

In rij veld staat de bovenstaande formule
In rij Totaal staat Expressie
in rij Kruistabel staat Rijkop

Deze berekent het totaal bedrag van de rij minus de TOTALE Omzet van het vorige jaar.
Als ik nu de maand(kolomkop) toevoeg aan DSUM krijg de foutmelding:
De SELECT-instructie bevat een ongeldige verwijzing.

Is er een mogelijkheid om de maand toch toe te voegen aan DSUM?

 

[OctaFish]

Hoe zou de toevoeging van de maand er dan uit moeten zien?

 

[Nico84]

Dit is hem met toevoeging van de maand.

Verschil som([BedragExcl])-DSum("[BedragExcl]";"tbl_Omzet";"Year([Factuurdatum])=" & [Jaar] & " And Month([Factuurdatum])<=" & [Maand] & " And DebiteurID = " & [tbl_Omzet].[DebiteurID])

 

[OctaFish]

Ik weet niet of het mogelijk is in de kruistabel zelf; ik zou een nieuwe query maken op basis van de kruistabel, en daar het lopend totaal in maken.

 

[Nico84]

OctaFish jouw tip heb me aan het denken gezet en heb het op de volgende manier opgelost:

Vanuit mijn filter formulier wordt mijn query ingevuld door deze code. Hierop is ook mijn dynamisch kruistabel rapport op gebasseerd.

Private Sub Rapportopenen()
Dim qDF As QueryDef
Dim strSQLfilter As String
Dim SQLquery As String

Call Filteren
    
        SQLquery = "qry_Leeg1"
        strSQLfilter = "TRANSFORM Sum(tbl_Omzet.BedragExcl) AS SomVanBedragExcl " _
                        & "SELECT tbl_Omzet.DebiteurID, tbl_Debiteuren.Debiteurnaam, Year([Factuurdatum]) AS Jaar, Sum(tbl_Omzet.BedragExcl) AS Totaal " _
                        & "FROM tbl_Debiteuren INNER JOIN tbl_Omzet ON tbl_Debiteuren.DebiteurID = tbl_Omzet.DebiteurID " _
                        & "Where" & sFilter _
                        & "GROUP BY tbl_Omzet.DebiteurID, tbl_Debiteuren.Debiteurnaam, Year([Factuurdatum]) " _
                        & "PIVOT Month([Factuurdatum])"

                        
        Set qDF = CurrentDb.QueryDefs(SQLquery)
        qDF.SQL = strSQLfilter

Heb vervolgens onderstaande code geplaatst onder de bovenstaande code.
Deze vult een nieuwe gefilterde query.

        SQLquery = "qry_Leeg2"
        strSQLfilter = "SELECT tbl_Omzet.DebiteurID, tbl_Debiteuren.Debiteurnaam, Year([Factuurdatum]) AS Jaar, Month([Factuurdatum]) AS Maand, tbl_Omzet.BedragExcl " _
                        & "FROM tbl_Debiteuren INNER JOIN tbl_Omzet ON tbl_Debiteuren.DebiteurID = tbl_Omzet.DebiteurID " _
                        & "Where" & sFilter
                        
        Set qDF = CurrentDb.QueryDefs(SQLquery)
        qDF.SQL = strSQLfilter

Op het rapport heb ik een tekstvak Verschil toegevoegd in de detail sectie.
Met als besturingselementbron:

=[txtTotaal]-DSum("[BedragExcl]";"[qry_Leeg2]";"Jaar=" & [txtJaar]-1 & " And DebiteurID = " & [txtDebiteurID] & " ")

En het werkt ook nog!
Alleen het rapport wordt wel iets trager geladen.