Current Microsoft MVP Awardees

MS Access™ MVP Awardee from July, 2007 through June, 2019


GPC's YouTube Training Video Library

Formatting Dates for Display in MS Access
Use MS Access Format() expressions to display dates and dates with times to your users in the interface.
Video Run Time: 08:24

So You Want to Build a Microsoft Acccess Database
Introduction to the underlying considerations on which a good Relational Database Application must be based
Video Run Time: 15:16

Creating Your First Table in a Microsoft Access Relational Database
Step by Step Process of Creating a table in an Access Relational Database. What fields need to be included and why.
Video Run Time: 27:51

Creating Your Second table in a Microsoft Access Relational Data
Adding a related table to your MS Access Relational Database requires careful thought. Properly defined Primary and Foreign Keys make the second table work with the first.
Video Run Time: 30:43

Reconciling Business Rules and Normalization
Normalization, or or the rules for creating a set of relational tables, is only part of the process. You also need to reconcile good table design with the business requirements that govern your database application. There may be more than one way to go, and determining which way will best support the business rules is a big part of doing a good job.
Video Run Time: 23:49

One-to-Many or Many-to-Many? Part I
Microsft Access Relational Databases capture information about relationships between two or more entities: People and Email Addresses, Classes and Students, Invoices and Line Items on the Invoices, Meals and Food Items in the Meals. Some of those relationships are one-to-many. Some of those relationships are many-to-many. In this video, you'll learn about both types, and you'll see how to create appropriate related tables for each type
Video Run Time: 23:48

One-to-Many or Many-to-Many -- and Why Part II
Microsft Access Relational Databases capture information about relationships between two or more entities: People and Email Addresses, Classes and Students, Invoices and Line Items on the Invoices, Meals and Food Items in the Meals. Some of those relationships are one-to-many. Some of those relationships are many-to-many. In this video, you'll learn about both types, and you'll see how to create appropriate related tables for each type
Video Run Time: 27:40

Relational Tables -- Summary and Review
A solid, stable, relational database exhibits several qualities that depend on a good foundation of sound table design.
Video Run Time: 21:18

Building Relational Tables -- Adding TransactionTables I
Adding Tables to an MS Access Relational Database that capture information about Transactions
Video Run Time: 22:56

Date Logic in Report Grouping - I
Learn to use DatePart(), DateAdd() and DateValue() to create summary reports with grouped details. Selecting records relies on TempVars in the WHERE Clause in queries and Iif() to filter on "ALL" or "One" unique ID.
Video Run Time: 13:51

Date Logic in Report Grouping -- II
Learn to use DatePart(), DateAdd() and DateValue() to create summary reports with grouped details. Selecting records relies on TempVars in the WHERE Clause in queries and Iif() to filter on "ALL" or "One" unique ID.
Video Run Time: 18:19

Calculated Values in Queries and on Forms
Learn to use Expressions to calculate values for display on Forms. Calculated values can also be used for sorting and filtering in queries.
Video Run Time: 18:49

Performance Analysis Using Date Logic and Conditional Formatting
Performance Analysis Reporting in Access Using Date Logic and Conditional Formatting to Highlight Results
Video Run Time: 20:12

Date and Time Gotchas in Access Reports and Queries
Two ways in which unwary Access users can create misleading or inappropriate reports because of failure to account for how Access actually stores dates with times.
Video Run Time: 20:21

Date Formatting and Filtering in Access Reports Filter Criteria
Using tempvars to set criteria for date range in an Access Report
Video Run Time: 18:50

Default Dates and Date Formatting & Grouping in Access Reports
How to Use Default values for dates, grouping and sorting on Formatted Dates
Video Run Time: 17:02

Date Formatting for Access Form Filters
Using Custom Date Formats and Date Logic to filter subforms and to improve appearance of the data displayed.
Video Run Time: 25:39

Date Formatting in Access Forms
Introduction to Display Formatting for Access Dates and Times in Forms How to use custom formatting to improve appearance and conserve space on a crowded form.
Video Run Time: 14:07

Date Formatting In Access Tables I
MS Access stores dates internally as numbers. It's up to the developer to format those dates properly for display.
Video Run Time: 14:37

Date Formatting in Access Tables II
The impact of Display Formats on Date/Time Fields In Access
Video Run Time: 10:00

Date Formatting in Access Tables III
A closer look at the display possibilities available for date/time fields in tables
Video Run Time: 17:13

Editing Office Ribbons with a Third Party tool
The steps required to modify a Custom Access ribbon using the IDBE Ribbon Creator Tool
Video Run Time: 15:16

Singing Cowboys
How to manage a Many-to-Many Relationship using a main form/subform design. Singing Cowboys is a Web Application developed with MS Access 2010 and published to SharePoint 2010.
Video Run Time: 5:00

GPC's Fully Functional Sample Access Database Applications

"Contains" Search in Multiple Fields

*New -- May, 10, 2020

A frequent request in Access forums is a search function that works like the "contains" searches common in web sites. Instead of an exact match, or a match beginning with the first letter of a word, a "contains" search returns all instances of a field where the search term occurs anywhere in the string.

For example, a "contains" search for "gus" would return names containing those three letters: First names like "Augustus" or "Gustov" as well as Last names like "Anthrogus" and so on.

I achieve this result by using TempVars to filter on multiple fields. This demo shows you how to implement the query and set the tempvars.

*This version supports both 32 bit and 64 bit Access.*

------------------------------------------------------------------------------------

Animated GIF for "Please Wait" Message

*New -- May, 12, 2020

Sometimes I learn something while trying to answer questions on an online Access forum. This was one such occasion. I really appreciate the poster's persistence in pushing me to a solution.

The original request was to display an animated GIF on a form in Access. My first thought was, unfortunately, "That's a bad idea." However, after an exchange of ideas with the original poster, I realized this could be a pretty good idea and one worth pursuing. The result is this demo.

An animated GIF can be displayed in a web browswer control. That means the image file must be stored in a location to which all copies of the Access Front End accdb have permissions. Or copies of it can be saved to the same folder where that Access FE accdb resides on each user's computer. Either way, the animated GIF can be displayed at the beginning of a long-running process and hidden again when that process completes.

I will leave it to you to try out other approaches to implementing this feature, such as dynamically loading different animated GIFs for different processes, or changing the size and location of the web browser control to maximize use of form spacing.

*This version supports both 32 bit and 64 bit Access.*

------------------------------------------------------------------------------------

Calls & Meetings--Contact Manager Database

* Updated -- April 14 2020

*Compatible with 32 bit and 64 bit Access

Contact Manager is a completely revised and updated version of an application I built for a client several years ago. It tracks:

     Companies with whom you have contacts.
     Individual Contacts at those companies, with contact information.
     Calls to your contacts.
     Meetings with your contacts.

In this version, many outdated methods were replaced with more flexible methods, primarily based on TempVars and IIF() functions to eliminate the need for dynamic sql or hard-coded forms references. Also, some unnecessary restrictions on data entry were modified or removed.

*This version supports both 32 bit and 64 bit Access.*

Reports were improved and simplified.

You might use this basic version as a "starter" for your own purposes. Like all of my demos, it can be expanded. Feel free to make any changes needed to suit your situation. However, note that I can't be responsible for any use you make of the application.

------------------------------------------------------------------------------------

Cancel Report on No Data without Error Message

*New --May 2, 2020
Validated for 64 bit version of MS Access --May 4, 2020

When a report opens without data, you can use the On No Data event to cancel the report and issue custom messages or responses.

However that cancellation will send error 2501 back to the event which called the "Report Open" event. That means you must also handle that returned error from the calling procedure in the calling form as well.

This demo illustrates how to handle it.

------------------------------------------------------------------------------------

Create a Membership Directory with images of each person

*New -- October, 2019
Including images in forms and reports in Access requires very little design and programming beyond adding image control(s) and binding them to the appropriate image. Although Access does support storing images internally, that causes file bloat. Therefore, storing images externally and linking to them is highly recommended. This fully functional sample accdb, based on one of Microsoft's templates, illustrates adding images to forms and reports.

------------------------------------------------------------------------------------

Dish Antenna Installations Database Installations

* Updated -- August 9, 2012

Created to staunch the paperwork flood in an Dish Network Installer's business, Installations is a fully functional MS Access database in the accdb format. Try it out, "as is" on your own PC for free. It was custom-built, so it will not be of direct use to you without some modifications to fit your business rules. The instructions are included.

To try it out, create a folder for it on your hard-drive and save the compressed file (installations.zip) to that folder. Double-click on the "ZIP" file to open and extract the files into that folder. Double-click on "Installations.mdb" to launch the 2003 version of the application, or on "Installations.accdb" to launch the 2010 version.

Please request a quote . If you'd like an estimate on a customized version of this tool for your organization.

The ZIP file contains Access 2003 and 2010-2016 versions.

------------------------------------------------------------------------------------

GPC's Customized Northwind Sample Database

*New -- February 2, 2016

I have often recommended the Northwind Access database as a good way to get started with table design based on valid normalization. It is still a good example. It's probably the most complete example of a retail sales application I've seen. Unfortunately, it also has undesirable design features that make it less than ideal as a "starter" for new developers.

That's always bothered me, so recently I took a closer look. It's more complicated than I had thought. It's not good or bad, just complicated. For a beginner, looking for ways to get started, that's a hurdle.

  • Object names in Northwind usually contain spaces and non-standard characters This was my main concern, but it's worse when you read through the entire database and realize how intricate the VBA and macros have to be to handle those names.
  • Many Primary Keys retain the default name "ID", which Access assigns to all newly created Autonumbers. This makes it really hard to track which ID came from which table in the many macros, procedures, queries, forms and reports.
    Of course, if all of the references were fully qualified (e.g. "Orders.ID" instead of "ID"), then it would be less of a hassle. But we're talking about a sample database aimed, one assumes, mostly at new developers and power users not already expert at figuring this stuff out. If I have to work at it, how must they feel?
  • The code in Northwind is a combination of Macros--mostly embedded in forms, but also free-standing--standard VBA, and classes.
    Not that many new Access users are comfortable with classing, even though it's a powerful tool.
    To my way of thinking, this mixed mode approach to coding puts the Northwind sample database firmly on the wrong side of a line dividing "introductory" databases from "professional" database templates.

Because of these characteristics, customizing the Northwind sample to fit the needs of a different organization is anything but a simple task. And that is it's biggest drawback.

So, my customized version is a first step towards making it more accessible to non-pros. I changed all object names to the more conventional standard of having no spaces and no special characters in them.
I renamed all Primary Keys from "ID" to include the name of the table, e.g. "OrderID". This simple step should help new Access users follow the trail more easily. I know that more experienced users won't benefit so much, but this is not aimed at them, only the newbies, anyway.

Down the road, I'll tackle the code. My intention is to convert macros to VBA where it makes sense to do so. There are places where they do make sense.
Unfortunately, the power of the classing approach is too important, in my mind, to eliminate it, so it stays at least in this version and the next. We'll see after that.

If you decide to download and check out my revision, please let me know if you find any bugs in it.
Thanks.

------------------------------------------------------------------------------------

GPC's Work Tracking Demo

*Updated -- October 29, 2016

Download the full, working accdb or mdb version of GPC's Work Tracking Demo, with the VBA that makes it work. I originally built this tool for myself, to track time and efforts on projects for my clients. Over time I expanded it to include some rudimentary Project Management features,such as defining the deliverables for a project, scheduling major milestones, and prioritizing tasks. It's a little more than a timesheet and a little less than a project management tool. You may either use Work Tracking as-is or modify it to suit your needs.The current backend DB for my working version in on SQL Azure, but this one still works well as a starter.

NOTE: The "accdb" version is tested and works in Access 2007 through Access 2016, both 32 bit and 64 bit version.

I recent added code to track and record all changes to data in this version.

It creates an audit of data changes by user who made the change and the date and time on which the change was made.

Please report any bugs in this newest version, so I can fix them.

The change history function is NOT included in the mdb version.

Like all of my samples and demos, this one is offered "As Is" with no guarantees of fitness for any particular purpose. Use it or modify at your own risk and with your own effort.

This ZIP file contains both mdb (tested in A2003) and accdb (tested in A2007 through A2016 and A2016 64 bit) versions. Only the accdb version contains the Change History Function.

------------------------------------------------------------------------------------

GPC Curated Links to Normalization Articles
I believe it's important that you understand what relational database applications are and how they work. The most important principal is that Relational Databases are NOT just super-charged spreadsheets. While there are many good references on the web to choose from, I've limited the list to these favorites.

Also check out Links to Other Access Resources page

Autonumbers - What They Are / Are Not

*Updated -- April 24, 2004
Autonumbers White Paper at Utter Access

------------------------------------------------------------------------------------

Crystal Long's ACCESS Basics for Programming

*New -- July 21, 2006
ACCESS Basics for Programming at Utter Access

------------------------------------------------------------------------------------

Danny Seager's FAQ on Reserved Words

*Updated -- September 11, 2004
Danny Seager's FAQ on Reserved Words at Utter Access

------------------------------------------------------------------------------------

Database planning tutorial Part I

*New -- September 28, 2003
The late Glenn Lloyd's Tutorial at Utter Access

------------------------------------------------------------------------------------

Database planning tutorial Part II

*New -- October 25, 2003
The late Glenn Lloyd's Tutorial at Utter Access

------------------------------------------------------------------------------------

Glenn Lloyd's FAQ on Design Strategies

*New -- October 25, 2003
The late Glenn Lloyd's FAQ at Utter Access

------------------------------------------------------------------------------------

Grover Park George's PDF on Normalization

*Updated -- May 18, 2019
A practical, non-technical explanation of Normalization for Relational Database Applications.

------------------------------------------------------------------------------------

Jerry Dennison's FAQ on Normalization

*New -- April 8, 2003
Jerry Dennison's FAQ at Utter Access

------------------------------------------------------------------------------------

MSKB 283878: Description of the database normalization basics

*Updated -- May 10, 2017
Microsoft Article on Relational Database Normalization basics.

------------------------------------------------------------------------------------

Noah's FAQ on Normalization

*New -- December 8, 2003
Noah's FAQ at Utter Access

------------------------------------------------------------------------------------

Presentation and sample db for Seattle Access User Group

*New -- August 9, 2012
My Seattle Access User Group Presentation and sample db from August, 2012.

------------------------------------------------------------------------------------

Primary Keys - Natural vs Surrogate

*New -- February 13, 2005
Primary Keys - Natural vs Surrogate at Utter Access

------------------------------------------------------------------------------------

Put Stuff Where It Belongs

*New -- November 12, 2013
Jack Leach's Whitepaper on Table Design

------------------------------------------------------------------------------------

Roger Carlson's Access Blog

*New -- March 7, 2011
Roger Carlson's Access Blog

------------------------------------------------------------------------------------

©Copyright 2010-2020 Grover Park Consulting. All Rights Reserved.
Free Resources and Full Working Demos for Microsoft Access Database Design and Development