Query in urenregistratie

[FrankinZwolle]

Op mijn werk gebruiken we een geavanceerd order-urenregistratiesysteem. Een maal per week kan ik hieruit mijn geboekte uren met activiteitensoorten exporteren naar Excel.

Ik wil in Access overzichten voor mijzelf kunnen maken op welke wijze ik mijn werkweken doorbreng. Hoeveel uren van activiteit A? Hoeveel vrije dagen heb ik nog? Hoeveel overuren heb ik gemaakt? Allemaal gegevens die het werksysteem niet voor mij kan produceren, zo geavanceerd is het dus ook weer niet. In de bijlage een opzet van mijn Access 2013 database. De Brontabel vul ik door het inlezen van de Excel export. Daarin staan alle benodigde gegevens.

Ik heb al een query voor de activiteiten uren per week en per maand gemaakt. Hiervan kan ik rapporten maken met totaaltellingen. Zo ver (nog) geen probleem. Het probleem komt omdat ik de totalen per week van bepaalde activiteiten wil weten. Hoeveel uur van bijvoorbeeld Feest, hoeveel van Vrij en Vrijv samen, hoeveel van Prj en Uitl samen, hoeveel van alle activiteiten samen en dan ook zichtbaar hoeveel er in de werk gewerkt moet worden. Hiervoor heb ik een aparte tabel weeknummers gemaakt met de hoeveelheid uren per week dat ik voor de werkgever registreer (uren in een week is niet altijd 40 ivm halve weken begin of einde jaar). En dan ook nog een telling van totaal geregistreerd minus de uren uit weeknummertabel, hetgeen de overuren zijn.

Moet ik een extra tabel maken waarin ik de activiteiten in groepen verdeel en daar queries mee maken?

Wie kan mij op weg helpenBekijk bijlage uren1.zip

 

[OctaFish]

Ik heb geen idee wat je wilt zien, vrees ik en je brontabel helpt ook niet echt. Die is nogal voorzien van dubbele velden. Wat heet: sommige velden zitten er wel 3 keer in (waaronder geen enkele in het juiste format) zoals [Datum], [Weeknr] en [Periode]. Allemaal te vangen met één (echt) datumveld. Daarnaast zit er een verkeerde urennotatie in ([Begintijd]=83000 en [[Eindtijd]=170000?) waar je een niet-gerelateerde berekening op maakt die geen berekend veld is.... Ik heb geen idee hoe je dat uit je registratie systeem krijgt aangeleverd, maar dat moet beter kunnen .
Maar waar ik écht op stuk loop is dit:

Het probleem komt omdat ik de totalen per week van bepaalde activiteiten wil weten. Hoeveel uur van bijvoorbeeld Feest, hoeveel van Vrij en Vrijv samen, hoeveel van Prj en Uitl samen, hoeveel van alle activiteiten samen en dan ook zichtbaar hoeveel er in de werk gewerkt moet worden.

Daar zit geen enkele herleidbare logica in, en in een database kun je alleen gegevens met elkaar vergelijken waar een zekere consistentie in zit. Willekeurig zomaar wat veldjes bij elkaar optellen voor een kruistabel kan namelijk niet. Het enige wat je daar op kunt verzinnen is elke keer een nieuwe kruistabel maken met de juiste velden. Dat kan natuurlijk wel, maar is dus behoorlijk ingewikkeld, want de ene keer wil je groeperen op het veld [Vrij], de volgende keer op [Vrij] en [Vrijv] en de derde keer op [Prj] en [Uitl]. Daar zit voor mijn gevoel geen logica in, en dat houdt dus in dat je heel wat te programmeren hebt om dat voor elkaar te krijgen.
De tabel [Weeknummers] zou ook overbodig zijn trouwens als je gewoon met datumvelden zou werken. Het aantal gewerkte uren is immers gelijk aan de som van het aantal geregistreerde uren per week. Simpel te herleiden dus.

 

[FrankinZwolle]

OctoFish: mogelijk dat mijn vraagstelling je verward heeft dus ik doe een nieuwe poging.

De gegevens in de brontabel zijn zoals ik ze uit het andere systeem kan krijgen. Dat kan niet anders. Dat hier velden dubbel erin zitten is nu eenmaal zo, daar moet ik het mee doen. Deze velden doe ik ook hier in Access verder niets mee, maar voor de export vanuit het andere systeem is het het makkelijks om gewoon alles te doen. Noem het luiheid of gemak, ik noem het tijdsbesparing.

Als ik me dan concentreer op de query Overzicht per week dan heb ik prima in beeld hoeveel uur er per week, per activiteit geboekt is. Dat is dus ook geen probleem.
Wat ik nu wil is dat ik een 'ingedikt' Overzicht per week nodig heb. Daarbij wil ik bepaalde activiteiten (altijd dus niet de ene keer wel en de andere keer niet) bij elkaar optellen.
Kolom [Vrij] +[Vrijv], kolom [Feest] apart, kolom [Prj]+[Uitl], kolom [ext]+[swt] en tot slot alle overige kolommen bij elkaar opgeteld: [hand]+[opl]+[sd]+[sdf]+[test]+[verg]+[verkw]

Resultaat zou dus een weeknummer zijn met 5 kolommen. Aan deze kolommen een totaal telling van al deze kolommen toevoegen (dat is het totaal geboekt aantal uren van de week). Als zevende kolom het aantal te werken uren van de week. Meestal is dit dus 40 maar voor week 1 uit de voorbeeld db is dit maar 16 uur. Als laatste 8ste kolom kolom 6 (totaal geboekte uren in de week) - kolom 7 = overuren.

Ik heb in de excelbijlage met knippen en plakken een voorbeeld gemaakt wat ik bedoel en wat ik graag in in Access zou willen hebben. Als het kan tenminste.Bekijk bijlage ingedikt_uren.xlsx

 

[OctaFish]

Je zult eerst een nieuwe query moeten maken op basis van je kruistabellen. Dat kan makkelijk natuurlijk, want je kunt een kruistabel als basis nemen voor een nieuwe query. Krijg je iets als:

SELECT [Overzicht uren per week].Weeknummer, Nz([vrij],0)+Nz([vrijv],0) AS [vrij+vrijv], Nz([feest],0)+0 AS fest, Nz([prj],0)+Nz([uitl],0) AS [prj+uitl], 
Nz([swt],0)+Nz([extr],0) AS [ext+swt], Nz([handl],0)+Nz([opl],0)+Nz([sd],0)+Nz([sdf],0)+Nz([test],0)+Nz([verg],0)+Nz([verkw],0) AS rest, 
[vrij+vrijv]+[fest]+[prj+uitl]+[ext+swt]+[rest] AS Totaal, Weeknummers.uren, [totaal]-[uren] AS Overuren
FROM Weeknummers INNER JOIN [Overzicht uren per week] ON Weeknummers.Weeknummers = [Overzicht uren per week].Weeknummer;

 

[FrankinZwolle]

Bedankt Octafish, dit geeft mij weer voldoende handvatten om mee verder te gaan. Ik wist niet hoe ik verschillende kolomwaarden bij elkaar op kon tellen. Met jouw code weet ik dat nu wel. Top!

 

[OctaFish]

Het zal je zijn opgevallen dat ik (bijna) overal Nz heb toegepast. Dat moet bij berekeningen met velden uit een kruistabel, omdat lege velden dan een Null waarde hebben. Gebruik je zo'n veld in een formule, dan is het resultaat ook Null. Daarom moet je die null-velden converteren naar een 0. En dat doe je met Nz.