donderdag 19 september 2013

Cursussen: inhoudsopgaven van eigen cursusmateriaal voor trainingen die ik geef


1       Basiscursus ANSI SQL

In dit cursusmateriaal houd ik rekening met de verschillende varianten van de taal SQL. Ik heb deze cursus verschillende malen gegeven in Access and SQL Server Management Studio. Andere tools zou ook geen probleem zijn.
Duur cursus: 2 dagen
INHOUDSOPGAVE ANSI SQL
1                 Inleiding                                                                                                              3
1.1               Definitie                                                                                                              3
1.2               Beschrijving                                                                                                         3
2                 Databasenormalisatie                                                                                            4
2.1               Inleiding                                                                                                              4
2.2               Hiërarchie van normaalvormen                                                                              4
2.3               Databasenormalisatie: functioneel belicht                                                              14
3                 Data Query Language                                                                                           16
3.1               Inleiding                                                                                                              16
3.2               SELECT FROM                                                                                                     16
3.3               TOP                                                                                                                     17
3.4               ORDER BY                                                                                                           18
3.5               Alias                                                                                                                    20
3.6               DISTINCT                                                                                                            20
3.7               Opgaven                                                                                                              21
4                 Concateneren en casten                                                                                        22
4.1               Concateneren                                                                                                       22
4.2               CASTING                                                                                                             22
5                 Voorwaarden                                                                                                        23
5.1               WHERE                                                                                                                23
5.2               OPERATOREN                                                                                                      24
5.3               NULL                                                                                                                  24
5.4               Opgaven                                                                                                              25
5.5               AND en OR                                                                                                         25
5.6               Opgaven                                                                                                              26
5.7               IN                                                                                                                       26
5.8               NOT                                                                                                                    27
5.9               BETWEEN                                                                                                            27
5.10             LIKE                                                                                                                    28
5.11             Jokers                                                                                                                  29
5.12             CASE                                                                                                                   31
5.13             Opgaven                                                                                                              33
6                 Functies                                                                                                               34
6.1               Rekenkundige functies                                                                                          34
6.2               Statistische functies                                                                                              34
6.3               Opgaven                                                                                                              38
6.4               Scalaire functies                                                                                                   38
6.5               Opgaven                                                                                                              39
7                 Groeperen                                                                                                            40
7.1               GROUP BY                                                                                                          40
7.2               HAVING                                                                                                              41
7.3               Opgaven                                                                                                              42
8                 Meer tabellen                                                                                                       43
8.1               Subqueries: regels                                                                                                 43
8.2               Subqueries met IN                                                                                                43
8.3               Subqueries als expressie                                                                                        44
8.4               Subqueries met EXISTS                                                                                          44
8.5               Subqueries met ALL en ANY                                                                                  45
8.6               Opgaven                                                                                                              47
8.7               Geneste subqueries in de SELECT                                                                           47
8.8               Opgaven                                                                                                              48
8.9               Geneste subquery in de FROM                                                                               48
8.10             Joins                                                                                                                    48
8.11             Opgaven                                                                                                              53
9                 Set operatoren                                                                                                      54
9.1               UNION                                                                                                                54
9.2               INTERSECT                                                                                                          55
9.3               EXCEPT                                                                                                                56
9.4               Opgaven                                                                                                              57
10                Data Manipulation Language                                                                                 58
10.1             SELECT INTO                                                                                                       58
10.2             INSERT INTO                                                                                                       59
10.3             UPDATE                                                                                                              60
10.4             DELETE                                                                                                                61
10.5             DELETE alle rijen                                                                                                  62
10.6             Opgaven                                                                                                              62
11                Data Definition Language                                                                                     63
11.1             CREATE                                                                                                               63
11.2             DROP                                                                                                                  64
11.3             TRUNCATE                                                                                                          65
11.4             ALTER                                                                                                                 65
12                Transaction Control Language                                                                                67
13                Data Control Language                                                                                         68
14                Performance                                                                                                         69
14.1             Zo weinig mogelijk OR gebruiken                                                                          69
14.2             De grootste tabel als laatste                                                                                   69
14.3             Liever geen SELECT *                                                                                           69
14.4             Zo weinig mogelijk DISTINCT gebruiken                                                                69
14.5             Pas op met UNION                                                                                               70
14.6             Snelheid van operatoren                                                                                        70
14.7             Gebruik indien mogelijk BETWEEN                                                                        70
14.8             BETWEEN boven IN                                                                                              70
14.9             Vermijd indien mogelijk de SUBSTRING functie                                                     70
14.10           Vermijd de combinatie NOT IN                                                                             70
14.11           IN of EXISTS                                                                                                         71
15                Vergelijking scalaire functies in SQL dialecten                                                       72
16                Datatypes                                                                                                            73
16.1             Microsoft Access Datatypes                                                                                   73
16.2             MySQL Datatypes                                                                                                73
16.3             SQL Server datatypes                                                                                            75
17                Quick reference                                                                                                   77
18                Relatieschema Noordenwind database                                                                    80
19                Uitwerkingen opgaven                                                                                          81

2       MS Access als middel voor data-analyse en rapportage

Het cursusmateriaal van deze training heb ik helemaal toegespitst op data-analyse en rapportage met Access als instrument. Alle onderwerpen van deze cursus hebben hiermee te maken.
Duur cursus: 2 dagen
I N H O U D S O P G A V E
1                 Inleiding                                                                                                              4
1.1               Cursusopzet                                                                                                          4
2                 Microsoft Office Access                                                                                        5
2.1               Inleiding                                                                                                              5
2.2               Versies en namen                                                                                                 5
2.3               Belangrijkste inhoudelijke verschillen 2007 en eerdere versies                                 5
3                 Wanneer Access, wanneer Excel                                                                            7
4                 De omvang van een Access database                                                                     8
4.1               Inleiding                                                                                                              8
4.2               Bepalend voor de grootte                                                                                       8
4.3               Mogelijke aanpassingen van de grootte van de database                                          9
5                 Performance                                                                                                         11
6                 Tabellen                                                                                                              12
6.1               Inleiding                                                                                                              12
6.2               Database normalisatie                                                                                           12
6.2.1            Inleiding                                                                                                              12
6.2.2            Hiërarchie van normaalvormen                                                                              12
6.2.3            Database normalisatie: functioneel belicht                                                             23
6.3               Normalisatie versimpeld                                                                                        24
6.3.1            Regels en voorbeelden                                                                                          24
6.3.2            Voorbeeld: Noordenwind                                                                                       25
6.4               Indexen                                                                                                               26
6.4.1            Inleiding                                                                                                              26
6.4.2            Probleemstelling                                                                                                   26
6.4.3            Werking                                                                                                               27
6.4.4            Structuur                                                                                                              27
6.4.5            Nadelen                                                                                                               27
6.5               Opgaven                                                                                                              27
7                 Externe gegevens                                                                                                  28
7.1               Inleiding                                                                                                              28
7.2               Van Access naar Access                                                                                        28
7.2.1            Gegevens importeren uit een andere Access-database                                              28
7.2.2            Koppelen met gegevens uit een andere Access-database                                          32
7.3               Van Access naar Excel vice versa                                                                          33
7.3.1            Scenario's voor het importeren van excel-gegevens                                                 33
7.3.2            De eerste keer dat je gegevens uit excel importeert                                                 33
7.3.3            Meer over het importeren van gegevens uit Excel                                                    34
7.3.4            Scenario's voor het importeren van excel-gegevens                                                 34
7.3.5            Bij de eerste keer dat je gegevens uit excel importeert                                            34
7.3.6            Gegevens importeren uit Excel                                                                              34
7.3.7            Problemen met ontbrekende of onjuiste waarden oplossen                                        39
7.3.8            Koppelen naar gegevens in Excel                                                                           41
7.3.9            Scenario's voor het maken van een koppeling naar een excel-werkblad                    41
7.3.10          Bij de eerste keer is dat je een koppeling naar een excel-werkblad maakt                 41
7.3.11          Problemen onjuiste waarden in een gekoppelde tabel oplossen                                 43
7.4               Access en tekstbestanden                                                                                      44
7.4.1            Informatie over tekstbestanden en ondersteunde indelingen                                      44
7.4.2            Gegevens uit een tekstbestand importeren                                                              45
7.4.3            Problemen met ontbrekende of onjuiste waarden in een geïmporteerde tabel             49
7.4.4            Een koppeling naar een tekstbestand maken                                                           50
7.4.5            Problemen met #Getal! en onjuiste waarden in een gekoppelde tabel oplossen          53
8                 Query's                                                                                                                55
8.1               Selectiequery                                                                                                       55
8.2               Kiezen van de recordbron                                                                                      56
8.3               Koppelen van tabellen en/of query's                                                                       56
8.4               kiezen van de benodigde velden                                                                            57
8.5               Criteria toevoegen aan query's                                                                               60
8.6               Groeperen                                                                                                            63
8.7               kruistabelquery                                                                                                     65
8.8               Bijwerkquery                                                                                                        65
8.9               toevoegquery                                                                                                       66
8.10             verwijderquery                                                                                                     66
8.11             tabelmaakquery                                                                                                    67
8.12             Samenvoegquery                                                                                                  67
8.13             Algemene richtlijnen voor het opstellen van Query's                                               67
8.14             Opgaven bij de database Makelaardij                                                                    67
9                 Rapporten                                                                                                            74
9.1               Inleiding                                                                                                              74
9.2               Een recordbron kiezen                                                                                           74
9.3               Een rapport maken met het hulpmiddel Rapport                                                       74
9.4               Een rapport maken met de wizard Rapport                                                              74
9.5               Een rapport maken met het hulpmiddel Leeg rapport                                                75
9.6               De rapportsecties                                                                                                  75
9.7               Besturingselementen                                                                                             76
9.8               Een rapport aanpassen in de indelingsweergave                                                       77
9.9               Een rapport aanpassen in de ontwerpweergave                                                        77
9.10             Besturingselementen aan een rapport toevoegen                                                      78
9.11             Uw werk opslaan                                                                                                  79
9.12             Een rapport weergeven, afdrukken of als e-mailbericht verzenden                             79
9.13             Opgaven                                                                                                              82
10                Maken van rapporten: een uitgewerkte oefening                                                     83
10.1             Inleiding                                                                                                              83
10.2             Informatie-analyse                                                                                                83
10.3             Relatieschema                                                                                                      84
10.4             Voorbeeld in Access                                                                                             85
10.5             Uitwerking Access                                                                                                85

3       Excel rekenmodellen en dashboards

Dit cursusmateriaal heb ik ontwikkeld voor het bouwen van rekenmodellen in Excel en het presenteren van resultaten met behulp van de grafische hulpmiddelen van Excel. Belangrijke type grafieken als Pareto, Boston Consulting Group Matrix, Waterval, Speedometer en diverse andere leg ik uit met voorbeelden die zo simpel mogelijk zijn opgezet.
Duur cursus: 2 dagen
I N H O U D S O P G A V E
1                 Inleiding                                                                                                              1
1.1               Rekenmodellen                                                                                                    1
1.2               Dashboards                                                                                                          1
1.3               Onze aanpak in deze cursus                                                                                  2
2                 Modelmatig werken in Excel                                                                                 3
2.1               Informatieanalyse                                                                                                 3
2.2               Opzet van de rekenbladen                                                                                    3
2.2.1            De output                                                                                                             3
2.2.2            Invoerbladen                                                                                                        4
2.2.3            Opzet bewerkingsbladen                                                                                       4
2.3               Beveiliging                                                                                                          4
2.4               Eventueel gebruik van VBA                                                                                   5
3                 Technische zaken apart toegelicht                                                                         6
3.1               Invoer van data                                                                                                    6
3.1.1            Constanten                                                                                                           6
3.1.2            Variabelen: startwaarden en parameters                                                                  6
3.1.3            Bereik (range)                                                                                                       8
3.1.4            Formules en bereiken                                                                                            8
3.1.5            Namen baseren op flexibele bereiken                                                                     8
3.1.6            Namen uit werkmap afdrukken                                                                              14
3.1.7            Blok met gegevens                                                                                               15
3.1.8            Data importeren van internet/intranet                                                                     16
3.1.9            Dynamische, parameter gestuurde koppeling met database                                      18
3.1.9.1         Inleiding                                                                                                              18
3.1.9.2         Uitwerking                                                                                                           18
3.1.10          Opgaven                                                                                                              23
3.1.11          Valideren van gegevens                                                                                        23
3.1.12          Formulierbesturingselementen: schuifbalk, keuzelijst etc.                                        25
3.1.13          Celstijlen of opmaakprofielen                                                                                27
3.2               Bewerken van data                                                                                               28
3.2.1            Belangrijkste gebruikte functies                                                                             28
3.2.1.1         ADRES (ADDRESS)                                                                                               28
3.2.1.2         INDEX (INDEX)                                                                                                    29
3.2.1.3         INDIRECT (INDIRECT)                                                                                          32
3.2.1.4         KIEZEN (CHOOSE)                                                                                               34
3.2.1.5         SOMMEN.ALS (SUMIFS) vanaf Excel 2007                                                             36
3.2.1.6         SOMPRODUCT (SUMPRODUCT): selectief optellen                                               36
3.2.1.7         SUBTOTAAL (SUBTOTAL)                                                                                     38
3.2.1.8         VERGELIJKEN (MATCH)                                                                                        39
3.2.1.9         VERSCHUIVING (OFFSET)                                                                                    41
3.2.1.10       ZOEKEN (LOOKUP)                                                                                              42
3.2.1.11       Combinatie INDEX en VERGELIJKEN                                                                      44
3.2.2            Blok met gegevens omzetten naar een Excel lijst/tabel                                            45
3.2.3            Databases & lijsten                                                                                               47
3.2.3.1         Autofilter: aantal records als resultaat                                                                     47
3.2.3.2         Autofilter en de aggregatiefuncties van de statusbalk                                               47
3.2.3.3         Autofilter: niet voor alle kolommen uit het lijstbereik                                              47
3.2.3.4         Autofilter en subtotalen                                                                                         47
3.2.3.5         Uitgebreid filter                                                                                                    48
3.2.3.6         Uitgebreid filter: verwijderen dubbele waarden                                                       50
3.2.3.7         Sorteren speciaal                                                                                                  51
3.2.4            Data splitsen                                                                                                        52
3.2.5            Concateneren (concatenate)                                                                                  53
3.3               Output                                                                                                                 53
3.3.1            Draaitabellen                                                                                                       53
3.3.1.1         Dynamisch bereik                                                                                                 53
3.3.1.2         Groeperen op datum                                                                                             55
3.3.1.3         Groeperen op getallen                                                                                           57
3.3.1.4         Handmatig groeperen                                                                                            58
3.3.1.5         Uitkomst weergeven als percentage van de kolom                                                  59
3.3.1.6         Running totals of cumulatieven                                                                              60
3.3.1.7         Gewogen gemiddelde in een draaitabel                                                                  61
3.3.1.8         Handmatig sorteren                                                                                               62
3.3.2            Voorwaardelijke opmaak                                                                                       63
3.3.3            Grafieken algemeen                                                                                             68
3.3.3.1         Grafiektypen                                                                                                        68
3.3.3.2         Bijzondere opties bij grafieken                                                                               72
3.3.4            Grafieken op basis van benoemde bereiken en formules                                           75
3.3.5            Sparklines                                                                                                            76
3.4               Overig                                                                                                                 77
3.4.1            Documenteren                                                                                                      77
3.4.2            Opmerkingen invoegen                                                                                         78
3.4.3            Foute uitkomsten van formules ondervangen                                                           79
3.4.4            Beveiliging                                                                                                          79
3.4.5            Verbergen van formules                                                                                         81
3.4.6            Wijzigingen bijhouden/archiveren                                                                          81
3.4.7            VBA voor knoppen en het verbergen van werkbladen                                               82
3.4.8            Aanzetten tab Ontwikkelaars in Excel 2007 en hoger                                              84
4                 Performance                                                                                                         85
4.1               Inleiding                                                                                                              85
4.2               Herberekening                                                                                                      85
4.3               Voorbeelden                                                                                                         85
5                 Case voorbeelden van dynamische grafieken                                                          87
5.1               Grafiek met verschillende kleur voor boven en onder de norm                                  87
5.2               Grafiek op basis van wisselende rijen                                                                     88
5.2.1            Met schuifbalk                                                                                                     88
5.2.2            Met macro                                                                                                           90
5.3               Automatisch gesorteerde grafiek                                                                            91
5.4               Grafiek: hoog laag met een paar simpele trucjes                                                     93
5.5               Grafieken op basis van kolommen met wisselende lengte                                        94
5.6               Grafiek met aparte input                                                                                       96
5.6.1            Opgaven                                                                                                              99
5.7               Histogram                                                                                                            102
5.7.1            Inleiding                                                                                                              102
5.7.2            Uitwerking                                                                                                           103
5.8               Dashboard                                                                                                            106
5.8.1            Inleiding                                                                                                              106
5.8.2            Hoe maken we zo iets?                                                                                         107
5.8.3            Opgaven                                                                                                              111
5.9               Grafiek met datumlijn                                                                                           111
5.9.1            Inleiding                                                                                                              111
5.9.2            Uitwerking                                                                                                           111
5.10             Paretodiagram                                                                                                      115
5.10.1          Inleiding                                                                                                              115
5.10.2          Uitwerking                                                                                                           115
5.11             Een Gantt grafiek in Excel                                                                                     117
5.11.1          Inleiding                                                                                                              117
5.11.2          Uitwerking                                                                                                           118
5.12             Boxplot diagram                                                                                                   123
5.12.1          Inleiding                                                                                                              123
5.12.2          De data                                                                                                               124
5.12.3          De uitwerking                                                                                                      124
5.13             BCG Matrix                                                                                                          127
5.13.1          Inleiding                                                                                                              127
5.13.2          De data voor de BCG Matrix                                                                                 127
5.14             Watervalgrafiek met Omhoog-omlaag balken                                                         131
5.14.1          Opgaven                                                                                                              133
5.15             Regelgrafiek                                                                                                         134
5.15.1          Inleiding                                                                                                              134
5.15.2          De data                                                                                                               135
5.15.3          De uitwerking                                                                                                      135
6                 Case draaitabellen met functies                                                                             138
6.1               Inleiding                                                                                                              138
6.2               Opgaven                                                                                                              140
7                 Case hiërarchische lijst                                                                                         141
7.1               Inleiding                                                                                                              141
7.2               Opgaven                                                                                                              142
8                 Case doorlopende kalender                                                                                    143
8.1               Inleiding                                                                                                              143
8.2               Uitwerking                                                                                                           143
9                 Case annuïteitenhypotheek                                                                                    146
9.1               Inleiding                                                                                                              146
9.2               Uitwerking                                                                                                           146
10                Case temperatuurmodel                                                                                         152
10.1             Uitwerking informatieanalyse                                                                                152
10.2             Uitwerking output                                                                                                 152
10.3             Uitwerking invoerblad Excel                                                                                  153
10.4             Blad voor Excel datafilter                                                                                      154
10.5             Selecteer 30-jarige perioden met grafiek                                                                 157
10.6             Een blad om jaren te vergelijken                                                                            158
10.7             Opgaven                                                                                                              158
11                Hyperlinks                                                                                                           160

4       Data-analyse met Excel VBA

Dit materiaal is in de eerste plaats gericht op mensen die met behulp van VBA gegevens willen analyseren. Het accent ligt op het gebruik van een specifiek Excel VBA object dat hiervoor zeer geëigend is. Ik illustreer dit aan den hand van diverse praktijkvoorbeelden.
Duur cursus: 2 à 3 dagen
INHOUDSOPGAVE EXCEL VBA
1                 Inleiding                                                                                                              4
1.1               Motto: wat kan met Excel doen we niet met Excel VBA.                                          4
1.2               Macro's en VBA                                                                                                   4
1.3               Organische aanpak                                                                                               5
2                 Objecten in Excel                                                                                                 6
2.1               Inleiding                                                                                                              6
2.2               Een uitgebreid voorbeeld                                                                                       8
3                 Procedureel programmeren                                                                                    10
3.1               Inleiding                                                                                                              10
3.2               Private, Public en Static                                                                                        10
3.3               Variabelen                                                                                                           10
3.4               IF THEN ELSE                                                                                                       11
3.5               FOR EACH NEXT                                                                                                  11
3.6               FOR NEXT                                                                                                           12
3.7               SELECT CASE                                                                                                       13
3.8               DO UNTIL etc.                                                                                                     14
3.9               Opgaven                                                                                                              14
4                 Excel object uitgelicht: UsedRange                                                                        15
4.1               Inleiding                                                                                                              15
4.2               Toepassingen UsedRange                                                                                      16
4.3               Opgaven                                                                                                              20
5                 (Menu)knoppen en VBA                                                                                        21
5.1               VBA voor knoppen en het verbergen van werkbladen                                               21
5.2               Checkboxen                                                                                                         22
6                 Functions                                                                                                             23
6.1               Inleiding                                                                                                              23
6.2               Van Celsius naar Fahrenheit                                                                                  23
6.3               Controleren van burgerservicenummer                                                                    24
6.4               Opgaven                                                                                                              24
7                 Dialoogvensters: Userforms                                                                                    25
7.1               Inleiding                                                                                                              25
7.2               Opgaven                                                                                                              28
8                 Excel VBA en Access                                                                                            29
8.1               Inleiding                                                                                                              29
8.2               Van Access naar Excel met DAO                                                                           29
8.3               Van Access naar Excel met ADO                                                                           30
8.4               Van Excel naar Access                                                                                          32
9                 Van Excel naar Word                                                                                            33
10                Email vanuit Excel                                                                                                34
10.1             Inleiding                                                                                                              34
10.2             Voorbeeldcode van direct versturen                                                                        34
10.3             Voorbeeldcode van emailen via Outlook                                                                34
11                Excel VBA en bestandsbeheer                                                                                36
11.1             Inleiding                                                                                                              36
11.2             Bestanden inlezen en openen                                                                                36
11.3             Excel bestand wegschrijven naar een tekstbestand                                                   37
11.4             Opgaven                                                                                                              38
12                Foutafhandeling                                                                                                    39
12.1             Inleiding                                                                                                              39
12.2             Algemene VBA-foutafhandelding                                                                           39
13                Oplossingen bij de opgaven                                                                                   40
13.1             Oplossingen van paragraaf 3.9                                                                               40
13.2             Oplossingen van paragraaf 4.3                                                                               41
13.3             Oplossingen van paragraaf 6.4                                                                               43
13.4             Oplossingen van paragraaf 11.3                                                                             45
14                Wat is Visual Basic For Applications                                                                      46
14.1             Achtergrond                                                                                                         46
14.2             VBA is meer dan een macrotaal                                                                             46
15                Basisbegrippen VBA                                                                                              47
15.1             VBA terminologie                                                                                                 47
15.1.1          Algemene termen                                                                                                 47
15.1.2          Bestandstypen                                                                                                      47
15.2             De Visual Basic Editor                                                                                           48
15.3             Objecten                                                                                                             49
15.4             Properties                                                                                                             50
15.4.1          Instellen van properties in Design Time                                                                  50
15.4.2          Instellen van properties in Run Time                                                                       50
15.4.3          Meerdere waarden van properties gelijk instellen                                                    50
15.4.4          Het opvragen van properties in Run Time                                                                51
15.5             Methods                                                                                                               51
15.6             Events                                                                                                                  51
15.7             Overzicht objecten, properties, methods en events                                                  52
15.8             De Object Browser                                                                                               52
16                Het schrijven van code                                                                                         54
16.1             De plaats                                                                                                             54
16.1.1          Document Modules                                                                                               54
16.1.2          UserForm modules                                                                                                54
16.1.3          General Modules                                                                                                  54
16.1.4          Class Modules                                                                                                      55
16.2             De opmaak van code                                                                                            55
16.2.1          De Split Bar                                                                                                         55
16.2.2          Views                                                                                                                  55
16.2.3          Inspringen                                                                                                            55
16.2.4          Commentaar                                                                                                        55
16.2.5          Het Line-Continuation teken                                                                                  56
16.2.6          Het Concatenatie teken                                                                                        56
16.2.7          Opmaak instellingen                                                                                             56
16.2.8          Hulp bij het schrijven van code                                                                             56
17                Variabelen                                                                                                           58
17.1             Wat zijn variabelen                                                                                              58
17.2             Variabelen gebruiken                                                                                            58
17.2.1          Variabelen declareren                                                                                           58
17.2.2          Toekennen van variabelen                                                                                     60
17.2.3          Het declareren en toewijzen van constanten                                                           60
17.3             Het bereik van variabelen                                                                                     61
17.3.1          Local scope                                                                                                          61
17.3.2          Module / UserForm scope                                                                                      61
17.3.3          Public scope                                                                                                         61
17.3.4          Static variabelen                                                                                                  62
17.3.5          Arrays in VBA                                                                                                       62
18                Procedures en functies                                                                                           64
18.1             Inleiding                                                                                                              64
18.2             Event Procedures                                                                                                  64
18.3             General Procedures                                                                                               65
18.3.1          Inleiding                                                                                                              65
18.3.2          Sub Procedures                                                                                                     65
18.3.3          Function Procedures                                                                                              65
18.3.4          Procedures en Functies aanroepen                                                                          66
18.4             Property Procedures                                                                                               66
19                Communiceren met de gebruiker                                                                           67
19.1             Berichten weergeven                                                                                            67
19.2             Vragen stellen                                                                                                      68
20                Besturingsstructuren                                                                                              70
20.1             Vergelijkings- en logische operatoren                                                                     70
20.1.1          Vergelijkingsoperatoren                                                                                         70
20.1.2          Logische operatoren                                                                                              70
20.2             Conditionele structuren                                                                                         70
20.2.1          If…Then                                                                                                               70
20.2.2          If…Then…Else                                                                                                      71
20.2.3          If…Then…ElseIf                                                                                                    71
20.2.4          Select Case                                                                                                          72
20.3             Lus structuren                                                                                                       72
20.3.1          For…Next                                                                                                            73
20.3.2          For…Each                                                                                                            73
20.3.3          Do…Loop While                                                                                                   74
20.3.4          Do…Loop Until                                                                                                    74
20.3.5          Do While…Loop                                                                                                   75
20.3.6          Do Until…Loop                                                                                                    75
20.3.7          Een lus direct verlaten                                                                                           75
21                Foutopsporing en Foutafhandeling                                                                          76
21.1             Inleiding                                                                                                              76
21.2             Foutopsporing                                                                                                       76
21.2.1          De break mode                                                                                                     76
21.2.2          Breakpoints                                                                                                          76
21.2.3          De debug toolbar                                                                                                  76
21.3             Foutafhandeling                                                                                                    77
21.3.1          De stappen                                                                                                           78
21.3.2          Het Err object                                                                                                       79
21.3.3          Een centrale Error Handler                                                                                     79
22                Nuttige Excel VBA links                                                                                        83

5       Access VBA

Dit cursusmateriaal is gericht op mensen die met behulp van Access applicaties willen bouwen en daarbij VBA nodig hebben om de puntjes op de i te zetten. Het staat vol met voorbeelden uit mijn eigen praktijk als Access ontwikkelaar.
Duur cursus: 2 à 3 dagen
I N H O U D S O P G A V E  A C C E S S  V B A
1                 Inleiding Access en VBA                                                                                       6
2                 Kennismaken met algemene modules en objecten                                                   7
2.1               Inleiding                                                                                                              7
2.2               Algemene module                                                                                                7
2.3               Het DOCMD-statement                                                                                         8
2.4               Het objectenmodel (MAO)                                                                                    9
3                 Functies                                                                                                               11
3.1               Inleiding                                                                                                              11
3.2               Standaardfuncties                                                                                                 11
3.3               Een eigen functie maken met één argument                                                            11
3.4               Een eigen functie maken met meer argumenten                                                      13
3.5               Nog enige voorbeelden                                                                                         14
3.6               Extra opdrachten bij Mak.mdb                                                                               15
4                 Gebeurtenisprocedures voor rapporten                                                                    17
4.1               Inleiding                                                                                                              17
4.2               Objecten                                                                                                             17
4.3               Gebeurtenisprocedures                                                                                          17
4.4               Eigenschappen en methoden                                                                                  18
4.5               Enige voorbeelden                                                                                                19
4.6               Extra opdracht                                                                                                      20
5                 Verdere kennismaking met VBA voor Access                                                          21
5.1               Inleiding                                                                                                              21
5.2               Het me.statement                                                                                                 22
5.3               Controls als subobjecten                                                                                        23
6                 Werken met keuzelijsten en tekstvakken                                                                25
6.1               Inleiding                                                                                                              25
6.2               Keuzelijsten met invoervak                                                                                   25
6.3               Tekstvakken                                                                                                         29
7                 Gegevens filteren                                                                                                 31
7.1               Inleiding                                                                                                              31
7.2               Filteren via een selectiefilter: de theorie                                                                 31
7.3               Filteren via groepsvakken en keuzerondjes                                                             32
7.4               Reageren op filtering                                                                                             33
8                 Gegevens valideren                                                                                              35
8.1               Inleiding                                                                                                              35
9                 Gebeurtenisprocedures voor formulieren                                                                 37
9.1               Inleiding                                                                                                              37
9.2               Onbedoelde wijzingen voorkomen                                                                         37
9.3               Onbedoelde toevoegingen voorkomen                                                                    38
9.4               Wijzigingen bevestigen                                                                                         39
9.5               Reageren op het toetsenbord                                                                                  39
10                Werken met formulieren                                                                                        41
10.1             Inleiding                                                                                                              41
10.2             Openen van formulieren                                                                                        41
10.3             Sluiten van formulieren                                                                                         43
10.4             De collectie van open formulieren: Forms                                                               43
10.5             De collectie Controls                                                                                            44
10.6             Werken met subformulieren                                                                                   45
11                Werken met gegevens (DAO of ADO)                                                                    46
11.1             Inleiding                                                                                                              46
11.2             DataAccessObjects (DAO)                                                                                    46
11.2.1          Initialiseren database                                                                                            47
11.2.2          Initialiseren Recordset                                                                                           47
11.2.3          Bladeren door records                                                                                            47
11.2.4          Records en velden tellen                                                                                       47
11.2.5          Velden weergeven                                                                                                47
11.2.6          Positie huidige record                                                                                            48
11.2.7          Records toevoegen                                                                                                48
11.2.8          Record zoeken in tabellen met indexen                                                                  48
11.2.9          Record zoeken in queries                                                                                      48
11.2.10        Huidige record wissen                                                                                           48
11.2.11        Door alle records  lopen                                                                                        49
11.2.12        Door alle velden lopen                                                                                          49
11.2.13        Variabelen afsluiten                                                                                              49
11.2.14        Extra opdrachten                                                                                                   50
11.3             ActiveX DataAccessObjects (ADO)                                                                        53
11.3.1          Initialiseren database                                                                                            54
11.3.2          Initialiseren Recordset                                                                                           55
11.3.3          Bladeren door records                                                                                            56
11.3.4          Records en velden tellen                                                                                       56
11.3.5          Velden weergeven                                                                                                56
11.3.6          Positie huidige record                                                                                            57
11.3.7          Records toevoegen                                                                                                57
11.3.8          Record zoeken in tabellen met indexen                                                                  57
11.3.9          Records zoeken en wissen                                                                                     57
11.3.10        Door alle records 'lopen'                                                                                       57
11.3.11        Door alle velden 'lopen'                                                                                        58
11.3.12        Afsluiten                                                                                                              58
11.3.13        Voorbeeld van Access naar Excel met ADO vanuit Excel                                         59
12                Werken met SQL                                                                                                  60
12.1             Inleiding                                                                                                              60
12.2             Selecteren via SQL                                                                                               60
12.3             Statistiek via SQL                                                                                                 60
12.4             Verwijderen van records via SQL                                                                           61
12.5             Aanpassen van gegevens via SQL                                                                          61
12.6             Toevoegen records via SQL                                                                                   61
12.7             SQL via DAO en VBA                                                                                           61
13                Uitwisseling met andere Officeprogramma's                                                           63
13.1             Inleiding                                                                                                              63
13.2             Access-gegevens opvragen en plaatsen in Excel                                                      63
13.3             Vanuit Access gegevens overbrengen naar Word                                                     64
13.4             Access database splitsen                                                                                       67
14                Foutafhandeling                                                                                                    68
14.1             Inleiding                                                                                                              68
14.2             Algemene VBA-foutafhandelding                                                                           68
14.3             Foutafhandeling Access specifiek                                                                           68
14.4             Opgave bij mak.mdb                                                                                            69
15                Wat is Visual Basic For Applications                                                                      70
15.1             Achtergrond                                                                                                         70
15.2             VBA is meer dan een macrotaal                                                                             70
16                Basisbegrippen VBA                                                                                              71
16.1             VBA terminologie                                                                                                 71
16.1.1          Algemene termen                                                                                                 71
16.1.2          Bestandstypen                                                                                                      71
16.2             De Visual Basic Editor                                                                                           72
16.3             Objecten                                                                                                             72
16.4             Properties                                                                                                             73
16.4.1          Instellen van properties in Design Time                                                                  73
16.4.2          Instellen van properties in Run Time                                                                       74
16.4.3          Meerdere waarden van properties gelijk instellen                                                    74
16.4.4          Het opvragen van properties in Run Time                                                                74
16.5             Methods                                                                                                               75
16.6             Events                                                                                                                  75
16.7             Overzicht objecten, properties, methods en events                                                  75
16.8             De Object Browser                                                                                               76
17                Het schrijven van code                                                                                         77
17.1             De plaats                                                                                                             77
17.1.1          Document Modules                                                                                               77
17.1.2          UserForm modules                                                                                                77
17.1.3          General Modules                                                                                                  77
17.1.4          Class Modules                                                                                                      78
17.2             De opmaak van code                                                                                            78
17.2.1          De Split Bar                                                                                                         78
17.2.2          Views                                                                                                                  78
17.2.3          Inspringen                                                                                                            78
17.2.4          Commentaar                                                                                                        78
17.2.5          Het Line-Continuation teken                                                                                  79
17.2.6          Het Concatenate teken                                                                                         79
17.2.7          Opmaak instellingen                                                                                             79
17.2.8          Hulp bij het schrijven van code                                                                             79
18                Variabelen                                                                                                           81
18.1             Wat zijn variabelen                                                                                              81
18.2             Variabelen gebruiken                                                                                            81
18.2.1          Variabelen declareren                                                                                           81
18.2.2          Toekennen van variabelen                                                                                     82
18.2.3          Het declareren en toewijzen van constanten                                                           83
18.3             Het bereik van variabelen                                                                                     83
18.3.1          Local scope                                                                                                          83
18.3.2          Module / UserForm scope                                                                                      84
18.3.3          Public scope                                                                                                         84
18.3.4          Static variabelen                                                                                                  84
18.3.5          Arrays in VBA                                                                                                       84
19                Procedures en functies                                                                                           86
19.1             Inleiding                                                                                                              86
19.2             Event Procedures                                                                                                  86
19.3             General Procedures                                                                                               87
19.3.1          Inleiding                                                                                                              87
19.3.2          Sub Procedures                                                                                                     87
19.3.3          Function Procedures                                                                                              88
19.3.4          Procedures en Functies aanroepen                                                                          88
19.4             Property Procedures                                                                                               88
20                Communiceren met de gebruiker                                                                           89
20.1             Berichten weergeven                                                                                            89
20.2             Vragen stellen                                                                                                      90
21                Besturingsstructuren                                                                                              92
21.1             Vergelijkings- en logische operatoren                                                                     92
21.1.1          Vergelijkingsoperatoren                                                                                         92
21.1.2          Logische operatoren                                                                                              92
21.2             Conditionele structuren                                                                                         92
21.2.1          If…Then                                                                                                               92
21.2.2          If…Then…Else                                                                                                      93
21.2.3          If…Then…ElseIf                                                                                                    93
21.2.4          Select Case                                                                                                          94
21.3             Lus structuren                                                                                                       95
21.3.1          Inleiding                                                                                                              95
21.3.2          For…Next                                                                                                            95
21.3.3          For…Each                                                                                                            95
21.3.4          Do…Loop While                                                                                                   96
21.3.5          Do…Loop Until                                                                                                    96
21.3.6          Do While…Loop                                                                                                   97
21.3.7          Do Until…Loop                                                                                                    97
21.3.8          Een lus direct verlaten                                                                                           97
22                Foutopsporing en Foutafhandeling                                                                          98
22.1             Inleiding                                                                                                              98
22.2             Foutopsporing                                                                                                       98
22.2.1          De break mode                                                                                                     98
22.2.2          Breakpoints                                                                                                          98
22.2.3          De debug toolbar                                                                                                  98
22.3             Foutafhandeling                                                                                                    99
22.3.1          De stappen                                                                                                           100
22.3.2          Het Err object                                                                                                       101
22.3.3          Een centrale Error Handler                                                                                     101

6       Crystal Reports: van basisgebruik tot geavanceerd

Dit cursusmateriaal is gericht op mensen die Crystal Reports willengebruiken om professionele rapportage te bouwen. Het bevat heel veel voorbeelden en tips en trucs uit mijn eigen praktijk als Crystal Reports ontwikkelaar.
Duur cursus: totaal 4 dagen
I N H O U D S O P G A V E  deel I (1 dag)
1                 Vast stramien                                                                                                       2
2                 Een simpel rapport van begin tot eind                                                                     3
2.1               Simpel rapport                                                                                                      3
2.2               Opgave: Outer join                                                                                               11
3                 Bestaand rapport koppelen aan een andere database                                                13
3.1               Updaten van de koppeling                                                                                     13
3.2               Opgave                                                                                                               14
4                 Een tweede rapport in de vorm van een sjabloon                                                     15
4.1               Inleiding                                                                                                              15
4.2               Printer                                                                                                                  15
4.3               De marges                                                                                                           16
4.4               Pagina-instelling: staand of liggend                                                                        17
4.5               Summary info                                                                                                       18
4.6               Special fields                                                                                                       19
4.7               Plaatje en/of logo                                                                                                 20
4.8               Extra opties                                                                                                          20
4.9               Opgaven                                                                                                              21
5                 Terug naar het stappenplan                                                                                    22
6                 Formula fields                                                                                                      23
6.1               Inleiding                                                                                                              23
6.2               Werkwijze                                                                                                           23
6.3               Losse voorbeelden                                                                                                24
6.4               Opgaven                                                                                                              26
7                 Selecteren van records                                                                                          28
7.1               Select Expert                                                                                                        28
7.2               Filteren op tekst uit een memoveld                                                                        32
7.3               Selectie op groepsresultaten                                                                                  33
7.4               Opgaven                                                                                                              33
8                 Groeperen en sorteren                                                                                           35
8.1               Groeperen                                                                                                            35
8.1.1            Algemeen                                                                                                            35
8.1.2            Groepering veranderen                                                                                          36
8.2               Sorteren                                                                                                               37
8.3               Paginering                                                                                                            37
8.4               Opgaven                                                                                                              39
9                 Totalen, aantallen, gemiddelden etc.                                                                     41
9.1               Voorbeelden                                                                                                         41
9.2               Opgaven                                                                                                              44
10                Relatieschema                                                                                                      46
I N H O U D S O P G A V E  deel II (2 dagen)
1                 Groeperen en sorteren                                                                                           3
1.1               Groeperen                                                                                                            3
1.1.1            Algemeen                                                                                                            3
1.1.2            Customize Group Name Field                                                                                4
1.1.3            Groeperen op datums                                                                                            5
1.1.4            Groepering veranderen                                                                                          6
1.2               Groeperen met Formula Fields                                                                               7
1.3               Groeperen met Specified Order                                                                              8
1.4               Sorteren                                                                                                               11
1.5               Paginering                                                                                                            12
1.6               Opgaven                                                                                                              15
2                 Opmaak: what you get is what is visible!                                                                18
2.1               Simpele opmaak                                                                                                   18
2.2               Uitlijnen en de breedte van meer velden  aanpassen                                               18
2.3               Conditionele opmaak                                                                                            19
2.4               Opgaven                                                                                                              25
3                 Opslaan van een rapport in een ander formaat                                                        30
3.1               Inleiding                                                                                                              30
3.2               Opgaven                                                                                                              32
4                 Eindopdracht: maken van een complex rapport                                                       33
4.1               Inleiding                                                                                                              33
4.2               Informatie-analyse                                                                                                33
4.3               Uitwerking in Crystal Reports                                                                                 34
4.3.1            Nieuw rapport                                                                                                      34
4.3.2            Koppeling naar de database Noordenwind.mdb                                                       35
4.3.3            Tabellen selecteren                                                                                               35
4.3.4            Relaties                                                                                                               36
4.3.5            Formula Fields: berekende velden                                                                          36
4.3.6            Selecties                                                                                                              38
4.3.7            Groeperen van gegevens                                                                                       38
4.3.8            Summaries                                                                                                           40
4.3.9            Grafiek                                                                                                                41
4.3.10          Opmaak                                                                                                              43
4.4               Extra opgaven                                                                                                      45
4.5               Relatieschema                                                                                                      45
5                 Voorbeeldrapport                                                                                                  46
6                 Geavanceerd formule gebruik                                                                                47
6.1               Bijhouden hoogste orderregelsbedrag, datum en klant: if then                                   47
6.2               Streepjes uit telefoonnummer halen: While Do loop                                                47
6.3               Formulas om werkdagen tussen twee data te berekenen                                           48
6.4               Opgaven                                                                                                              49
7                 Parameters                                                                                                           50
7.1               Parameters maken                                                                                                50
7.2               Parameter opnemen in een filter                                                                            53
7.3               Parameters invullen ook anders                                                                              53
7.4               Gekozen parameters tonen                                                                                    53
7.5               Meerdere parameterwaarden tonen: For to loop                                                       54
7.6               Parameter met meerdere keuzes sorteren                                                                54
7.7               Paramater met ranges                                                                                            55
7.8               Cascading parameters (versie XI en hoger)                                                              55
7.9               Opgaven                                                                                                              56
8                 Running totals                                                                                                      59
8.1               Opgaven                                                                                                              61
9                 Kruistabellen                                                                                                        64
9.1               Inleiding                                                                                                              64
9.2               Opgaven                                                                                                              66
I N H O U D S O P G A V E  deel III (1 dag)
1                 Optimaliseren van rapporten: performance                                                              3
1.1               Inleiding                                                                                                              3
1.2               SQL Expression fields                                                                                            3
1.3               Server-based grouping                                                                                           5
1.4               Geïndexeerde velden                                                                                            6
1.5               Volgorde van de relaties                                                                                        7
1.6               Kleinste tabel voorop in link                                                                                  8
1.7               Vermijd gekoppelde subrapporten                                                                          10
1.8               Opgave                                                                                                               10
2                 Grafieken                                                                                                             12
2.1               Opgaven                                                                                                              15
3                 Crystal Reports Templates                                                                                      19
3.1               Opgaven                                                                                                              20
4                 Multipass rapport                                                                                                  22
4.1               Inleiding                                                                                                              22
4.2               Opgaven                                                                                                              24
5                 Reverse engineering van bestaande rapporten                                                         25
5.1               Report definition                                                                                                   25
5.2               Gebruikte koppeling en tabellen                                                                            27
5.3               Links                                                                                                                   28
5.4               Show SQL Query                                                                                                  29
5.5               Filter                                                                                                                   29
5.6               Groene vinkjes bij gebruikte velden en formula fields                                              30
5.7               Rechter muisknop bij Fields, Formula fields en Parameter Fields                               30
5.8               Opgaven                                                                                                              31
6                 Geavanceerd formule gebruik                                                                                34
6.1               Bijhouden hoogste orderregelsbedrag, datum en klant: if then                                   34
6.2               Streepjes uit telefoonnummer halen: While Do loop                                                34
6.3               Formulas om werkdagen tussen twee data te berekenen                                           35
6.4               Opgaven                                                                                                              36
7                 Gebruik van Arrays                                                                                               37
7.1               Inleiding                                                                                                              37
7.2               Voorbeeld: dynamische arrays                                                                               37
7.3               Voorbeeld: Array als filter                                                                                      40
7.4               Opgaven                                                                                                              42
8                 Subrapporten                                                                                                        45
8.1               Hoofdrapport-subrapport                                                                                        45
8.2               Opgaven                                                                                                              47
8.3               Koppelen op basis van Shared variables                                                                  48
8.4               Koppelen op basis van deelsleutels en doorgeven van waarden                                 49
8.5               Opgaven                                                                                                              51
9                 Repository                                                                                                            52
9.1               Custom functions                                                                                                  52
Een reactie plaatsen