Grafiek in access 2010 met VBA

[Frits1969]

Hallo allemaal,

Ik heb een database tabel met: Datum, BedragEx, BTW, Inkomsten
BTW is een getal met een percentage.
Inkomsten is een waar/onwaar veld.

Wie kan mij vertellen hoe ik dit om kan zetten naar 2 grafieken in 1.
Ik wil een staafgrafiek per dag. (later misschien ook per week, maand, kwartaal en jaar)

Ik heb in 2 variabele een datum om daarmee aan te geven wat de periode is.

Ik wil dan de x-as verdeeld in dagen. In de database heb ik niet iedere dag zitten, dus dan moet er als bedrag 0 uit komen.
Op de y-as staan de bedragen in euro's.
Ik wil per dag een staaf voor de inkomsten en een staaf voor de uitgave naast elkaar.

Wie kan mij helpen om dit te bouwen in Access 2010

Vriendelijk bedankt alvast,
Frits van Leeuwen

 

[OctaFish]

Probeer eerst eens gewoon een grafiek te maken, lijkt mij op basis van je gegevens niet zo moeilijk. Om dat in VBA te gaan doen, geeft aan dat je vermoedelijk over zeeën van tijd beschikt

 

[Frits1969]

OctaFish, bedankt voor je reactie.
Grafieken in Access vind ik lastig. En natuurlijk moet ik dit stap voor stap opbouwen.
Maar uiteindelijk moet vanuit VBA aangeven aan welke voorwaarden de grafiek moet voldoen.

De moeilijkheid die ik nu tegen kom, is dat ik op de x-as ALLE dagen wil zien. En niet alleen de dagen die in de database zijn ingegeven.

 

[OctaFish]

Een grafiek is gebaseerd op een gegevensbron. Een selectie daarvan ook. Dat heeft allemaal weinig met VBA te maken. Idem dito voor je grafiek waarin je alle dagen wilt zien. Dat probleem heb ik een aantal maanden geleden al behandeld in de Access cursus in de Nieuwsbrief, met 3 verschillende oplossingen, oplopend in moeilijkheidsgraad. Lees eens na, zou ik zeggen!

 

[Frits1969]

Heb je die nieuwsbrief nog? En mag ik die dan van je lezen?

 

[OctaFish]

Alle oude hoofdstukken vind je in de Handleidingen sectie. Je kunt eens beginnen met Hoofdstuk 13 die deze materie behandelt, inclusief een formulier om een datumselectie te maken.

 

[Frits1969]

Als ik het goed begrijp, is er een hulp tabel nodig. Ik had gehoopt dat die niet nodig was. Het is dus niet in een qwery te plaatsen.

 

[OctaFish]

Het voorbeeld is één situatie, die voor jou wellicht niet nodig is. Een query of een tabel maakt als invoer voor een grafiek ook helemaal niet uit. Als je gegevensbron klopt, dan klopt je grafiek ook. Maar de beste manier voor ons om te helpen is natuurlijk als je een voorbeeldje post met bruikbare data.

 

[Frits1969]

Dat snap ik, maar ik heb niets. Dus wat ik gedaan heb omschrijf ik.
Maar met een hulptabel kan ik ook wel werken.

 

[Harry46]

Ik wil dan de x-as verdeeld in dagen. In de database heb ik niet iedere dag zitten, dus dan moet er als bedrag 0 uit komen.
Op de y-as staan de bedragen in euro's.
Ik wil per dag een staaf voor de inkomsten en een staaf voor de uitgave naast elkaar.

Krijg sterk de indruk dat dus niemand in dit draadje al eens (datum)grafiek gemaakt heeft in Access.
Het is toch tamelijk simpel. Een grafiek object plaatsen op een formulier en bij de 'row source' van de grafiek een kruistabelquery maken van de gegevens.
Die kan er dan b.v. zo uitzien:
TRANSFORM Sum(tblFin.BedragEx) AS TotaalBedrag SELECT tblFin.Inkomsten FROM tblFin GROUP BY tblFin.Inkomsten PIVOT tblFin.Datum;

Tja en die tussenliggende datums waar voor er geen data is... Nou ja, dat zie je vanzelf

 

[Frits1969]

Harry46, hartelijk dank voor je reactie. Ik heb inmiddels een hulptabel aangemaakt en daar stuur ik dan de benodigde data naar toe en voeg daar via een for-next lus voor alle benodigde dagen een record met alleen de datum aan toe. Als ik daar vevolgens een qwery over heen gooi, waarbij ik de dagen samen voeg en sommeer, dan moet het lukken denk ik.

 

[Harry46]

Frits,
Het aardige in een grafiek is nu net dat je geen hulptabel nodig hebt, Access vult de ontbrekende dagen zelf aan.
Hier een voorbeeldje

De rowsource van de grafiek ziet er zo uit:

 

[Frits1969]

Dat heb ik niet. Ik zie enkel de datums waarop ik een transactie had. Vandaar dat ik een hulptabel nodig heb.

 

[OctaFish]

Zonder voorbeeldje kunnen wij niet zien hoe jij je grafiek hebt ingesteld. Dus als je snel(ler) iets zinnigs wilt horen, is een voorbeeldje een prachtig idee. Kun je altijd op een fileshare zetten als je hem niet gecomprimeerd en gezipt krijgt.

 

[Frits1969]

Ik ben inmiddels een stap verder. Ik heb de hulptabel die ik leeg en weer vol met behulp van de volgende regels.

TmpBedrijfsnummer = Forms!Menu!Keuzelijst3.Value
TmpDatum = Format(Me.Tekst17.Value, "d mmmm yyyy") 'vroegste datum
TmpDatum2 = Format(Me.Tekst55.Value, "d mmmm yyyy") 'nieuwste datum
'bereken de periode naar dagen/maanden/jaren
aantaldagen = Me.Tekst55.Value - Me.Tekst17.Value + 1

'leeg de tabel TMPDagen
DoCmd.SetWarnings False
DoCmd.RunSQL ("Delete * from TMPDagboek")

'vul TMPDagen met alle dagen tussen Tekst55 en tekst17
For iDD = Me.Tekst17.Value To Me.Tekst55.Value
    DoCmd.RunSQL ("INSERT INTO TMPDagboek (Datum, Debet_Bedrag, inkomsten, BTW) VALUES( #" & Format(iDD, "m-d-yyyy") & "#,0,null,0);")
    DoCmd.RunSQL ("INSERT INTO TMPDagboek (Datum, Debet_Bedrag, inkomsten, BTW) SELECT Datum, Debet_Bedrag, inkomsten, BTW FROM Dagboek WHERE Dagboek.Bedrijfsnummer = " & TmpBedrijfsnummer & " AND Dagboek.datum = #" & Format(iDD, "m-d-yyyy") & "#;")
Next iDD
DoCmd.SetWarnings True

Nu ben ik nog aan het kijken naar de qwery die ik nodig heb om de juiste grafiek er uit te krijgen. Want de grafiek zelf lukt inmiddels ook wel. OctaFish, tussen de regels door geef je voldoende aanwijzingen om weer een stap verder te komen. Ik weet dan natuurlijk niet of dat het misschien simpeler kan, maar het gaat mij er om dat het werkt.
Harry46 geeft aan dat access2010 zelf de dagen aanvult. Maar dat lukt me helaas niet. Maar met de code hier boven lukte het mij wel om de ontbrekende datums in de hulp tabel te krijgen.
De hulptabel gebruik ik dan om de grafiek te maken.

 

[OctaFish]

Harry46 geeft aan dat access2010 zelf de dagen aanvult.

Moet je de assen wel goed instellen (als datum namelijk, niet als waarde).

 

[Frits1969]

Hoe doe ik dat dan?
Als ik dat weet, dan zal mijn grafiek een stuk sneller in beeld staan.
Ik heb nu in VBA ook de volgende code opgenomen. Daarmee lukt het mij om de grafiek te tonen op de praats waar ik hem hebben wil.

'toon de grafiek op dagen
Me.Parent!BoekingenLijst.Visible = False
Me.Parent!Grafiek.Visible = True
'qwery voor grafiek
Me.Parent!Grafiek.Form!Grafiek34.RowSource = "SELECT TMPDagboek.datum, SUM(TMPDagboek.Debet) AS Inkomsten, SUM(TMPDagboek.Credit) AS Uitgaven FROM TMPDagboek GROUP BY TMPDagboek.datum;"
Me.Parent!Grafiek.SetFocus

 

[OctaFish]

Eigenschappen van de as instellen.

 

[Frits1969]

Zo simpel? Morgen avond heb ik tijd om er verder naar te kijken, dan laat ik het resultaat weten.
Ik zal zeer waarschijnlijk dan in der daad geen hulp tabel nodig hebben. Daardoor wordt het vrijwel zeker vele malen sneller. Nu moet ik lang wachten. Dat is dan afhankelijk van de periode die ik kies, en de grote van de tabel. Hoe groter de tabel hoe trager het resultaat verschijnt, maar ook hoe groter ik de periode maak, hoe langer het duurt om de hulp tabel te vullen.
Ik laat wel weten wat ik met de VBA code heb gedaan.

 

[Frits1969]

Ik heb het toch 's avonds al direct gedaan.
De hele For ... Next lus heb ik verwijderd.

De volgende regel heb ik vervangen

'qwery voor grafiek
Me.Parent!Grafiek.Form!Grafiek34.RowSource = "SELECT TMPDagboek.datum, SUM(TMPDagboek.Debet) AS Inkomsten, SUM(TMPDagboek.Credit) AS Uitgaven FROM TMPDagboek GROUP BY TMPDagboek.datum;"

door:

Me.Parent!Grafiek.Form!Grafiek34.RowSource = "SELECT Datum, IIf(Dagboek.inkomsten=True,Sum(Dagboek.debet_bedrag*((Dagboek.BTW/100)+1),0)) AS Debet, IIf(Dagboek.inkomsten = True, 0, Sum(Dagboek.debet_bedrag * ((Dagboek.BTW / 100) + 1)) As Credit FROM Dagboek WHERE Dagboek.Bedrijfsnummer = " & TmpBedrijfsnummer & " AND Dagboek.datum >= #" & Format(Me.Tekst17.Value, "m-d-yyyy") & "# AND Dagboek.datum <= #" & Format(Me.Tekst55.Value, "m-d-yyyy") & "# GROUP BY Format(Dagboek.datum,'m-d-yyyy'), Dagboek.inkomsten;"

En dat met de aanwijzing hoe ik de datum tussen de verschillende datums kan doorlopen, zou dit moeten werken. aar nu maak ik ergens een SQL fout. (Maar waar?)