Inhoudsopgave studieboek Access 2010

Inhoudsopgave | 3
Inhoudsopgave
Voorwoord...............................................................................................................9
Hoofdstuk 1: Beginnen met Access......................................................11
Wat zijn databases?..................................................................................................................12
Tabellen..........................................................................................................................12
Query's...........................................................................................................................13
Formulieren...................................................................................................................14
Rapporten......................................................................................................................14
Wat is een macro?.........................................................................................................15
Wat is een module?.......................................................................................................15
Taak: Access opstarten.............................................................................................................15
Taak: Access afsluiten..............................................................................................................16
Taak: Database openen............................................................................................................17
Access venster...........................................................................................................................17
Nieuwe database venster.........................................................................................................19
Taak: Database opslaan als.....................................................................................................20
Bestandsformaten van Access.................................................................................................21
Hoofdstuk 2: Database SNOEP verkennen.........................................23
Voorbeelddatabase SNOEP....................................................................................................24
Beveiliging macro's..................................................................................................................27
Database verkennen................................................................................................................29
Taak: Tabel verkennen............................................................................................................30
Taak: Formulier verkennen......................................................................................................31
Taak: Opdrachtknop gebruiken..............................................................................................33
Taak: Record zoeken................................................................................................................34
Taak: Query verkennen...........................................................................................................34
Taak: Rapport verkennen........................................................................................................36
Taak: Sorteren..........................................................................................................................38
Taak: Filteren...........................................................................................................................39
Afdrukken van Access onderdelen.........................................................................................41
Opgaven verkennen................................................................................................................42
Opgave: expl001 - Selectie Bonbons............................................................................42
Opgave: expl002 - Selectie Klanten op plaatsnaam...................................................42
Opgave: expl003 - Selectie Klanten op postcode........................................................42
Softwijs
4 | Access
Hoofdstuk 3: Gegevens toevoegen/wijzigen.......................................43
Over gegevens invoeren..........................................................................................................44
Tabel bewerkingen..................................................................................................................44
Taak: Nieuwe klant toevoegen...............................................................................................44
Taak: Nieuwe bonbon toevoegen...........................................................................................45
Taak: Nieuwe doos toevoegen................................................................................................46
Restrictie en validatie van gegevens.......................................................................................47
Opgaven gegevensbewerking.................................................................................................48
Opgave: data001 - Bonbons toevoegen.......................................................................48
Opgave: data002 - Order toevoegen...........................................................................48
Hoofdstuk 4: Tabellen maken............................................................49
Over tabellen...........................................................................................................................50
Velden in tabellen...................................................................................................................50
Veldnamen....................................................................................................................50
Gegevenstype................................................................................................................50
Eigenschappen van een veld........................................................................................52
Veldlengte eigenschap..................................................................................................52
Validatie....................................................................................................................................53
Invoermaskers...............................................................................................................53
Validatieregel en tekst..................................................................................................55
Taak: Nieuwe tabel handmatig maken..................................................................................56
Taak: Nieuwe tabel via Excel import......................................................................................57
Taak: Keuzelijst maken...........................................................................................................58
Relaties tussen tabellen..........................................................................................................60
Relatietypes...................................................................................................................60
Relaties maken, bewerken of verwijderen...................................................................61
Referentiële integriteit.................................................................................................63
Taak: Relatie Dozen-Doosdetails maken....................................................................64
Opgaven tabellen....................................................................................................................65
Opgave: tabl001 - Dubbele namen voorkomen..........................................................65
Opgave: tabl002 - Tussenvoegsel in namen................................................................65
Opgave: tabl003 - Titelveld met keuzelijst.................................................................65
Opgave: tabl004 - Valutanotatie.................................................................................66
Opgave: tabl005 - Samengestelde sleutel...................................................................66
Opgave: tabl006 - Veld doelgroep met keuzelijst......................................................66
Opgave: tabl007 - Relatie maken................................................................................66
Opgave: tabl008 - Tabel Werknemers........................................................................66
Softwijs
Inhoudsopgave | 5
Opgave: tabl009 - Contactregistratie..........................................................................67
Opgave: tabl010 - Klachtenregistratie.........................................................................67
Opgave: tabl011 - Referentiële integriteit....................................................................67
Hoofdstuk 5: Query's..........................................................................69
Over query's maken................................................................................................................70
Taak: Klanten en orders..........................................................................................................70
Criteria in query's....................................................................................................................73
Taak: Orders Utrechtse klanten.............................................................................................74
Taak: Klanten met doos Kers..................................................................................................75
Groeperen en Berekeningen...................................................................................................78
Taak: Aantal klanten per provincie........................................................................................82
Taak: Kolomtitel wijzigen.......................................................................................................84
Taak: Berekend veld................................................................................................................84
Taak: Eerste order per klant...................................................................................................86
Taak: Parameterquery.............................................................................................................88
Actiequery's.............................................................................................................................90
Taak: Bijwerkquery...................................................................................................................91
Taak: Tabelmaakquery............................................................................................................93
Taak: Kruistabelquery.............................................................................................................95
Opgaven query's......................................................................................................................99
Opgave: quer001 - Klanten uit Friesland en Groningen............................................99
Opgave: quer002 - Direct mail campagne doos MARS.............................................99
Opgave: quer003 - Bonbondozen met lage prijs........................................................99
Opgave: quer004 - Bonbons melk en puur met lage kosten.....................................99
Opgave: quer005 - Klanten uit Enschede, Hengelo of Almelo................................100
Opgave: quer006 - Klanten uit Amsterdam met postcode 20*................................100
Opgave: quer007 - Bonbons zonder vulling..............................................................100
Opgave: quer008 - Klanten niet uit Amsterdam......................................................100
Opgave: quer009 - Aantal bonbons per doos...........................................................100
Opgave: quer010 - Aantal bonbonsoorten per doos..................................................101
Opgave: quer011 - Aantal orders per regio.................................................................102
Opgave: quer012 - Totale omzet per klant.................................................................102
Opgave: quer013 - Klanten die order geplaatst hebben............................................102
Opgave: quer014 - Klanten waarvan achternaam Jansen of Janssen is uit regio
Noord.......................................................................................................................102
Opgave: quer015 - Bonbons met vulling en zonder noot.........................................103
Opgave: quer016 - Dozen zwaarder dan 150 gram met prijs van hoogstens 35
euro..........................................................................................................................103
Softwijs
6 | Access
Opgave: quer017 - Dozen met prijs tussen 17 en 25 en voorraad van minstens
400...........................................................................................................................103
Opgave: quer018 - Totale omzet per provincie.........................................................104
Opgave: quer019 - Klanten die week voor de kerst besteld hebben........................104
Opgave: quer020 - Dozen met lage omzet................................................................105
Opgave: quer021 - Klanten met omzet hoogstens 50 en postcode beginnend met
22..............................................................................................................................105
Opgave: quer023 - Klanten voor Zoet en bitter.........................................................105
Opgave: quer024 - Plaatsen met minstens 2 klanten...............................................106
Opgave: quer025 - Parameterquery bonbons............................................................106
Opgave: quer026 - Bijwerkquery prijsverlaging........................................................106
Opgave: quer027 - Kruistabel dozen per gewicht.....................................................106
Opgave: quer028 - Bijwerkquery prijsverhoging.......................................................107
Opgave: quer029 - Kruistabel omzet per klant per maand......................................107
Hoofdstuk 6: Formulieren maken....................................................109
Over formulieren maken........................................................................................................110
Hoofd- en subformulieren...........................................................................................110
Gekoppelde formulieren...............................................................................................111
Besturingselementen en Indelingen......................................................................................112
Taak: Automatisch formulier.................................................................................................113
Taak: Hoofd- en subformulier...............................................................................................116
Taak: Invoerformulier Klanten...............................................................................................118
Taak: Invoerformulier Bonbons............................................................................................120
Taak: Invoerformulier Dozen................................................................................................122
Taak: Invoerformulier Orders................................................................................................123
Taak: Ordertotaal...................................................................................................................126
Taak: Formulier met grafiek..................................................................................................127
Taak: Gekoppelde formulieren..............................................................................................130
Opgaven formulieren.............................................................................................................133
Opgave: form001 - Klanten..........................................................................................133
Opgave: form002 - Bonbons in dozen........................................................................133
Opgave: form003 - Dozen per klant...........................................................................134
Hoofdstuk 7: Rapporten maken........................................................135
Over rapporten maken...........................................................................................................136
Taak: Etiketten maken...........................................................................................................136
Taak: Automatisch rapport....................................................................................................139
Taak: Groepsrapport..............................................................................................................139
Softwijs
Inhoudsopgave | 7
Taak: Bonbon afbeeldingen..................................................................................................144
Opgaven rapporten................................................................................................................147
Opgave: rapp001 - Verkoop per regio per doos.........................................................147
Opgave: rapp002 - Omzet per doos per regio...........................................................147
Opgave: rapp003 - Dooskosten met Bonbonkosten.................................................148
Opgave: rapp004 - Dooskosten met Bonbonkosten gegroepeerd...........................148
Opgave: rapp005 - Omzet per doos per order per periode......................................149
Opgave: rapp006 - Doos per pagina..........................................................................149
Opgave: rapp007 - Doosetiketten..............................................................................149
Opgave: rapp008 - Jaarafzet dozen per regio............................................................150
Hoofdstuk 8: Hulpmiddelen..............................................................151
Over hulpmiddelen................................................................................................................152
Taak: Analyse van een tabel...................................................................................................152
Database comprimeren..........................................................................................................154
Hoofdstuk 9: Macro's maken.............................................................155
Over macro's maken..............................................................................................................156
Taak: Klant zoeken op code...................................................................................................156
Taak: Klant zoeken op naam.................................................................................................159
Taak: Keuzelijst dooscode.....................................................................................................162
Hoofdstuk 10: Integratie met Office.................................................163
Over integratie met Office....................................................................................................164
Taak: Mailmerge.....................................................................................................................164
Taak: Export naar Word........................................................................................................166
Taak: Export naar Excel.........................................................................................................166
Opgaven integratie.................................................................................................................167
Opgave: intg001 - Standaardbrief...............................................................................167
Opgave: intg002 - Export klantgegevens...................................................................167
Hoofdstuk 11: Normaliseren..............................................................169
Over normaliseren.................................................................................................................170
Sleutel.....................................................................................................................................170
Normalisatieproces................................................................................................................174
0 NV (Inventarisatie gegevens)...................................................................................174
1 NV..............................................................................................................................176
2 NV..............................................................................................................................176
Softwijs
8 | Access
3 NV..............................................................................................................................177
Samenvatting normalisatieproces..............................................................................177
Gegevens Structuur Diagram (GSD).....................................................................................178
Opgaven normaliseren..........................................................................................................178
Opgave: norm001 - Orders..........................................................................................178
Opgave: norm003 - Cursusoverzicht..........................................................................179
Opgave: norm004 - Garage.........................................................................................179
Opgave: norm005 - Klantcontacten...........................................................................179
Opgave: norm006 - Hoveniersbedrijf........................................................................180
Opgave: norm007 - Onderdelen................................................................................180
Opgave: norm008 - Relaties........................................................................................181
Opgave: norm009 - Optimaliseren database.............................................................181
Opgave: norm010 - Sleutels en tabellen.....................................................................181
Opgave: norm011 - Doe-het-zelf zaak........................................................................182
Opgave: norm012 - Vrachtbrief...................................................................................182
Opgave: norm013 - Bestelbon.....................................................................................183
Opgave: norm014 - Boekingen...................................................................................184
Opgave: norm015 - Integratie.....................................................................................184
Opgave: norm016 - Postcodes.....................................................................................185
Hoofdstuk 12: Cases...........................................................................187
Opgave: case001 - Klachtenregistratie..................................................................................188
Opgave: case002 - Wasserette...............................................................................................190
Opgave: case003 - Marketing database................................................................................192
Opgave: case004 - Appartementengebouw..........................................................................193
Colofon.................................................................................................................195
Softwijs