Categorie archief: Access

Steekproeven

14 november 2014Access, ExcelAbs, Aselect, Random, RND, Steekproef, willekeurigGijs Verbruggen

Steekproeven worden bij veel werkzaamheden gebruikt; of het nu gaat om kwaliteitsonderzoek, control, accountancy: regelmatig is er behoefte om uit een voorraad gegevens een willekeurige steekproef te kunnen trekken.

Heb je een overzicht in Excel, dan is dat snel geregeld; voor een tabel in een Access-database is het wat ingewikkelder.

Excel

Als voorbeeld nemen we een ‘SAP-bestand’: 2000 regels met boekingen in 2014 op vijf verschillende rekeningen.
De accountant komt langs en wil een willekeurige steekproef van 10 regels hebben om te kunnen controleren.

Met de volgende stappen is dit zo geregeld:

kopieer het SAP-werkblad (de tab onderaan ‘vastpakken’ met de muis en, terwijl je de Ctrl-toets vasthoudt, het tabblad verslepen)
voeg in de kopie achter Bedrag een nieuw kopje toe: Aselect
tik in de eerste cel daaronder de formule: =ASELECT()
(denk aan de 2 haakjes op het einde; iedere functie in Excel dient vergezeld te gaan van een open- en sluit-haak).
Deze functie genereert een willekeurig getal tussen 0 en 1.
kopieer deze formule naar beneden, zodat alle regels een willekeurig getal krijgen toegewezen
plaats de cursor ergens in de Aselect-kolom en sorteer: kies in de menubalk Gegevens en in het blok Sorteren en filteren:
de steekproef staat klaar: de eerste 10 regels moeten worden gecontroleerd.

LET OP: nadat de sortering is uitgevoerd zal Excel alle cellen met de formule Aselect() opnieuw berekenen! Opnieuw sorteren levert dan weer een andere steekproef op.
Wil je dit voorkomen, dan moeten de willekeurige waarden ‘vast’ gezet worden vóór stap 5: selecteer alle cellen in kolom E, klik rechts, kies kopiëren en dan direct (dus op dezelfde plaats) weer rechts klikken en dan de 2e plakoptie kiezen (Waarden plakken).

LET OP2: er is een HEEEEL kleine kans, dat Aselect in deze serie één of meerdere keren precies hetzelfde getal genereert. Als dat ook nog net bij de 10e en 11e regel het geval is, klopt bovenstaande niet helemaal. Maar dat vergeten we maar even!

Access

We starten in Access met hetzelfde SAP-bestand (nog wel even ontZIPpen!!).

Een mogelijke oplossing (vergelijkbaar met Excel) is nu om deze tabel (of beter: een kopie daarvan) uit te breiden met een veld Aselect en dit veld voor ieder record te vullen met een willekeurig getal met behulp van een bijwerk-query.

Het kan gelukkig korter en zonder iets aan te passen in het origineel:

kies Maken/Queryontwerp
voeg de tabel SAP aan het ontwerp toe
sleep de drie velden naar het ontwerp:
voeg een nieuw veld toe achter Bedrag:
Aselect: Rnd()
LET OP: in Access worden Engelstalige functies gebruikt
kies linksboven de Gegevensbladweergave:
waarschijnlijk is de vierde kolom niet leesbaar: maak die breder.
Schrik: alle records hebben dezelfde willekeurige waarde!!!!

Waar Excel de functie Aselect() voor alle regels iedere keer opnieuw berekent, blijkt Access de Rnd()-functie één keer uit te rekenen en het resultaat in ieder record te gebruiken.

Dit is op te lossen door aan de functie Rnd() voor ieder record een (andere) parameter mee te geven. De meest eenvoudige oplossing is om tussen haakjes een NUMERIEK veld uit de betreffende tabel op te nemen.
Punt 4 wordt dan: Aselect: Rnd([Bedrag])

Nu krijgt ieder record een ander willekeurig getal mee en kunnen we, na sorteren op het veld Aselect, weer de eerste 10 records selecteren.

LET OP: ook hier zal NA de sortering de functie Rnd() opnieuw worden uitgevoerd en ziet de sortering er ‘vreemd’ uit.
Druk op F5 en Access zal opnieuw de sortering uitvoeren, andere records bovenaan zetten en dan weer alle Rnd()’s opnieuw uitrekenen.

Wanneer we in de query direct de sortering opgeven, dan hoeft dat niet meer handmatig te gebeuren:

Wanneer we, in combinatie met de sortering, ook nog de Top-eigenschap van de query vullen met de waarde 10 zijn we in één keer klaar:

klik rechts in het lege gebied naast de tabel en kies Eigenschappen
tik in het Eigenschappenvenster bij Top-waarden 10 in
kies linksboven de Gegevensbladweergave
het resultaat is, dat je 10 willekeurige records krijgt te zien.
Wil je andere records, druk op F5 en de query wordt vernieuwd.

De Rnd()-functie

De Rnd()-functie in Access (en ook in VBA) is uitgebreider in zijn mogelijkheden dan de tegenhanger, Aselect(), in Excel.
Om echt iedere keer andere, willekeurige getallen te krijgen, moet aan de functie Rnd() een positief getal meegegeven worden.

In het huidige voorbeeld (alle bedragen zijn positief) voldoet de hierboven genoemde functie (Rnd([Bedrag]) prima, maar meestal kun je daar niet vanuit gaan.

Beter is het dan ook om er zeker van te zijn dat er een positief getal als parameter wordt doorgegeven: Rnd(Abs([Bedrag])+1)
Dus:

zorg dat het bedrag groter dan (of gelijk is aan) nul: Abs zorgt er voor, dat een eventueel min-teken verdwijnt
tel er 1 bij op, zodat nul ook niet meer voorkomt
geef dit door aan Rnd en deze zal een nieuw, willekeurig getal opleveren!

LET OP: ook hier bestaat er een kleine kans, dat Rnd() een keer dezelfde getallen oplevert; als dit op de 10e en 11e plaats gebeurt, zal Access 11 records laten zien.

Handige opties in Access

9 oktober 2014AccessHulpmiddelen voor databases, Relaties, Sneltoetsen, TotalenGijs Verbruggen

Hoe vaak ik per dag in Excel de toets-combinatie Alt-= gebruik weet ik niet, maar ik kan hem in ieder geval blindelings vinden.

Wanneer je de cursor onder een serie getallen zet, geeft Excel met behulp van die 2 toetsen (Alt-toets vasthouden en dan het =-teken indrukken) de som van die getallen.
De truc werkt ook als de cursor rechts van een rijtje getallen staat.

In Access was dat tot voor kort niet mogelijk en voerde ik de volgende handelingen uit, als ik een tabel of de resultaten van een query op het scherm had:

Ctrl-a om alle records en kolommen te selecteren OF
met de cursor de gewenste records selecteren OF
met de cursor één of meerdere kolommen selecteren OF
met de cursor een blok gegevens selecteren
Ctrl-c; de selectie kopiëren
naar Excel gaan (eventueel via Alt-Tab als Excel al geactiveerd is)
Ctrl-n; een nieuwe werkmap openen
Ctrl-v; gegevens uit Access plakken
Ctrl- om naar de laatste regel te gaan
dan de cursor direct onder de kolom met de gegevens, waarvan ik een totaal wil weten
Alt-= en eventueel via Ctrl-1 nog even de opmaak regelen

Totalen in Access

Sinds Microsoft versie 2010 van Access heeft uitgebracht, zijn bovenstaande handelingen meestal overbodig.

In dit overzicht heeft Access het aantal omschrijvingen geteld en de som van het Aantal en van de bedragen.

LET OP: bij het bepalen van een aantal wordt een leeg veld niet meegerekend!

Hoe creëren we de totalen:

kies het menu-tabblad Start
zoek daarbinnen het blok Records
klik op Totalen
onderaan het scherm komt een nieuwe regel met vooraan het woord Totaal
onder iedere kolom kun je nu totalen selecteren door middel van het keuze-driehoekje (zie hierboven bij het totaal 6.630,00); de mogelijkheden zijn afhankelijk van het type veld: bij een tekstveld kun je alleen aantallen turven, bij numerieke velden ook de som of gemiddelde etc.

Maak je een selectie dan passen de totalen zich automatisch aan.

LET OP: de totalen vormen geen echte, nieuwe regel onderaan; ze ‘zweven’ min of meer boven de lijst met gegevens. De totalen zijn dan ook altijd zichtbaar!

Object-afhankelijkheden

Tijdens de ontwikkeling van een grote database met tientallen tabellen en misschien wel honderd query’s, kan het nog wel eens gebeuren, dat je het overzicht kwijtraakt.

In het begin heb je tabellen meegenomen, die later toch niet meer gebruikt worden; hoe weet je dat zeker?
Of de structuur van een tabel wordt iets aangepast; voor welke query’s heeft dit misschien consequenties?

Neem de volgende stappen:

selecteer links in het navigatie-venster een tabel of query waar je de afhankelijkheden van wilt weten
kies het menu-tabblad Hulpmiddelen voor databases
zoek daarbinnen het blok Relaties
klik op Object-afhankelijkheden

In dit geval zie je direct dat er 5 query’s gebruik maken van de tabel Product.

Via de 2e keuzebutton kun je ook zien waar deze tabel van afhankelijk is.
Deze optie is vooral van belang bij query´s om snel te achterhalen welke tabellen of andere query´s gebruikt worden.

G-Info en de functie G_Info()

15 september 2014Access, Excel, VBA (Visual Basic for Applications)Datum, eigen functie, Namen, Opmaak, Select, voorwaardelijke opmaakGijs Verbruggen

In mijn vorige artikel heb ik het gehad over de functies CEL() en INFO().
LET OP: in Excel en Access wordt een functie altijd gevolgd door 2 ronde haken. Bij de meeste functies worden tussen die haakjes argumenten meegegeven.

De naam van die tweede functie triggerde me om een eigen functie G-Info() te ontwikkelen. Maar wat zou die functie moeten doen?

Deze week kwam ik op een idee: wij moesten al nadenken wanneer we volgend voorjaar een korte vakantie zouden plannen; ergens met Hemelvaart of Pinksteren. Maar op welke data vallen die volgend jaar?

Ik wist nog wel, dat deze feestdagen afhankelijk zijn van Pasen, maar wanneer is het nu precies eerste Paasdag?

Pasen

Even “googlen” en al snel was ik een stap verder. Wikipedia:

Het concilie van Nicea bepaalde in 325 dat Pasen moet worden gevierd op de zondag na de eerste volle maan in de lente.

We hoeven dus alleen maar te weten wanneer de lente begint, wanneer het dan weer volle maan wordt en wat dan de eerstvolgende zondag is en we zijn klaar!!

Nog maar even verder op Google rondgeneusd en toen bleken er al diverse voorbeelden beschikbaar voor het bepalen van de datum van eerste Paasdag.

Dat is dus minder makkelijk dan ik gedacht had!
Met dank aan de bedenkers van deze routine gaan we dit gebruiken als bouwsteen voor onze nieuwe functie G-Info().

In het Voorbeeldbestand kunt u deze functie aan het werk zien:

wanneer in cel C2 een jaartal wordt ingetikt zal in cel D2 worden weergegeven, wanneer Pasen in dat jaar valt.

In cel D2 staat de formule =BepaalPasen(Jaar). Wanneer deze functie wordt ingetikt, zal in eerste instantie in D2 een getal komen. Door deze cel een datum-opmaak mee te geven zien we, dat Pasen in 2014 op 20 april is geweest. Ik heb de cel een aangepaste opmaak meegegeven: niet alleen de datum komt in cel D2, deze wordt vooraf gegaan door de tekst “Pasen valt op”.

LET OP: het Voorbeeldbestand bevat een viertal zelf-gedefinieerde namen, zodat de gebruikte formules veel beter leesbaar zijn. Cel C2 bijvoorbeeld heeft de naam Jaar gekregen.
Voor uitleg over het definiëren van namen, zie het artikel Namen ipv celverwijzingen.

G_Info()

De functie G_Info() vormt een uitbreiding op de functie BepaalPasen. Als parameter/argument wordt niet allen het jaar meegegeven, maar ook van welke feestdag de datum bepaald moet worden. Als derde parameter dient nog meegegeven te worden of de functie een datum moet retourneren (een getal dus) of een tekst.

Als voorbeeld: in C7 staat de formule =G_Info(Jaar;$B7;C$6).
Deze levert als resultaat de datum voor Pasen (cel B7) in het jaar 2014 en wel als echte datum (niet als tekst; cel C6)). Het resultaat is een getal, die een datumopmaak mee heeft gekregen.

PS1 let op de $-tekens in de formule; deze zijn zodanig gekozen, dat bij het kopiëren van de formule (naar rechts en naar beneden) de juiste verwijzingen blijven bestaan.
PS2 ik had de formule graag G-Info genoemd, maar het -streepje is in een functienaam niet toegestaan.

De formule in cel D7 geeft ook de Paasdatum weer, maar nu is het resultaat een tekst.

Naast de Paasdatum levert G_Info() ook de data voor Pinksteren, Carnaval, Hemelvaart, Kerstmis en Nieuwjaar (die laatste 2 liggen nogal voor de hand!). Zoals we hierna zullen zien, zijn uitbreidingen makkelijk door te voeren. Een verjaardag of huwelijksdag vergeten is er dus niet meer bij!

VBA

Eigen functies (in het Engels: User Definied Function, UDF) worden op dezelfde plaats gedefinieerd als waar macro’s worden geregistreerd: in de VBA-editor. Wanneer u daar nog nooit mee hebt gewerkt, bekijk dan het betreffende artikel.

Een korte uitleg van het programma:

Allereerst definiëren we de naam van de functie: G_Info.
NB zoals al eerder aangegeven is een – (streepje) niet toegestaan.
Daar achter, tussen haakjes, geven we aan welke parameters/argumenten/waarden aan de functie worden meegegeven: het Jaar, waarvoor de feestdagen bepaald moeten worden (moet een Integer=geheel getal zijn), voor welke Feestdag moet er informatie opgeleverd worden (een String=tekst) en een parameter TekstUitvoer (van het type Boolean; kan alleen de waarden WAAR of ONWAAR bevatten).
De groene tekst (achter het ‘-teken) is commentaar en wordt door het programma niet gebruikt.
In het vervolg van het programma willen we tusseninformatie ‘onthouden’ daarom definiëren we via Dim een variabele x.
Het programma moet nu afhankelijk van de inhoud van de parameter Feestdag de datum van Pasen of van Pinksteren of van Carnaval etcetera genereren.
Binnen VBA is daar een handige constructie voor:
Select Case ….
Case Voorwaarde1
Vervolg1
Case Voorwaarde2
Vervolg2
……
End Select
Wanneer in onze functie aan de parameter Feestdag de waarde Pasen wordt meegegeven, moet het programma de Paasdatum gaan bepalen. Afhankelijk van de inhoud van TekstUitvoer (WAAR of ONWAAR) levert het programma een tekst of een datum/getal op. Dit resultaat wordt in de hulpvariabele x bewaard.
De Select-optie wordt afgesloten met een Case Else: als er aan de parameter Feestdag een andere tekst wordt meegegeven dan waar in ons programma rekening mee is gehouden, zal dit programmagedeelte worden uitgevoerd.

Na afsluiting van de Select-constructie (dus na End Select) moeten we nog zorgen, dat de de functie in Excel het resultaat van het programma weergeeft. Aangezien we het resultaat in de variabele x hebben vastgelegd sluiten we de functie af met G_Info=x.

Het mag nu niet moeilijk meer zijn om de functie uit te breiden met voor u belangrijke feestdagen als verjaardag, huwelijksdag enzovoorts.

PS de twee in het Voorbeeldbestand gebruikte functies (BepaalPasen en G_Info) zijn zonder aanpassingen ook bruikbaar in Access. Kopieer de functies naar de VBA-omgeving (Module) van Access en u kunt ze direct in een query inzetten.

Kalender

In het voorbeeld is ook een jaaroverzicht opgenomen, waarin direct de diverse feestdagen worden geaccentueerd:
In dag 1 van januari staat de formule =Datum(Jaar;Maand;Dag) en deze formule is naar rechts en naar beneden gekopieerd. Deze Excel-functie genereert afhankelijk van het jaar, maand- en dagnummer de betreffende datum (in Excel-formaat, dus een getal; zie ook het betreffende artikel over Datums).
Jaar is de naam van cel C2, Maand de naam van het bereik met de cijfers 1 t/m 12 voor de maandomschrijvingen en Dag is het bereik met de cijfers 1 t/m 31.
Alle betreffende cellen hebben bij cel-eigenschappen het datumformaat “ddd” meegekregen: dus alleen de weekdag wordt afgekort getoond.

De blanco cellen op het einde van diverse maanden, de kleuren voor de weekenden en de kleuren voor de feestdagen worden m.b.v. Voorwaardelijke opmaak geregeld (via de menu-optie Start in het blok Stijlen).

Klembord

18 mei 2014Access, ExcelKlembord, Kopiëren, PlakkenGijs Verbruggen

Een van de meest gebruikte onderdelen van je computer-besturingssysteem is het Klembord; alleen hebben we het meestal niet in de gaten!
Iedere keer als we Kopiëren kiezen (of Ctrl-c natuurlijk) dan wordt de selectie op het systeem-klembord geplaatst.
Frustrerend genoeg is er maar ruimte voor 1 kopieeractie en misschien nog ergerlijker: het gekopieerde verdwijnt ook nog eens, als je voor het ‘plakken’ eerst bijvoorbeeld nog even gauw een tabblad, een rij of kolom toevoegt.
Maar gelukkig is daar iets aan te doen!

Het Office-klembord activeren

Wanneer je het Office-klembord activeert, dan krijg je ineens ruimte voor 24 kopieeracties. Wanneer deze allemaal gebruikt zijn, valt de oudste weg, maar dat brengt in de dagelijkse praktijk normaal gesproken weinig problemen met zich mee.
Waar in Office 2003 het Klembord geactiveerd kon worden door 2x achter elkaar op Ctrl-c te drukken, moeten we in de nieuwe versies het Klembord (eenmalig) activeren, voordat we een kopieeractie uitvoeren, om van de uitgebreide mogelijkheden gebruik te maken (gelukkig kun je nog van alles instellen, zodat het Klembord volgende keer direct opent; zie hieronder bij Opties).
Zolang we het Klembord niet sluiten, zal dit actief blijven.

Daar gaan we dan:

start een Office-programma (het Klembord werkt in alle Office-programma’s hetzelfde)
klik zo nodig op het tabblad Start
ga naar Klembord en klik op het kleine pijltje rechtsonder
daar is het Office-klembord!

Wanneer je nu iets kopieert, dan zul je zien dat dit op het Klembord komt. Wil je deze kopie ergens gebruiken: verplaats de cursor en klik in het Klembord op de betreffende tekst of plaatje.

Let op: er is verschil tussen het systeem- en het Office-Klembord.
Waar het systeem-klembord alleen de laatste kopieer- of plakactie vasthoudt kun je bij het Office-klembord 24 acties bewaren.
Wanneer je iets kopieert (of dat nu via een menu-opdracht, via ‘rechts-klikken’ of via Ctrl-c is), dan wordt deze actie op allebei de Klemborden bewaard.
Plak je iets via een menu-opdracht, via ‘rechts-klikken’of via Ctrl-v, dan komt de informatie van het systeem-klembord (dus altijd de laatst uitgevoerde actie)!
Plakken vanuit het Office-klembord kan alleen met behulp van de muis.

Even ‘spelen’ met wat voorbeeldjes en je zult versteld staan van de mogelijkheden:

heb je tijdens je werk regelmatig een groot woord, een lange zin, een pad naar een bepaald bestand nodig: één keer kopiëren naar het Klembord en de hele dag heb je er plezier van!
soms wil je in Excel diverse cellen kopiëren, maar helaas sluiten ze niet precies op elkaar aan; er staan bijvoorbeeld kolommen tussen, die je niet mee wilt nemen.
Dat kan natuurlijk opgelost worden door Ctrl ingedrukt te houden tijdens het selecteren, maar dat wil nog wel eens mis gaan.
Makkelijker is het om ieder stuk apart naar het Klembord te kopiëren en met enkele muisklikken deze ergens anders te plakken.

Het Klembord wissen

In de loop van de (werk)dag kan het klembord wat onoverzichtelijk worden. Maak het je zelf makkelijk door af en toe het klembord te wissen.

Het Klembord minimaliseren

Wanneer je ruimte-gebrek hebt op je beeldscherm, kun je via hetzelfde pijltje rechtsonder het klembord weghalen.
Let op: het klembord is dan niet meer actief! Items die erop staan worden wel bewaard.

Maar ik vind het handiger om in zo’n geval het klembord heel smal te maken. Je kopieeracties blijven in ieder geval bewaard.

Klembord-opties

Als het Klembord actief is dan zie je onderaan een knop om Opties in te stellen.

Wil je, dat voortaan het klembord altijd beschikbaar is, wanneer je iets kopieert: zet de eerste 2 opties aan.

Het nut van de vierde optie ontgaat mij. Weet iemand wat daar de bedoeling van is?