Lightning Guide to Databases with Microsoft Access and SQL
Minimum price
Suggested price

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.

  • Share this book

  • Categories

    • Databases
    • MS_Access
  • Feedback

About the Author

Arturo Azcorra
Arturo Azcorra

I am an Engineer and Information Technology professional with 30 years of educational and research experience with multiple contributions to the field.

I have wide experience in MS-Access (both as a user and as a database designer), in designing databases and in developing queries with SQL.

I am also a university professor (PhD in formal languages) truly passionate about databases (in spite of my main area of expertise being computer networks) with a deep knowledge in educational writing, going straight to the point in a clear and concise way to effectively transmit ideas and concepts. You can see my wikipedia page.

Table of Contents

  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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

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

80% Royalties. Earn $16 on a $20 book.

We pay 80% royalties. That's not a typo: you earn $16 on a $20 sale. If we sell 5000 non-refunded copies of your book or course for $20, you'll earn $80,000.

(Yes, some authors have already earned much more than that on Leanpub.)

In fact, authors have earnedover $12 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

Write and Publish on Leanpub

You can use Leanpub to easily write, publish and sell in-progress and completed ebooks and online courses!

Leanpub is a powerful platform for serious authors, combining a simple, elegant writing and publishing workflow with a store focused on selling in-progress ebooks.

Leanpub is a magical typewriter for authors: just write in plain text, and to publish your ebook, just click a button. (Or, if you are producing your ebook your own way, you can even upload your own PDF and/or EPUB files and then publish with one click!) It really is that easy.

Learn more about writing on Leanpub