Lightning Guide to Databases with Microsoft Access and SQL
Lightning Guide to Databases with Microsoft Access and SQL
A practical guide to Database Design with Microsoft Access and SQL
About the Book
What is this Lightning Guide good for?
The Lightning Guide to Databases with Microsoft Access and SQL is a fast and easy way to design your databases with Microsoft Access and using Structured Query Language (SQL). It is:
- Very practical: based on learning-by-doing using clear database examples.
- Very direct: goes straight to the point with a short and clear explanation of each topic. Each topic is later developed in deeper detail, for those interested in learning more about it.
- Intended for all users: from MS-Access beginners to very experienced users. If you have some programing experience you will make the most out of this guide, and experience with spreadsheets is also useful – but neither are necessary.
- Very broad: covers from the most basic questions about MS-Access, relational databases, and SQL (such as “what is a Table?”), to advanced features such as Forms, writing complex SQL Queries, Query testing, and debugging guidance.
How do you use this Lightning Guide?
This Lightning Guide is not designed to be read linearly like a book, and it is not intended to be read in full (although you can do both). Rather, it is written as a long list of Frequently Asked Questions, where each short section addresses a specific topic. You can just read the section or sections that are useful for you at any given moment.
To easily find the solution to your specific doubt or question, this Lightning Guide is structured in short sections, each attempting to be reasonably self-contained and answering a specific question/problem that you may have. Sections include cross references to other sections, allowing you to follow up on the explanation of topics in other related sections in case you want to dig-in. There is also some redundancy between sections for the sake of self-containment, making it easier to find the desired answer as fast as possible.
I therefore recommend using this Guide by searching for the specific question/problem that you have and going directly to the corresponding section.
What version of Microsoft Access is this Guide for?
Microsoft Access (MS-Access) is a computer program for building, maintaining and using relational databases. The explanation of the user interface and the exercises in this guide are for the 2021 English version of MS-Access 365, which is very similar to MS-Access 2019 and to MS-Access 2016. Besides, many of the concepts explained in this book are version-independent and apply to database design and database concepts themselves, being equally applicable to past and future versions.
What is not in this Guide?
This Lightning Guide explains in a clear, concise, and detailed way almost all MS-Access features and commands, which will allow you to exploit MS-Access in great dept. However, be aware this Guide covers Reports very lightly and does not cover macros.
I would also like to clarify that this guide does not cover how to program in VBA. It explains how to use VBA code in MS-Access, and how VBA data types interact with SQL ones, but it does not provide a course on how to program in VBA. In case that you know some other imperative programming language like C or Java you will find VBA straightforward and will be able to code with it very fast using a few tips from web pages.
About the Author
Table of Contents
- PART A. CREATING MY FIRST DATABASE WITH MS-ACCESS 1
- A.1 How do I use this Lightning Guide? 1
- A.2 What version of MS-Access is this Guide for? 2
- A.3 How do I create my first database? 2
- A.4 How do I write and run my first SQL Query? 5
- A.5 How do I add a Table to my first database? 8
- A.6 How do I write and run my first Select Query with record aggregation? 9
- A.7 How do I configure my Tables in my first database? 11
- A.8 How do I write and run my first Union Query? 16
- A.9 How do I create a Relationship in my first database? 18
- A.10 How do I write and run my first Join Query? 20
- A.11 How do I write and run my first Transform Query? 21
- PART B. BRIEFING ON MS-ACCESS USER INTERFACE 24
- B.1 What options should I set in MS-Access? 24
- B.2 How is the MS-Access window structured? 27
- B.3 What are the Ribbons? 31
- B.4 What is the “Navigation Pane” and how it works? 36
- B.5 What is a Table/Query/Form in “Datasheet View”? 51
- B.6 What is a Table in “Design View”? 60
- B.7 What is a Query in “Design View”? 72
- B.8 What is a Form in “Design View”? 76
- B.9 What is a Query in “SQL View”? 79
- B.10 What is the “Relationships” pane? 80
- B.11 Can I use a drop-down/expression menu even if its icon is not shown? 91
- PART C. CONCEPTS AND INTERNALS OF DATABASES 92
- C.1 What are the main concepts of databases? 92
- C.2 What are objects, names, keywords, data types, constants, variables, operators, functions and expressions? 95
- C.3 What are fields, field value-lists, records and record-lists? 100
- C.4 What are database Tables? 103
- C.5 What is a pointer? 103
- C.6 What is a Null? 104
- C.7 What are duplicate records and duplicate field values? 105
- C.8 What is indexing? 107
- C.9 How do I prevent duplicate field values and duplicate records? 117
- C.10 What are the Table Key(s) and how should I handle them? 120
- C.11 What is a Relationship? 122
- PART D. DESIGNING MY DATABASES WITH MS-ACCESS 133
- D.1 How do I create, close and open a database file? 135
- D.2 How do I carefully assign good names from the very beginning? 137
- D.3 How do I create and design a Table and its fields? 145
- D.4 How do I configure a Table field data type and size? 146
- D.5 How do I configure a Table field validation rule, indexing, and other properties? 157
- D.6 How do I configure the Primary Key field(s) of a Table? 164
- D.7 How do I add simple and/or composite index(es) to a Table? 168
- D.8 How do I configure the properties of a Table? 171
- D.9 How do I create and configure my Table Relationships? 174
- D.10 How do I design MS-Access Forms? 182
- D.11 How do I configure the way to enter data (e.g., a drop-down menu) in a Table/Form field? 188
- D.12 How do I use MS-Access Reports? 200
- D.13 How do I share a database, having multiple concurrent users? 201
- PART E. ENTERING, MODIFYING AND DELETING MY DATABASE DATA 202
- E.1 How do I edit one new or existing record? 203
- E.2 How do I edit the field’s values of the record under edition? 206
- E.3 How do I interactively delete existing records? 215
- E.4 What is different about entering, modifying or deleting records from a Table or a Form? 215
- E.5 How do I copy/cut and paste data between MS-Access and other applications? 216
- E.6 What checks are done when saving a field value, or entering or modifying a record? 223
- E.7 How do I bulk-change my Table/Form’s data? 225
- E.8 How do I upload my pre-existing data into my database? 228
- E.9 Can I get inconsistent results out of my initial data in my database? 229
- E.10 Why should I use “Compact and Repair Database”? 229
- PART F. WRITING SQL QUERIES TO USE MY DATABASE 231
- F.1 What is the Structured Query Language (SQL)? 231
- F.2 What version of SQL is this guide for? 232
- F.3 Why should I write and run Queries? 232
- F.4 What is an SQL operation and an SQL Query? 232
- F.5 How do I edit my SQL Queries with the plug-in “Access SQL Editor”? 235
- F.6 What are the SQL operators I use to write my Queries? 259
- F.7 What is a Select operation and how do I write it? 261
- F.8 What is a Join operation and how do I write it? 314
- F.9 What is a Union operation and how do I write it? 347
- F.10 What is a Transform operation and how do I write it? 354
- F.11 What are the SQL clauses, their expression’s elements and color codes? 382
- F.12 How do I add parameters (type-in variables) to my Queries? 386
- F.13 How do I write a Query that changes my Table data? 388
- F.14 How do I write and debug my SQL Queries? 396
- PART G. WRITING EXPRESSIONS 398
- G.1 What are the main differences between the three expression scopes? 398
- G.2 How do I manage VBA data types and Table field types-sizes? 403
- G.3 What is the data type returned by an expression? 410
- G.4 How do I write a constant? 411
- G.5 How do I use value operators in an expression? 414
- G.6 How do I use functions in an expression? 423
- G.7 What is the evaluation order of an expression? 429
- G.8 How do I use an SQL operation in an expression? 430
- G.9 How are numeric-like values internally represented and processed? 435
- PART H. CUSTOMIZING THE APPEARANCE OF A QUERY/TABLE/FORM IN “DATASHEET VIEW” 439
- H.1 How do I change the column width, or freeze/unfreeze the columns in a Table/Query/Form? 439
- H.2 How do I change row height, hide rows or change row order in a Table/Query/Form? 442
- H.3 How do I change the order of columns that I see in a Table/Query/Form? 445
- H.4 How do I hide/unhide columns in a Table/Query/Form? 447
- H.5 How do I change the column headings in a Table/Query/Form? 450
- H.6 How do I configure the formatting of column values in a Table/Query/Form? 452
- H.7 How do I configure the column text alignment in a Table/Query/Form? 464
- H.8 How do I show aggregate values (e.g., totals) in a Table/Query/Form? 465
- H.9 How do I configure colors, fonts and other features of a Table/Query/Form? 467
- PART I. EVOLVING MY DATABASE DESIGN 469
- I.1 Why would I want to improve/modify my database design? 469
- I.2 Why should I be so careful with any change to the database design? 469
- I.3 How do I find all the dependent objects on a given database object? 472
- I.4 What are the side effects of modifying my Table fields? 472
- I.5 What are the side effects of modifying my Tables? 482
- I.6 What are the side effects of modifying my Queries? 486
- I.7 What are the side effects of modifying my user-defined VBA functions? 493
- I.8 What are the side effects of modifying my Relationships, Forms and/or Reports? 494
- PART J. DEBUGGING MY SQL QUERIES 495
- J.1 How do I fix an error/crash in a test-and-proven Query? 495
- J.2 How do I fix an error/crash in a non-test-and-proven Query? 497
- J.3 How do I debug by commenting/uncommenting? 497
- J.4 How do I debug in progressive steps? 498
- J.5 How do debug inside out? 499
- J.6 How do I debug my same-level code linearly? 499
- J.7 How do I debug the current uncommented SQL operation at each step? 500
- J.8 How do I fix a syntax error that prevents saving a Query? 510
- J.9 How do I fix a crash from a syntax error? 511
- J.10 How do I fix a crash from a run-time error? 517
- J.11 How do I fix defective Query results? 524
- J.12 What do I do when I just cannot fix a Query? 539
- J.13 Why should I always compare the results of an existing Query? 540
- J.14 What Null-related bugs can I get? 542
- J.15 What exception-value bugs can I get? 545
- J.16 What data type bugs can I get? 549
- PART K. USEFUL DESIGN ADVICE 555
- K.1 What are good practices in my Table design? 555
- K.2 What are other good practices in my database design? 566
- K.3 How do I structure and optimize a distributed database? 577
- K.4 What Query design principles should I follow? 591
- K.5 Why and how should I carefully handle Nulls in my Queries? 604
- K.6 What are some useful models of SQL code? 613
- K.7 Why and how do I design a fast database and fast Queries? 642
- K.8 Why should I avoid using Decimal data types? 653
- K.9 How do I write my user-defined VBA functions and database Subroutines? 655
- K.10 What elements/concepts are explained in various places? 663
- PART L. FIXING DATABASE ERRORS 669
- L.1 Why can I get an error/crash in a test-and-proven database? 669
- L.2 How do I fix errors with my Table/Form design? 670
- L.3 How do I fix errors in my Relationship configuration? 679
- L.4 How do I fix errors when entering, modifying or deleting records? 685
- L.5 How do I fix errors in Table/Form data? 696
- L.6 How do I fix a Table/Form that I cannot open? 703
- L.7 How do I fix errors with Short Text or String fields? 705
- L.8 How do I fix errors with the user interface? 712
- PART M. LIST OF BUILT-IN FUNCTIONS 720
- M.1 ActiveX functions 721
- M.2 Application functions 721
- M.3 Array functions 721
- M.4 Conversion functions 721
- M.5 Database functions 722
- M.6 Date and Time functions 722
- M.7 Domain Aggregate functions 722
- M.8 Error Handling functions 723
- M.9 File Input/Output functions 723
- M.10 Financial functions 723
- M.11 Inspection functions 723
- M.12 Mathematical functions 724
- M.13 Message functions 724
- M.14 Miscellaneous functions 724
- M.15 Program Flow functions 725
- M.16 SQL aggregate functions 725
- M.17 File Management functions 725
- M.18 Text Processing functions 725
- PART N. CONTENTS AND ACKNOWLEDGEMENTS I
The Leanpub 60 Day 100% Happiness Guarantee
Within 60 days of purchase you can get a 100% refund on any Leanpub purchase, in two clicks.
Now, this is technically risky for us, since you'll have the book or course files either way. But we're so confident in our products and services, and in our authors and readers, that we're happy to offer a full money back guarantee for everything we sell.
You can only find out how good something is by trying it, and because of our 100% money back guarantee there's literally no risk to do so!
So, there's no reason not to click the Add to Cart button, is there?
See full terms...
Earn $8 on a $10 Purchase, and $16 on a $20 Purchase
We pay 80% royalties on purchases of $7.99 or more, and 80% royalties minus a 50 cent flat fee on purchases between $0.99 and $7.98. You earn $8 on a $10 sale, and $16 on a $20 sale. So, if we sell 5000 non-refunded copies of your book for $20, you'll earn $80,000.
(Yes, some authors have already earned much more than that on Leanpub.)
In fact, authors have earnedover $14 millionwriting, publishing and selling on Leanpub.
Learn more about writing on Leanpub
Free Updates. DRM Free.
If you buy a Leanpub book, you get free updates for as long as the author updates the book! Many authors use Leanpub to publish their books in-progress, while they are writing them. All readers get free updates, regardless of when they bought the book or how much they paid (including free).
Most Leanpub books are available in PDF (for computers) and EPUB (for phones, tablets and Kindle). The formats that a book includes are shown at the top right corner of this page.
Finally, Leanpub books don't have any DRM copy-protection nonsense, so you can easily read them on any supported device.
Learn more about Leanpub's ebook formats and where to read them
