Categorie archief: Access

Weekdagen en weeknummers

Kalender-2016-LandscapeEén van de eerste artikelen op de site van G-Info ging over datums en de mogelijkheden van de diverse opmaak-opties (klik hier voor het betreffende artikel).

Vorige week kreeg ik een vraag over weekdagen en weeknummers in Access. Toen bleek, dat het probleem niet met opmaak was op te lossen, viel het me weer op, hoe ingewikkeld het kan zijn om dit soort vraagstukken met behulp van formules te tackelen.
Ook de (soms) grote verschillen tussen Excel en Access maken het er niet makkelijker op(hoezo, allebei een onderdeel van Microsoft Office!) .

Daarom maar eens wat mogelijkheden (en onmogelijkheden) op een rijtje gezet.

Opmaak in Excel

Zoals gezegd heb ik daar in een eerder artikel al eens over geschreven.
KalenderIn het Voorbeeldbestand heb ik een overzicht gemaakt met de dagen van de laatste week van 2016 en de eerste 2 van 2016.
De datums staan in kolom F; in de kolommen G t/m J staan verwijzingen naar de dagen in kolom F, maar met behulp van celopmaak (Ctrl-1 indrukken) is de lay-out van de cellen veranderd.

Voordeel hiervan is, dat makkelijk en snel de opmaak aangepast kan worden aan je wensen.
Nadeel is echter, dat je niet rechtstreeks formules of filters op deze cellen kunt los laten; de inhoud van die cellen blijft een datum (intern voor Excel een getal). Ook is het niet mogelijk om op deze cellen een Voorwaardelijke opmaak toe te passen.

NB wanneer je een Filter aanbrengt op het overzicht zul je zien, dat de filtermogelijkheden voor de kolommen F t/m J allemaal hetzelfde zijn: Excel ziet, dat het datums betreft en geeft daarom de mogelijkheden om te filteren op jaar, maand of dag. Het is NIET mogelijk om te filteren op bijvoorbeeld maandag.

Kalender2Experimenteer met de aangepaste opmaak via Celeigenschappen (Ctrl-1). Gebruik combinaties van de letters d, m en j; 1 letter, 2 letters etc. Het resultaat zie je direct in het vak Voorbeeld.

Datum-formules in Excel

Wil je kunnen rekenen, filteren, sorteren met de diverse onderdelen van een datum dan voldoet de opmaak niet meer. Je zult dan formules moeten gebruiken, waarmee de datum ‘ontleed’ wordt.

In het Voorbeeldbestand zijn in de kolommen K, L en M formules gebruikt om het jaar, de maand en de dag van een datum op te halen; in cel K4 bijvoorbeeld staat de formule =JAAR(F4).

Niets bijzonders, handig, recht-toe-recht-aan.
Willen we echter de weekdag ophalen (is het een maandag, dinsdag?) , dan blijkt er in Excel geen functie te zijn, die dat rechtsreeks oplevert; wel kunnen we het volgnummer van de dag in de week opvragen met de functie Weekdag.
Daar zit echter een addertje onder het gras: er is in de wereld geen overeenstemming over welk nummer bijvoorbeeld de maandag moet krijgen: 1 zoals in Europa gebruikelijk is of moet het 2 zijn (een nieuwe week begint op zondag)?
Aan de functie Weekdag moet dus altijd kenbaar worden gemaakt wat de eerste dag van de week is (als je dat niet meegeeft, dan wordt de standaard genomen en krijgt zondag volgnummer 1).
In cel N4 staat de formule =WEEKDAG(F4;KzWkDag)

Kalender3

Er wordt dus gezocht naar de weekdag, die hoort bij de datum in cel F4, waarbij als Type_getal de waarde 2 wordt meegegeven (maandag krijgt volgnummer 1).
Type_getal verwijst naar de cel met de naam KzWkDag (cel C3). Via Gegevensvalidatie kunnen daar alleen de voor deze functie toegestane waarden 1, 2, 3 en 11 t/m 17 ingevoerd worden.
Met VERT.ZOEKEN is in cel D3 te zien wat een bepaalde waarde inhoudt. Op het tabblad Parameters is de bron voor de Gegevensvalidatie en het verticaal zoeken te vinden in de kolommen B en C.

NB met een formule kunnen we dus het volgnummer van een weekdag afleiden, willen we echter een omschrijving dan wordt het iets ingewikkelder.
In cel Q4 staat een voorbeeld:
=KIEZEN(N4;”ma”;”di”;”wo”;”do”;”vr”;”za”;”zo”)
Let wel op: de volgorde van de dagen is afhankelijk van het Type_getal, dat in cel N4 is gebruikt!

Met opmaak is het niet mogelijk om in Excel het weeknummer te zien; wel is daar een functie voor. In het Voorbeeldbestand wordt die in kolom O gebruikt.
In cel O4 komt dan de formule =WEEKNUMMER(F4;KzWkNr)

Ook deze functie kent een 2e paramater (het Type_resultaat). Welke keuze hier gemaakt moet worden is nog iets ingewikkelder dan bij Weekdag. Want niet alleen is het van belang op welke dag een nieuwe week begint, ook moeten we aangeven wanneer we met weeknummer 1 beginnen.
Voor dat laatste onderscheidt Excel 2 systemen: bij Systeem 1 is Nieuwjaar het begin van week 1, bij Systeem 2 (het Europese systeem) begint week 1 met die week, waarin de eerste donderdag valt (en de week begint dan automatisch op maandag).

Om Systeem 2 te kiezen moet als 2e paramater voor Weeknummer het getal 21 meegegeven worden.

Opmaak in Access

Net als in Excel kunnen we in Access de datum “in stukken knippen” door een opmaak daaraan mee te geven.
In een query, die gebaseerd is op een tabel met een veld Datum, kunnen we bijvoorbeeld een nieuw veld toevoegen (met de naam JrF) om het jaar af te leiden:
JrF: Format([datum];”yyyy”)

LET OP Access verwacht Engelse functienamen en ook de Engelse afkorting voor het jaar.

We kunnen ook minder letters aan de functie meegeven, maar het resultaat is soms iets anders dan in Excel:
y: dit levert het volgnummer van de dag in het jaar
yy: het jaar in 2 cijfers
yyy: het jaar in 2 cijfers en direct daarachter het volgnummer van de dag in het jaar (dus een combinatie van yy en y)

Voor het maandnummer kan een vergelijkbare formule gebruikt worden:
MndF: Format([datum];”m”)
mm: het maandnummer altijd in 2 cijfers
mmm: de afkorting voor de maand (bijvoorbeeld jan)
mmmm: een volledige maand-aanduiding (bijvoorbeeld januari)

Voor de dag van de maand gebruiken we dan:
DagF: Format([datum];”d”)
dd: het dagnummer altijd in 2 cijfers
ddd: de afkorting voor de dag van de week (bijvoorbeeld ma)
dddd: een volledige dag-aanduiding (bijvoorbeeld maandag)

Binnen Access is het op deze manier ook mogelijk om het kwartaalnummer automatisch te genereren:
KwF: Format([datum];”q”)

Ook kunnen we de w als parameter gebruiken, maar ook nu dienen we nog extra parameters mee te geven:
WkF: Format([datum];”w”;2)
Deze formule levert het volgnummer van de dag in de week op. Maar net als in Excel moeten we dan wel opgeven op welke dag een nieuwe week begint. Geven we als 3e parameter een 0 (nul) mee, dan worden systeeminstellingen gebruikt, 1 dan begint de week op zondag etc.

Als laatste nog:
Wk2F: Format([datum];”ww”;2;2)
Dit levert het weeknummer in het jaar; net als in Excel is het wel van belang welk systeem daarbij gebruikt moet worden.
Als de 4e parameter 0 (nul) is dan worden de systeeminstellingen gebruikt, 1 dan begint de eerste week op Nieuwjaar, 2 dan is de eerste week afhankelijk van de eerste donderdag en 3 dan is de eerste week die week, die volledig in het nieuwe jaar ligt.

LET OP de functie Format retourneert een tekst; wil je rekenen met de uitkomst hiervan neem dan de waarde van het resultaat: Val(Format([datum];”d”))

Datum-formules in Access

Willen we direct rekenen met de dagen, maanden, weken etc (zonder gebruik te maken van de Val-functie) dan kent Access nog de functie DatePart.

Als we bijvoorbeeld in een query als veld opgeven
JrNr: DatePart([datum];”yyyy”)
dan resulteert dit in een getal met 4 cijfers, dat het jaar aangeeft.

Alle instellingen, die hiervoor bij Access-opmaak zijn toegelicht, zijn hier toepasbaar.
Dus bijvoorbeeld DatePart([datum];”ww”;1;2) levert het weeknummer (als getal) op, waarbij een week begint op zondag en de eerste donderdag bepalend is voor de weeknummer 1.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Van rijen naar kolommen

Iemand vroeg mij vorige week of ik kon helpen:

“Ik heb een bestand met leningnummers. Daar staan ook polisnummers en poliswaarden in, maar ze staan per lening onder elkaar; ik wil voor iedere lening 1 regel met daarachter alle polisgegevens.”

Nog net voordat ik begon te lachen (“Dat is zo geregeld!”, dacht ik) hoorde ik dat het bestand in Access stond. Oeps, dat wordt lastiger.
Ik zal alvast verklappen: het heeft me wel wat hoofdbrekens gekost. Ik denk ook nog steeds dat het simpeler moet kunnen. Dus bij deze een oproep aan Access-kenners: kom met een betere oplossing!

Excel-oplossing

Rijen1Voordat we het probleem in Access gaan aanpakken, eerst maar even een oplossing in Excel; dat is iets makkelijker!

Hiernaast staat een voorbeeldje van het bestand: zoals te zien is horen bij LenNr=1 meerdere Polisnr’s etc.

In het Excel-voorbeeldbestand zijn deze gegevens in het tabblad Basis terug te vinden.

In het tweede tabblad (Tussen) is een kopie gemaakt van deze gegevens en heb ik de kop van de tabel al uitgebreid met Polis1, Waarde1, Polis2 etc. In het voorbeeld is het maximaal aantal polissen per lening 4, maar ik heb al ruimte gemaakt voor 5 polissen:

Rijen2

Via wat handige formules kunnen we het bestand uitbreiden; maar zorg wel eerst dat alle regels van dezelfde lening onder elkaar komen, dus sorteren op LenNr.

  1. onder het kopje Polis1 komt het polisnummer van de eerste lening; eigenlijk zouden we in die cel (E3) kunnen verwijzen naar C3, maar ik heb daar een formule geplaatst, die direct naar beneden gekopieerd kan worden:
    =ALS(B3<>B2;C3;””)
    Dus als in kolom B een ander LenNr staat dan in de vorige regel, dan de inhoud van kolom C kopiëren, anders de cel leeg laten (2 “-tekens achter elkaar).
  2. onder Waarde1 (cel F3 dus) staat de formule
    =ALS(E3<>””;D3;””)
    Als in de vorige stap een polisnummer in ingevuld dan ook de waarde daarvan ophalen.
  3. Dan onder Polis2 (cel G3):
    =ALS(E3<>””;ALS(B4=B3;C4;””);””)
    We willen alle polisnummers op eenzelfde regel krijgen, dus Polis2 mag alleen maar ingevuld worden als er ook een Polis1 is (ALS(E3<>””).
    Daarna vergelijken we het LenNr van de huidige regel met de volgende regel; als dat hetzelfde nummer is dan halen we het betreffende polisnummer op
  4. Voor Waarde2 geldt hetzelfde als hiervoor bij Waarde1.
  5. De formule onder Polis3 mag niet meer verrassend zijn:
    =ALS(G3<>””;ALS(B5=B4;C5;””);””)
  6. Ook de andere kolommen krijgen vergelijkbare formules en dan alles naar beneden kopieren.Rijen3
Bijna klaar: alle polissen zijn mooi van de rijen naar de kolommen verplaatst, maar we hebben nog wat overbodige regels over:

Rijen4

  1. klik met de muis ergens in de tabel met gegevens
  2. kies de menutab Gegevens
  3. en binnen het vak Sorteren en filteren de optie Filter
  4. klik op het ‘vinkje’ achter Polis1 (de filteropties voor deze kolom) Rijen5
  5. zorg dat helemaal onderaan het vinkje bij (Lege cellen) uit staat
  6. selecteer de overblijvende gegevens en kopieer die naar een ander tabblad (zie Resultaat)

Access

Nu gaan we hetzelfde doen in Access.
In het Access-voorbeeldbestand (dit is gezipt, omdat de download anders vast kan lopen) staat hetzelfde basisbestand, in de tabel Basis.

Rijen6Om problemen te voorkomen bij het wegschrijven van de tussenstappen, creëren we eerst de Tussen-tabel met per veld het juiste gegevenstype.
NB Wanneer we Access de tabel laten aanmaken via een Tabelmaak-query, gaat Access ‘gokken’ welke type het veld moet krijgen; dat strookt lang niet altijd met onze wensen!

Rijen7Dan de eerste stap: via een query selecteren we van alle leningnummers het kleinste polisnummer (had ook de maximum-waarde kunnen zijn).
Via de button Rijen8 wordt dit een Toevoeg-query; records toevoegen aan de hiervoor aangemaakte tabel Tussen.

NB Access groepeert zelf per leningsnummer; de records hoeven dan ook niet gesorteerd te zijn, in tegenstelling tot de Excel-oplossing.

Het ophalen van een mogelijk tweede polisnummer is al wat ingewikkelder:

Rijen9Vanuit Basis selecteren we opnieuw de minimale waarde voor het polisnummer, maar via de zogenaamde outer-joins naar de Tussen-tabel wordt er voor gezorgd, dat combinaties van LenNr en PolisNr, die al voorkomen, niet meer meedoen (zie bij Criteria de voorwaarde Is Null).

Ook voor de volgende polisnummers zijn op vergelijkbare manier query’s gebouwd, waarbij meerdere koppelingen met dezelfde Tussen-tabel zijn aangemaakt.

Rijen10De tussentabel bevat nu records, waarbij leningnummers nog wel in verschillende records voorkomen, maar waar wel al de polisnummers op de ‘juiste’ plaats staan.

Ook missen we de poliswaardes nog in de tussentabel. Dat is opgelost door 5 Bijwerk-query’s, waarmee vanuit de Tussen-tabel aan de hand van LenNr en PolisNr de overeenkomende waarde in Basis wordt opgehaald:
Rijen11Rijen12
Om het gewenste overzicht te krijgen (alle leningnummers uniek met daarachter één of meerdere polisnummers) hebben we nog een laatste query nodig.

Transponeren

Voor diegene, die zich door de titel van het artikel (Van Rijen naar Kolommen) hebben laten misleiden, nog een toegift:
soms wil je in Excel gegevens, die onder elkaar staan, naast elkaar hebben of andersom. Hiervoor zijn geen ingewikkelde formules nodig: Excel heeft daar een ingebouwd commando voor:

  1. selecteer in het Excel-voorbeeldbestand alle relevante gegevens in het tabblad Basis
  2. klik met de rechter muisknop en kies Kopiëren
  3. selecteer de cel waar het resultaat moet komen; dit kan ook in een ander tabblad of in een andere werkmap zijn.
  4. Rijen13klik daar opnieuw met de rechter muisknop
  5. en kies nu Plakken speciaal…
  6. in het vervolgscherm staat een optie Transponeren; zet het vinkje aan en kies OK

Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Aantallen turven

De afgelopen weken kreeg ik in diverse situaties vragen over het tellen van aantallen.

Wat simpel lijkt, wil in geautomatiseerde systemen nog wel eens ingewikkelder uitpakken.
Of het nu het tellen is van het aantal regels in een Excel-bestand, het aantal lege velden in een Excel-kolom, het aantal records in een Access-tabel of het aantal niet-lege namen in een Access-record: het blijft goed opletten wat en hoe er geteld moet worden.

ToepenAantallen turven:
toen ik dit intikte, gingen mijn gedachten weer terug naar de vele gezellige avonden, die we lang geleden met een groepje hebben doorgebracht met het kaartspel Toepen. Bij dit spel worden de strafpunten op een bier- (of sinas-?)viltje geturfd door het zetten van streepjes.

Voor de liefhebbers volgt hieronder, na wat uitleg over het turven van aantallen in Excel en Access, een “geautomatiseerd bierviltje”.
Ik heb daarbij ook een weinig bekende manier van kopiëren gebruikt.

Excel

AantallenIn het Voorbeeldbestand staat in het tabblad Aantal vanaf cel  D15 een overzicht van de omzet per maand en regio van een kleine witgoedhandel (ze verkopen alleen stofzuigers, strijkijzers en broodroosters).

Uiteraard willen we de totale omzet weten (dat lossen we simpel op via een Som-formule);  maar vandaag zijn we meer geïnteresseerd in Aantallen.

AantallenExcel kent diverse standaard-functies om aantallen te berekenen.
De meest simpele (en meest gebruikte) functie is Aantal. Maar als we die toepassen op het bereik D15:D158 (de maanden) dan krijgen we als resultaat 0; idem voor Regio en Product. Pas het turven van aantallen in de kolom Bedrag levert een aantal groter dan nul op, namelijk 142. De functie Aantal telt alleen maar de inhoud van numerieke cellen!

NB een datum is voor Excel ook een getal; dus de functie Aantal turft ook datums (zie ook de cellen L18:M21 in het Voorbeeldbestand).

De functie Aantalarg voldoet al beter aan de verwachtingen (zie het overzicht hierboven): deze functie telt hoeveel cellen er gevuld zijn, onafhankelijk van de inhoud.

Mbv de functie Aantal.lege.cellen (denk aan de puntjes!) kunnen we analyseren of en hoeveel lege cellen er in een cellenbereik voorkomen.

Voor de volledigheid heb ik ook nog enkele voorbeelden van de functies Aantal.als en Aantallen.als opgenomen; zie voor nadere uitleg het artikel Tellen met voorwaarden.

Turf-alternatief

Om snel te kunnen achterhalen in welke regel er gegevens ontbreken, heb ik achter iedere regel, in kolom H, mbv de functie Aantalarg geteld hoeveel velden er gevuld zijn.
AantallenDoor cellen, die NIET 4 opleveren, via Voorwaardelijke opmaak te kleuren zien we direct in  welke regels het fout gaat
(in één van de komende artikelen zal ik op Voorwaardelijke opmaak terugkomen).

Voor de liefhebbers nog een andere manier van tellen waarbij gebruik wordt gemaakt van zogenaamde Array- of Matrix-formules.
Wanneer we in Excel in een cel intypen =D15<>”” (we kijken dus of cel D15 NIET leeg is) dan levert dit de waarde WAAR op (als die cel gevuld is tenminste!). Maar intern vertaalt Excel dit naar een 1 (en ONWAAR naar een 0; het blijft een computerprogramma!). Daar kunnen we gebruik van maken:
typ in een cel =1*(D15<>””) en we krijgen als resultaat 1.
NB denk aan de haakjes rond de voorwaarde.

We zouden in cel I15 kunnen intikken =1*(D15<>””)+1*(E15<>””)+1*(F15<>””)+1*(G15<>””) om te achterhalen hoeveel cellen er niet leeg zijn, maar dat kan makkelijker:

  1. tik in cel I15 in =Som(1*(
  2. selecteer de cellen D15 t/m G15
  3. en tik dan in (allemaal in cel I15) <>””))
  4. niet op Enter drukken, maar Ctrl-Shift-Enter

Excel zal nu voor alle cellen in het bereik D15:G15 beoordelen of die leeg is; vermenigvuldigd met 1 levert dit een serie 0’en en 1’en op, die door de functie Som bij elkaar worden opgeteld.

Door de combinatie Ctrl-Shift-Enter wordt er een Array- of Matrix-formule ingevoerd; in de formulebalk wordt dit weergegeven door {} rond de formule.

NB alleen op bovenstaande manier kun je een array-formule invoeren; het plaatsen van accolades rond een formule werkt niet!

Kopieer de array-formule naar beneden (via de vulgreep rechtsonder in cel I15 natuurlijk; zie het artikel Doorvoeren en sorteren).

Access

Binnen dit programma moeten we het doen met veel minder (functie-)mogelijkheden. Voor het tellen van het aantal records in een tabel kunnen we een Totaal-query maken en dan per veld in de regel Totaal: aangeven dat we het Aantal willen zien.
Aantal AccessHet resultaat hiervan komt overeen met het gebruik van de functie Aantalarg in Excel; dus het aantal niet-lege velden wordt geturfd voor ieder type veld.

Willen we het aantal niet-lege velden binnen een record weten (net als de berekening in de cellen H15 en I15 hiervoor in Excel) dan zullen we zelf een formule moeten intypen in de query.
Aantal Access

Optie 1:
Gevuld: IIf([Maand]<>””;1;0) + IIf([Regio]<>””;1;0) + IIf([Product]<>””;1;0) + IIf([Bedrag]<>0;1;0)

De eerste als-voorwaarde (in Access Iif) levert een 1 als Maand niet leeg is, anders 0; de tweede voorwaarde hetzelfde voor Regio enz. Door de 4 resultaten van de Iif’s op te tellen zien we per record hoeveel velden er gevuld zijn.

NB Bedrag is in Access een numeriek veld; de inhoud kunnen we dan niet vergelijken met een lege tekst, dus we controleren of de waarde niet gelijk is aan 0.

Optie 2:
Gevuld2: IIf(Not IsNull([Maand]);1;0) + IIf(Not IsNull([Regio]);1;0) + IIf(Not IsNull([Product]);1;0) + IIf(Not IsNull([Bedrag]);1;0)

Vergelijkbaar met Optie 1, maar we controleren mbv een Access-functie IsNull of een veld leeg is.

Optie 3:
Gevuld3: -(Not IsNull([Maand])) – (Not IsNull([Regio])) – (Not IsNull([Product])) – (Not IsNull([Bedrag]))

Dit is iets korter dan optie 2, waarbij we gebruik maken van de eigenschap dat de waarde WAAR in Access door -1 wordt voorgesteld (en ONWAAR door 0; dit laatste is WEL hetzelfde als in Excel!?).

NB denk aan de juiste plaats en het aantal van de haakjes.

Toepen

Zoals beloofd een “Excel-bierviltje”; zie het Voorbeeldbestand.
De (straf)puntentelling bij het kaartspel Toepen gebeurt van oudsher door het zetten van streepjes, waarbij een vijftal door een dwarsstreep wordt aangegeven. Heb je 15 strafpunten, dan lig je er uit; 14 punten is ook een bijzondere situatie: de tegenstanders krijgen bij verlies 2 strafpunten (of ze passen voor 1 punt). In het Brabants wordt dit “Pulleke” genoemd, op het “scorebord” aangeduid door een P.

In het tabblad ToepBasis worden alle benodigdheden voor het scorebord klaar gezet:

  1. Toepenin het blok B2:C4 staan de gebruikte symbolen, waarbij de cellen C2 t/m C4 een overeenkomende naam hebben gekregen (zie het Naamvak, linksboven)
  2. in de cellen B8:D12 staan de spelersnamen en hun scores: de stand binnen het lopende ‘potje’ en het aantal gewonnen ‘potjes’.
  3. ToepenF6:H12 is het scorebord/Excel-bierviltje.
    In kolom F worden de deelnemers gecombineerd met hun resultaten door cellen en teksten met behulp van het &-teken te koppelen; in cel F8 staat bijvoorbeeld =B8&”   (“&C8&”/”&D8&”)”
  4. kolom G is de meest interessante: hier wordt de score uit kolom C (de cellen C8:C12 hebben samen de naam Score gekregen) omgezet in de juiste streepjes:
    =ALS(Score>=15;Vijftal&Vijftal&Vijftal;ALS(Score=14;Vijftal&Vijftal&Paal;HERHALING(Vijftal;INTEGER(Score/5))&HERHALING(Streepje;REST(Score;5))))
    * als de Score groter of gelijk aan 15 is, dan wordt 3x de tekst uit de cel met de naam Vijftal geplaatst
    * bij een Score van 14, twee vijftallen en het teken voor het “Pulleke”
    * anders via de functie HERHALING zoveel vijftallen als we krijgen wanneer we Score door 5 delen (INTEGER betekent geheel getal) en zoveel streepjes als de REST is bij het delen van de Score door 5.
  5. kolom H bevat een simpelere vorm van de formule uit kolom G
  6. nog wat Voorwaardelijke opmaak toevoegen (‘kapot’: rode achtergrond, ‘Pulleke’: oranje achtergrond, meer dan 9 punten: oranje letters) en het Scorebord is klaar!

LET OP: de naam Score bevat de cellen C8 t/m C12. Wanneer we in kolommen rechts hiervan in een formule verwijzen naar Score, dan neemt Excel de met de rij overeenkomende waarde uit deze reeks. Dus in cel G8 wijst Score naar de waarde in cel C8, in G9 naar cel C9 etc.

ToepenHet “Excel-bierviltje” vinden we terug op het tabblad ToepScore.
Om het turven nog makkelijker te maken bevat het tabblad diverse Besturingselementen, een 10-tal zogenaamde Kringvelden Kringveld. Hiermee is het mogelijk om de scores snel te verhogen en te verlagen. De Kringvelden zijn gekoppeld aan de diverse scores in het vorige tabblad; klik met de rechtermuisknop op een Kringveld en bekijk de instellingen onder de optie Besturingselement opmaken …
Kringvelden
(en andere besturingselementen) vinden we onder de menu-tab Ontwikkelaars.

Het scoreformulier zelf is GEEN serie cellen maar een Excel-afbeelding; wanneer je er op klikt, zie je in de hoeken en in het midden van de randen plaatsen waar je met de cursor de afbeelding kunt vergroten en verkleinen.
En niet zo maar een statisch plaatje; nee, een zogenaamde Gekoppelde afbeelding. De inhoud van de afbeelding verandert met de inhoud van de cellen, waaraan deze gekoppeld is! Mooier kunnen we het niet maken….

Hoe maken we een gekoppelde afbeelding?

  1. selecteer de cellen, die in de afbeelding moeten worden weergegeven (voor ons scorebord de cellen F6:H12 in het tabblad ToepBasis)
  2. klik rechts in de selectie en kies Kopiëren
  3. Toepenga naar de cel waar de afbeelding moet komen, klik rechts en kies de optie Plakken speciaal ….
    In het submenu kies je onder Andere plakopties de 4e mogelijkheid, Gekoppelde afbeelding
  4. pas via de grepen aan de zijkant van de afbeelding de grootte aan en/of verplaats het plaatje naar de gewenste locatie.

NB Gekoppelde afbeeldingen kunnen een belangrijke rol spelen in rapportages. Wanneer je daar meerdere overzichten onder elkaar wilt weergeven, zal het meestal zo zijn, dat de gewenste kolom-breedtes van die overzichten niet met elkaar overeenkomen; maar Excel kan standaard maar één breedte weergeven.
Zet  de overzichten naast elkaar op een tabblad (of nog beter op verschillende tabbladen) en maak van deze overzichten Gekoppelde afbeeldingen; deze kun je dan onder elkaar zetten, zonder dat de kolombreedtes elkaar nog beïnvloeden.

LET OP Gekoppelde afbeeldingen vergen veel van Excel en van je PC! Sla je werk regelmatig op en gebruik niet teveel dynamische afbeeldingen in één werkmap.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Steekproeven

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

SteekproefAls 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:

  1. Steekproefkopieer het SAP-werkblad (de tab onderaan ‘vastpakken’ met de muis en, terwijl je de Ctrl-toets vasthoudt, het tabblad verslepen)
  2. voeg in de kopie achter Bedrag een nieuw kopje toe: Aselect
  3. 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.
  4. kopieer deze formule naar beneden, zodat alle regels een willekeurig getal krijgen toegewezen
  5. plaats de cursor ergens in de Aselect-kolom en sorteer: kies in de menubalk Gegevens en in het blok Sorteren en filterenSteekproef
  6. 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

Steekproef 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:

  1. kies Maken/Queryontwerp
  2. voeg de tabel SAP aan het ontwerp toe
  3.  sleep de drie velden naar het ontwerp:
    Steekproef
  4. voeg een nieuw veld toe achter Bedrag:
    Aselect: Rnd()
    LET OP: in Access worden Engelstalige functies gebruikt
  5. kies linksboven de GegevensbladweergaveSteekproef
  6. waarschijnlijk is de vierde kolom niet leesbaar: maak die breder.
  7. 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:
Steekproef

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:Steekproef

  1. klik rechts in het lege gebied naast de tabel en kies Eigenschappen
  2. tik in het Eigenschappenvenster bij Top-waarden 10 in
  3. kies linksboven de Gegevensbladweergave
  4. 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:

  1. zorg dat het bedrag groter dan (of gelijk is aan) nul: Abs zorgt er voor, dat een eventueel min-teken verdwijnt
  2. tel er 1 bij op, zodat nul ook niet meer voorkomt
  3. 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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Handige opties in Access

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:

  1. 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
  2. Ctrl-c; de selectie kopiëren
  3. naar Excel gaan (eventueel via Alt-Tab als Excel al geactiveerd is)
  4. Ctrl-n; een nieuwe werkmap openen
  5. Ctrl-v; gegevens uit Access plakken
  6. Ctrl-pijl om naar de laatste regel te gaan
  7. dan de cursor direct onder de kolom met de gegevens, waarvan ik een totaal wil weten
  8. 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.

Totalen

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:

  1. kies het menu-tabblad StartTotalen2
  2. zoek daarbinnen het blok Records
  3. klik op Totalen
  4. onderaan het scherm komt een nieuwe regel met vooraan het woord Totaal
  5. 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:ObjAfhankel

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

ObjAfhankel2

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.

 


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: