Category: Database Design

Filtering Combo Boxes in Microsoft Access

February 1, 2022November 4, 2023 Andrew Comeau

Combo boxes are extremely useful in Microsoft Access and other design environments. They provide a compact control from which your users can access a drop-down menu that enables them to choose from a variety of options. In my applications, combo boxes often pull their rows from lookup tables such as product or customer lists and…

Database Design, Microsoft Access

Customizing Reports in Microsoft Access

May 25, 2021November 4, 2023 Andrew Comeau

In my latest video, I go into more detail about Microsoft Access report settings. This video is also an addition to my tutorial series, Managing Your Data with Microsoft Access which is now free on YouTube. Microsoft Access has always been known for having a great report designer with which you can put together professional…

Database Design, Microsoft Access

Online Course – Managing Your Data with Microsoft Access

March 17, 2020January 4, 2023 Andrew Comeau

One of the things I hear about Microsoft Access is that it’s simply too complicated for many people to work with. This is because Access is designed for building database applications that can be used to automate and manage processes. Too few resources approach it from that perspective. This leaves users and students without the…

Database Design, Microsoft Access

Deploying MS-Access Table Design Changes to Remote Files

August 7, 2019March 29, 2024 Andrew Comeau

A New Challenge for Access

I designed a Microsoft Access database for one of my clients and it’s split into two files – a front-end file with all the forms, reports and code and a back-end file with the tables. That’s pretty standard.

During the development process, I needed to make adjustments to the table design – new indexes, fields need to be added and removed, relationships changed. That’s usually no problem if I have remote access to the client’s system and can login and make the changes. In this case, I don’t. Even to deploy a new program version, I have to send it to the client and let them unpack the files to their desired locations.

It’s not the ideal situation but it’s what I have to work with. When it comes to making design changes in the back-end tables, I could try to guide the client rep through the process but I’m not risking that. I’d rather have a routine that will automate it and ensure that everything is done as painlessly as possible.

Articles, Database Design, Microsoft Access, Programming

How to Perform a Basic MySQL Installation in Windows

January 25, 2019February 18, 2023 Andrew Comeau

One of the nice features of MySQL as a database software is its noinstall option in which you download the files themselves and perform a manual installation that takes just under a gigabyte of space on your drive. If you’re a developer or a student, this means you can quickly create a new self-contained instance wherever you need it and then blow it away if you need to without cluttering up your registry.

This does mean working on the command line so you’ll want to have a certain comfort level with that. You will also need to be working with Administrative privileges.

Database Design, SQL

What is Referential Integrity?

November 18, 2018March 29, 2024 Andrew Comeau

One of the fundamental differences between an Excel spreadsheet and a Microsoft Access or SQL Server database is the database’s ability to group data by subject into tables and create links between that information. This type of organization enables the user to store large amounts of data and retrieve specific information quickly by writing queries that will search on the various fields. Referential Integrity is actually a simple but essential concept to understanding how databases like Access or SQL Server work. Once you understand it, you will be able to use these tools to better organize and report on your data.

To demonstrate this concept, I’m going to use a few tables in a Microsoft Access database. Access is a great environment to start learning about databases as it provides a user-friendly interface and is widely available through Office365.

Watch the companion video for this article on YouTube or continue article below …

Database Design, Microsoft Access

Performing a Basic Install of MySQL or MariaDB in Windows

May 21, 2017February 18, 2023 Andrew Comeau

One of the great things about MySQL and MariaDB are their versatility of installation. They can be setup as a Windows services and trimmed out with some extra utilities or it can be installed on a flash drive as a portable server.

The easiest installation is certainly the Windows Installer with a wizard for MySQL or the MariaDB MSI that will guide you through the installation of the server and all the options you want. Myself, I still like self-contained programs that won’t interfere with anything else on my system and can be easily removed without leaving hidden traces. I also don’t like adding a lot of stuff to my Windows startup.

If you have a basic comfort with using the Windows command line and control panel, doing a minimal installation of either MariaDB or MySQL is not difficult once you understand a few steps. The MySQL full documentation and the MariaDB Knowedgebase are available for reference on their respective sites but here are the essentials to get you started.

Database Design, How-to

Calculated Fields in CiviCRM

June 14, 2016December 27, 2022 Andrew Comeau 2 Comments

One of CiviCRM’s strengths is the ability to add custom fields to hold specific information about your contacts. One thing it doesn’t offer (yet) is a calculated field type that will present the results of calculations of other fields. While calculated fields are generally discouraged in relational database design, they are sometimes necessary within a user interface. One suggested method is to add custom code hooks within CiviCRM’s PHP code but as a database guy, I decided on a back-end solution.

Database Design, Programming, SQL

MySQL User Defined Function Example: Data Cleanup

May 23, 2016February 14, 2023 Andrew Comeau

(Updated June 9, 2016)

The Challenge

One of my current projects involves migrating a large amount of data away from an old custom Borland Paradox application into a new CiviCRM system. As with too many quickly-constructed apps, this old Paradox database wasn’t especially well designed and, among other the other challenges in salvaging the data, there were no restrictions on how dates could be entered. This means that, in multiple fields within each of the 20,000+ database records, I might see any of the following:

03-14-96
5/20/1975
10/02/83
09051975
07.19.1995
19830211121500

While CiviCRM does have a utility for importing data from CSV and other SQL tables, it was having quite a time with this collection and many of the dates were being mishandled. That’s if the records weren’t rejected entirely for other reasons. Data migration doesn’t often happen with just a few settings adjustments and a click of the Import button.

Database Design, Programming

Installing MySQL on Mac OS X El Capitan

February 23, 2016February 18, 2023 Andrew Comeau

A few months ago, I wrote about how to install MySQL on Windows with IIS. Now I want to show you how to create an AMP web programming stack (Apache, MySQL & PHP) on Mac OS X. As with Windows, you could just go with a ready-made development environment like MAMP but, if you’re going to be doing real database and web design work with MySQL, it’s best to know how everything works.

Background

For this demonstration, I’m going to be using Mac OS X El Capitan (v.10.11), the latest version as of this writing. My installation environment is a hosted Mac server with 1 GB of RAM and 40 GB of disk space. I will be demonstrating the process using the Terminal commands.

Unlike Windows, OS X already includes the Apache web server and PHP language pre-installed so it’s a much simpler process to ensure that everything is working together. You will need root access in order to install and configure the components.

Database Design, Software