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.
- 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.
See full terms
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), EPUB (for phones and tablets) and MOBI (for 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.
Recipes for DecouplingMatthias Noback
Write software that survives
CQRS by ExampleCarlos Buenosvinos, Christian Soronellas, and Keyvan Akbary
- Leverage your Software Architecture skills by learning everything about CQRS in detail with lots of examples
- Develop faster applications by applying CQRS and fostering Read Models and Projections
- Learn how to apply CQRS into a brownfield project from a pragmatic approach
OpenIntro StatisticsDavid Diez, Christopher Barr, Mine Cetinkaya-Rundel, and OpenIntro
A complete foundation for Statistics, also serving as a foundation for Data Science.
Leanpub revenue supports OpenIntro (US-based nonprofit) so we can provide free desk copies to teachers interested in using OpenIntro Statistics in the classroom and expand the project to support free textbooks in other subjects.
More resources: openintro.org.
Jetpack Compose internalsJorge Castillo
Jetpack Compose is the future of Android UI. Master how it works internally and become a more efficient developer with it. You'll also find it valuable if you are not an Android dev. This book provides all the details to understand how the Compose compiler & runtime work, and how to create a client library using them.
Maîtriser Apache JMeterPhilippe Mouawad, Bruno Demion (Milamber), and Antonio Gomes Rodrigues
Toute la puissance d'Apache JMeter expliquée par ses commiteurs et utilisateurs experts. De l'intégration continue en passant par le Cloud, vous découvrirez comment intégrer JMeter à vos processus "Agile" et Devops.
If you're looking for the newer english version of this book, go to Master JMeter : From load testing to DevOps
Ansible for DevOpsJeff Geerling
Ansible is a simple, but powerful, server and configuration management tool. Learn to use Ansible effectively, whether you manage one server—or thousands.
C++20 - The Complete GuideNicolai M. Josuttis
All new language and library features of C++20 (for those who know previous C++ versions).
The book presents all new language and library features of C++20. Learn how this impacts day-to-day programming, to benefit in practice, to combine new features, and to avoid all new traps.
Buy early, pay less, free updates.
Mastering STM32 - Second EditionCarmine Noviello
With more than 1200 microcontrollers, STM32 is probably the most complete ARM Cortex-M platform on the market. This book aims to be the most complete guide around introducing the reader to this exciting MCU portfolio from ST Microelectronics and its official CubeHAL and STM32CubeIDE development environment.
The PowerShell Scripting and Toolmaking BookDon Jones and Jeff Hicks
Learn the patterns, practices, and details of PowerShell Scripting and Toolmaking from the industry's two most recognized experts on the subject.
Introduction to Data ScienceRafael A Irizarry
The demand for skilled data science practitioners in industry, academia, and government is rapidly growing. This book introduces concepts from probability, statistical inference, linear regression and machine learning and R programming skills. Throughout the book we demonstrate how these can help you tackle real-world data analysis challenges.
2 Books"Software Architecture for Developers" is a practical and pragmatic guide to modern, lightweight software architecture, specifically aimed at developers. You'll learn:The essence of software architecture.Why the software architecture role should include coding, coaching and collaboration.The things that you really need to think about before...#1
2 Books"Software Architecture for Developers" is a practical and pragmatic guide to modern, lightweight software architecture, specifically aimed at developers. You'll learn:The essence of software architecture.Why the software architecture role should include coding, coaching and collaboration.The things that you really need to think about before...
CCIE Service Provider Ultimate Study Bundle
2 BooksPiotr Jablonski, Lukasz Bromirski, and Nick Russo have joined forces to deliver the only CCIE Service Provider training resource you'll ever need. This bundle contains a detailed and challenging collection of workbook labs, plus an extensively detailed technical reference guide. All of us have earned the CCIE Service Provider certification...#2
CCIE Service Provider Ultimate Study Bundle
2 BooksPiotr Jablonski, Lukasz Bromirski, and Nick Russo have joined forces to deliver the only CCIE Service Provider training resource you'll ever need. This bundle contains a detailed and challenging collection of workbook labs, plus an extensively detailed technical reference guide. All of us have earned the CCIE Service Provider certification...
Pattern-Oriented Memory Forensics and Malware Detection
2 BooksThis training bundle for security engineers and researchers, malware and memory forensics analysts includes two accelerated training courses for Windows memory dump analysis using WinDbg. It is also useful for technical support and escalation engineers who analyze memory dumps from complex software environments and need to check for possible...#4
Pattern-Oriented Memory Forensics and Malware Detection
2 BooksThis training bundle for security engineers and researchers, malware and memory forensics analysts includes two accelerated training courses for Windows memory dump analysis using WinDbg. It is also useful for technical support and escalation engineers who analyze memory dumps from complex software environments and need to check for possible...
Architecture and Decoupling
2 BooksThis bundle combines two of the most recent books by Matthias Noback, which together offer some serious material that will definitely level up your skills in web application development with a focus on long term maintainability, testing, and domain-driven design.
Effective Kotlin bundle
2 BooksThe package of the most essential books for Kotlin developers, to become great developers. Effective Kotlin and Kotlin Coroutines by Marcin Moskała are both famous positions, well known and commonly recommended.
Cisco CCNA 200-301 Complet
4 BooksCe lot comprend les quatre volumes du guide préparation à l'examen de certification Cisco CCNA 200-301.
All the Books of The Medical Futurist
6 BooksWe put together the most popular books from The Medical Futurist to provide a clear picture about the major trends shaping the future of medicine and healthcare. Digital health technologies, artificial intelligence, the future of 20 medical specialties, big pharma, data privacy, digital health investments and how technology giants such as Amazon...#10
All the Books of The Medical Futurist
6 BooksWe put together the most popular books from The Medical Futurist to provide a clear picture about the major trends shaping the future of medicine and healthcare. Digital health technologies, artificial intelligence, the future of 20 medical specialties, big pharma, data privacy, digital health investments and how technology giants such as Amazon...