Access VBA code calculaties met tijd en datum probleem

[Powerblade]

Beste mensen,

ik zit al een aantal weken met een probleem. Hier volgt een kort gegeven:

Ik ben een factuurprogrammatje aan het maken, wat op zich heel simpel is. Maar nou zit er een MAAR achter, nm. automatische uur calculatie.
Men heeft voor aantal gewerkte uren iedere keer een andere uurloon vb:
maandag gewerkt tussen 06:00 - 07:00 = 30 euro / uur
maandag gewerkt tussen 07:00 - 18:00 = 32 euro / uur
maandag gewerkt tussen 18:00 - 0:00 = 34 euro /uur
maandag gewerkt tussen 0:00 - 06:00 = 40 euro / uur
en ga zomaar door....

Nou ziet mijn factuurdetail er zo uit:

[datum] [dag(vb maandag)][tijdstart][tijdeind] [totaal (in decimalen)] [30(euro)] [32] [34] [40]
15-9-2014 maandag 06:00 23:00 17,00 1uur 11uur 5uur 0uur


Zoals bovenstaand te zien is wil ik:

Datum invoeren en dag word automatisch ingevuld => dit lukt bij gebeurtenis na bewerken van datum:

Me![dag] = Format([datum], "dddd")

daarna voeren we tijdstart en tijd eind en daarna word totaal in decimalen automatisch ingevuld=> ook dit is geen probleem: als expressie bij totaal:

=(DateDiff("n";[tijdstart];[tijdeind])/60) & " uur"

Nou komt het:

Dus het systeem moet zelf bepalen hoeveel uur in die gewerkte 17 uur, voor 30 euro, hoeveel uur voor 32, hoeveel uur voor 34 en 40 is gewerkt.
In onze voorbeeld hebben we in dit geval :
1 uur gewerkt voor 30 euro per uur
11uur gewerkt voor 32 euro per uur
5 uur gewerkt voor 34 euro per uur
0 uur gewerkt voor 40 euro per uur.

dus
1x 30 = 30
11x32 = 352
5x34 = 170

totaal = 552 euro....

Dus ik zoek een manier een code die voor mij moet uitdokteren na invoer begin en eindwerktijd voor welke uurdelen welke bedragen gehanteerd gaan worden.

Uitleg is misschien een beetje puzzelwerk, maar ik denk dat de oplossing eigenlijk mee zal vallen, maar van alles geprobeerd en kom er niet uit. Ik zou weer fijn kunnen slapen als dit probleem opgelost zou zijn in iedere geval een richting zou al heel wat zijn....

In iedere geval mijn dank aan alle mensen die het hele verhaal hebben gelezen en hun bijdragen willen leveren aan dit probleem.

gr

Dekker

 

[OctaFish]

Een paar zaken vallen sowieso op: waarom een veld [Dag]? Je hebt al een veld [Datum], dus er is geen enkele reden te bedenken waarom je ook nog eens de naam van de dag opslaat. Dataredundantie heet: dat: nodeloos dubbele gegevens opslaan. In je subformulier heb je al genoeg aan een niet-gebonden tekstvak met de formule =Format([datum];"dddd").
Tweede ding: de berekening die je gebruikt voor je uren is ook nodeloos ingewikkeld. Tijden zijn getallen, en die kun je van elkaar aftrekken. Wél moet je de uitkomst met 24 vermenigvuldigen omdat de uureenheid nu eenmaal niet decimaal is. Dus je formule in het veld [Totaal] is dan: =([tijdeind]-[tijdstart])*24. En het woord uur? zet je in de notatie: 0,0" uur".
Verder zie ik een aantal rare velden in de tabel ([100], [122] etc) waarvan ik niet weet of je die wel nodig hebt. Maar je vraag zelf is zoals je al vermoedde simpel op te lossen met een Cartesisch product.

 

[Powerblade]

Beste Octafish,

Enkele opmerkingen kloppen, echter de opdrachtgever heeft specifiek om die velden gevraagd. Je advies omtrent urenberekening klopt nu je het opschrijft, soms maakt men moeilijker dan dat het is, in dit geval mijn dank .
velden 100, 122 etc zijn ook nodig en is een must vanuit opdrachtgever, namelijk, deze velden moeten uiteindelijk laten zien, als men vb tussen 6:00 en 18:00 heeft gewerkt, 100=2 uur( van 6 tot 8 ), 122=10uur ( 8 - 18:00 ) enz...

Je adviezen heb ik begrepen, maar eigenlijk misschien een omweg met mijn manier, maar die velden werkten al, mijn eigenlijke vraag is nog steeds niet beantwoord... En het is simpel op te lossen door een Cartesisch product????? Wat bedoel je daarmee?
Of zou je een voorbeeld code kunnen schrijven?

alvast bedankt.

 

[OctaFish]

... echter de opdrachtgever heeft specifiek om die velden gevraagd.

Opdrachtgevers, het zal je niet onbekend zijn, weten doorgaans weinig van database normalisatie. Je mag al blij zijn als ze weten wat een database is . Het is aan de ontwerper om de klant duidelijk te maken dat hij onzinnige wensen heeft. Zo is alle informatie die je in de tabel opslaat simpel te berekenen. En wat zegt de eerste normalisatie regel? Gegevens die zijn af te leiden uit andere gegevens sla je niet op in de tabel! Simpel voorbeeldje: als je in één record de waarde in veld 122 verandert, wat databasetechnisch natuurlijk geen enkel probleem heeft, verandert niet automatisch de begin- of eindtijd.

Sowieso geldt op een normale weekdag, als ik het goed interpreteer, het 122 tarief alleen tussen 6:00-7:00 uur en 22:00-23:00. De logica gebiedt dan dat je nooit meer dan 3 uur op een werkdag mag invoeren in dat veld. Maar je kunt rustig daar het getal 6 intypen. Zou absoluut niet mogen! En kán ook helemaal niet als je de db genormaliseerd hebt, want dan wordt het aantal uren in die tariefgroep berekend op basis van begin- en eindtijd. Zoals het hoort. Kortom: stuur die opdrachtgever maar eens langs .

Een Cartesisch product is de meest simpele query die je kan bedenken: pak 2 tabellen, sleep ze naar het query venster en je hebt een Cartesisch product . D.w.z.: als de 2 tabellen niet gekoppeld zijn in het Relaties venster. Maar dan kun je er alsnog een Cartesisch product van maken door de verbindingslijn weg te halen. Nou is dat doorgaans net níet de bedoeling, want wat is het resultaat? Als je de query uitvoert zonder gekoppelde tabellen, dan weet Access niet welke gegevens aan elkaar gelinkt zijn. Je krijgt dan van elk record uit Tabel1 alle combinaties te zien die met Tabel2 te maken zijn. Dus als je het eerste record uit Tabel1 pakt en er zijn in Tabel2 36 records, dan zie je in de query voor dat eerste records uit Tabel1 al 36 records. Voor het 2e record uit Tabel1 komen daar ook 36 records uit Tabel2 bij, en zo verder. Als Tabel1 49 records bevat, dan zie je dus in totaal 49 * 36 = 1764 records. Nogmaals: meestal niet zo nuttig, want je wilt doorgaans de 36 records uit Tabel1 zien en de gekoppelde records uit Tabel2. Maar dit is dus het principe van een Cartesisch product. Onnodig te zeggen dat de records die je zou zien met gekoppelde tabellen uiteraard ook in het resultaat zitten.

In jouw geval heeft een cartesisch product dus wél zin, omdat je een tabel hebt met tarieven, en een tabel met werktijden. Wat je wilt zien is een overzicht waarin voor elk urenbereik een match is met begin- en eindtijd. En dat doe je dus met een criterium toe te voegen. In jouw geval 2, want je wilt ook nog eens op week of weekend filteren.
Kijk maar eens of je er zelf uitkomt, anders maak ik wel een voorbeeldje.
Hier alvast een voorbeeldje van een Cartesisch product dat voor elk werkblok een apart record maakt.

SELECT factuurdetail.datum, IIf(Weekday([Datum],2)<6,"Week",Format([Datum],"dddd")) AS Dag, factuurdetail.tijdstart AS Begintijd, 
factuurdetail.tijdeind AS Eindtijd, uurloon.tijdstart, uurloon.tijdeind, uurloon.procent, uurloon.prijs
FROM factuurdetail, uurloon
WHERE (((IIf(Weekday([Datum],2)<6,"Week",Format([Datum],"dddd")))=[uurloon].[Dag]) 
AND ((CDbl([factuurdetail].[tijdstart]))<CDbl([uurloon]![tijdeind])) AND ((CDbl([factuurdetail].[tijdeind]))>CDbl([uurloon]![tijdstart])))
ORDER BY factuurdetail.datum, uurloon.tijdstart;

 

[Powerblade]

Bedankt voor je duidelijke gedetailleerde uitleg. Heb in die richting niet zitten kijken ... Werd nogal laat gisteren.. Ik zal kijken straks en geef wel een seintje of het is gelukt of niet natuurlijk

Thanks!

 

[Powerblade]

Ik heb even gekeken en ik snap wat je bedoeld klopt. Ik ben niet zo goed met tijdberekeningen, misschien dat ik iets niet goed zie in die query.. Heb even gekeken... tussen 6:00 - 23:00 heb je 3 records volgens SQL. 6-7/7-20/20-22...
echter heb je nog een blok van 22-00 met 144 percentage, deze zie ik niet terug. dat is 1.

2) ik zie nog steeds geen lichtje branden hoe ik in godsnaam het aantal uren kan laten berekenen met de bijbehorende percentages of uurtarief.. per blok. dus :

datum Dag Begintijd Eindtijd tijdstart tijdeind procent prijs
15-9-2014 Week 6:15:00 23:00:00 6:00:00 7:00:00 122 € 34,29

vb: 6:00 - 7:00 = totaal 0.75 uur oww wacht even:
zou het verstandig zijn als ik nog een veldje ( gewerkte uur ) toevoeg en :

gewerkte uur: factuurdetail.begintijd - uurloon.tijdeind *24 ???

Zoals bovenvermeld dan mis ik nog steeds 1 uur werktijd : van 22:00 - 23:00...

Ik voel al wat opheldering ik hoop wel in de goede richting...;P

 

[Powerblade]

ik zie al, ik moet met if gaan werken denk ik, zou je in tussen een voorbeeldje kunt geven....

 

[OctaFish]

Ik heb vanmiddag verder weinig tijd, maar vanavond kan ik er wel weer naar kijken. De reden dat je wellicht niet alles ziet is omdat je eindtijd identiek is aan je begintijd van het volgende record. En dat mag niet. dus 7:00:00-20:00:00 en 20:00:00-22:00:00 is fout, dat zou moeten zijn: 7:00:00-19:59:59 en 20:00:00-22:00:00 of: 7:00:00-20:00:00 en 20:01:00-22:00:00. Of zoiets. Een tijd moet namelijk tussen 2 waarden kunnen vallen en niet in 2 tegelijkertijd. Ik heb zelf wat data in je voorbeeldje gerost, en daar werkt het prima. Als je dus de tijden in orde hebt.

 

[Powerblade]

Dat dacht ik al... ok geen nood. Als je even wil kijken heel graag. ik heb over na zitten denken, want stel iemand werkt maar van precies 06:00:00 - 7:00:00 en ik zou als eindtijd 6:59:59 nemen, krijg ik dan geen decimalen voor die ene honderdste seconden? de tijden worden afgerond naar 15 minuten, dus 1 minuut of minder is geen probleem. Dus men schrijft alle gewerkte uren naar 15 minuten telkens, dus 6:00 - 6:15 - 6:30 enz... Dus als we de uurloon tabel de tijden van
6:00:00 - 6:59:59=122
7:00:00 - 19:59:59=100
20:00:00 - 21:59:59=122 enz doen,
en de persoon zou voor die dag precies van 20:00 - 22:00 gewerkt hebben, hoe ziet de berekening dan eruit? dat ene honderdste seconden...

Ik zie je reactie graag tegemoet, bedankt tot zover...

 

[OctaFish]

Iemand die exact op de seconde van 06:00:00 - 7:00:00 werkt, heeft dus één seconde in het uur van 7-8 gewerkt. Of, zo kan je het ook zien, de laatste seconde van het uur 6-7. Hoe dan ook komt er een uur bij .

 

[Powerblade]

Hi, nog tijd gehad om te kijken? Ben benieuwd of de code waar ik opzoek ben uberhaupt zal werken? ben gisteren weer aan het werk geweest .... maar tevergeefs...dus als je tussendoor tijd hebt, zou ik graag een voorbeeldje kunnen gebruiken..
alvast bedankt.

 

[OctaFish]

Ben benieuwd of de code waar ik opzoek ben uberhaupt zal werken?

Lees ik hier dat je eigenlijk op zoek bent naar niet-werkende code? Want die heb ik ook wel voor je liggen . Ik heb er gisteren toch geen tijd voor gehad, maar omdat het principe niet zo moeilijk is, en ik het zelf ook gebruik (werkend, wees gerust) zal ik er vandaag wel een voorbeeldje van maken.

 

[Powerblade]

Ow nee hoor, ik ben een prof op gebied van niet werkende codes ... Dus die heb ik zat liggen... Graag, ik zie je reactie wel....:thumb: thnx!

 

[Powerblade]

Hi, enige vooruitgang mbt het voorbeeldje? ....

 

[OctaFish]

Dat wel, maar het ligt thuis... Stick vergeten

 

[Powerblade]

geef nie , beter laat dan nooit

 

[Powerblade]

Hi, intussen ben ik bijna waar ik wilde zijn ... Ik heb de laatste versie geupload. Als je even ernaar wilt kijken. Alles werkt naar mijn idee zoals moet. Maar misschien zijn er tig andere manieren om hetzelfde resultaat te behalen. Daarbij waren je query's erg nuttig. Een laatste vraag:
Nu gaat met met
- door de weeks ( maandag tm vrijdag )
- Zaterdag
- Zondag
allemaal goed. Er is nog 1 uurtarief, welke zelfde is als zondag tarief en dat is namelijk feestdagen = 160 %....

Hoe moet dat? ik bedoel, nu wordt vanuit datum teruggeredeneerd naar dagen in de week. Maar hoe kun je een datum veld laten weten dat een maandag bijvoorbeeld feestdag is op dat moment???

Bedankt tot zover!:thumb:

 

[OctaFish]

Ik heb de feestdagen er niet voor niks even uitgelaten . Normaal gesproken zijn de meeste feestdagen wel terug te herleiden tot een datum, maar dat gaat dus niet op voor Pasen en de daaropvolgende dagen. Er zijn wel procedures te vinden die de paasdagen uitrekenen, en de overige dagen zijn, zoals Kerst en Oud- en Nieuw en 5 mei, bekend. Ik zou de feestdagen dus in een matrix inlezen waarbij je de paasdagen simpel (wat heet) kunt berekenen (zie voorbeeldje uit Excel dat je simpel kunt ombouwen). Als je Pasen weet, is de rest ook simpel te herleiden.
Al die datums lees je dus in een matrix in, en vervolgens kijk je bij elke te berekenen datum of het een feestdag is of niet. Je zet dan een Boolean op True bijvoorbeeld. De berekening pakt dan het feestdag tarief als de boolean True is, en anders het ingelezen tijdtarief.