In this article we'll go through the process of creating a simple database in Microsoft Access. The idea being that this will be a gentle introduction to the world of relational databases and using Microsoft Access. You'll learn about table design, creating a simple form, query, and report. There is some brief discussion about data normalization. The idea is to just give you a small taste without overwhelming you.

Tables

In Excel, you can have multiple worksheets. You can have a list of your pets with their info and balance your check book all on one worksheet should you wish to do so. So, you can have your "data" and "data manipulation" (i.e. calculations) all in one place.

In Access, things are done a little differently. OK, they are done VERY differently! To use an Excel analogy, think of a "table" in Access as a worksheet. Like a worksheet, a table stores data. Unlike a worksheet, a table only stores data and does not perform any calculations. Each row in a table is referred to as a "record" in a table. Each column in a table is a "field".

To create a table you must first figure out what data you want to store. Let's say you want to create a database to keep track of CDs you own.

What data to you want to store? Album Title and Artist are probably the first two that jump to mind. Now, let's get creative and add two more - Year Released, Songs, and Genre. That should be enough for now.

In Excel, you would most probably create a list with the 5 items we've come up with as headers. For example;

So, all we need to do now is two create a table that mimics this in access and we are done, right? I wish it were that simple. The fact is, to properly utilize Access we must have at least four tables.

One table each for

• Artist
• Album Info - This will contain the Title and Year Released data
• Song list
• Genre

Normalizing (Very Basic)

(Why on earth are we doing this (i.e. having so many tables) ? Doesn't that make it harder to manipulate the data?)

What we are trying to accomplish here is to store repeated and related groups of data together in individual tables in order to reduce redundancy. Now, you could store data in a table this way;

But you've now repeated the artist twice. In doing so, you are using more data storage space for the one artist. So we want to avoid that. Doing this allows us to "normalize" our database. Normalization helps to avoid errors when entering data and actually helps manipulation of data!

Don't worry about understanding normalization just yet. We will get to that soon enough.

In figuring out your table structure, you aren't going to be worried as of yet about entering the actual data. To use the Excel list analogy, you want to figure out what your headings in a list are, i.e. what "fields" are going to be stored in a database. Each field in an Access table corresponds to a column in the Excel List.

One field that should appear in all tables is a 'Primary Key'. It serves the purpose of uniquely identifying a row in a table. Sometimes it may be a data field, e.g. order number. But most of the time you'll be using a Autonumber field because it's created by Access and is always unique in the table. It is also meaningless, which is a good thing. Use something meaningful if you know it will be unique for the table and will always be entered (i.e. not missing sometimes). For a naming convention, I've adopted adding table name (singular version minus tbl) + PK (for Primary Key).

[NOTE: See additional links for some arguments on singular vs plural naming of tables. Either way you go, best to be consistent.]

So, what we are striving for are tables for each 'entity' in our database; i.e. things that stand alone but may be related to one another. In this case: Artist, Album, Song, Genre. For example,

• An artist is a person/group who may have many albums.
• An album can have multiple songs on it (unless it's a one song single or EP).
• An genre may have multiple albums that fall under it.

So, here are the four tables and their respective fields as they should be created in Access. It is important to note that tables should be named starting with tbl (for table) and shouldn't contain any spaces or special characters (e.g. #, &, $, and %). You can use the underscore character to separate words if you wanted to, e.g. Artist_Name.

1. tblArtists
   • ArtistPK
   • ArtistName
2. tblAlbums
   • AlbumPK
   • AlbumTitle
   • YearReleased
3. tblSongs
   • SongPK
   • SongTitle
4. tblGenres
   • GenrePK
   • Genre

For example data in tblAlbum would look like the following table.

Creating the tables

Create with a blank Access database. Then using the menu/ribbon, select Create --> Table Design.



The add your field names and data types.

We'll be using four data types:

1. Autonumber: Whole number created by Access that increments automatically
2. Short Test: Text limited to 255 characters
3. Number (Long Integer): There are several types of number types, but we'll focus on the long integer for now.

Also be sure to fill in the caption with what the field is, e.g. ArtistName --> Caption = 'Artist Name'. It'll be used as captions and headers when looking at the data, but you don't want to use spaces in field names. Spaces in field names make queries and coding more difficult, so best to get used to not including them. Trust me, you'll thank me for the advice down the line.

I'll leave it to you to add the other tables yourself. [NOTE: At this point, you may want to download the sample database to follow along or use to check your own work. Although it may not make sense until you've read through the whole article first.]

(Wow! That's it?! Access can recreate our original Excel list from just those four tables! Excellent!)

Well, not quite...

So far, all we've done is to create the tables to store the data.

(WHAT? More tables?)

Yup... I know that this is a lot to swallow but you're doing good to have read along this far. Have faith!

Our next step is to understand and define the relationships and implement them via some extra tables. Once we've done that, we will create "forms" to enter our data and use "queries" to "join" our data back again or manipulate it as we want to.

Relationships

Relationships are the foundation of a database. The are the basis of our table design, which sets up how (and what) we can do with queries, forms, and reports. So, it's important that we understand the type of relationships we have and

One-to-Many

An example of a one-to-many I gave earlier was 'An album is a person/group who may have many albums'; this is an example of a one-to-many relationship.

(What about one-to-one relationship?)

Good question! Those exist, but are not common so I won't discuss them here. (Personally, I've never had cause to use them.)

The way to implement this is to add the a foreign key to the many side of the table. The foreign key is the primary key from the one side of the relationsip.

• tblArtist (one side)
• ArtistPK
• ArtistName
• tblAlbum (many side)
• AlbumPK
• AlbumTitle
• ArtistFK

Note that I changed the name from AlbumPK (PK for primary key) to AlbumFK (FK for foreign key), this makes it easier to distinguish between primary keys and foreign keys (which will come in handy later when we start working on queries).

To add the foreign key, first select 'Lookup Wizard...' under Data Type (No need to name it first, we'll do that later).


The select 'I want the lookup field to get the values from another table or query.". Click 'Next' to continue.


Then select 'tblArtists" under 'Tables'. Click 'Next' to continue.


Add the 'available fields' to 'selected fields'. Click 'Next' to continue.


Sort by ArtistName. Click 'Next' to continue.


Adjust column widths to your preference. Make sure you check 'Hide key column'. Click 'Next' to continue.
NOTE: What you see when you look at the table is the artist name, but what is really stored in that field is the primary key autonumber.


Now we can name it as 'ArtistFK'. Be sure to check 'Enable Data Integrity' and select 'Cascade Delete'. We leave what those options mean for later, but suffice to say you'll always want to do that. Click 'Finish' to continue.


Click Yes to save the foreign key and save the relationship; A foreign key in a table defines a one-to-many relationship between two tables.


Now you've created your first relationship in the database. A one-to-many relationship is the basis for all relationships in the database. Let's look at a many-to-many relationsup

Many-to-Many (using junction tables)

I didn't list this earlier, but wanted to save the discussion for this particular topic. In the examples I gave above, I only listed two songs per album. Each song appeared in a single album. What if some crazy label got the rights for all these songs, and released a compilation album. (Can you imagine it?; Lisa Loeb and Metallica on one album. Stranger things have happened.).

Well, now we have:

• a album has multiple songs (i.e. is related to multiple songs)
• a song could appear on multiple albums (i.e. is related to multiple songs)

This is an example of a many-to-many relationship between songs and albums. So, how do we to this?

(Adding a foreign key defines a one-to-many relationship, so I just add the primary key from both tables to the other table, yes?)

Close, but no. What we need to is to add a new table, i.e. a junction table.

Think of it like this. We've defined a one-to-many relationship between artist and album, 1-to-∞. What we will do is create two 1-to-∞ relationships, with the many (∞) side (i.e. the side with the foreign key) in the junction table.

(Wait, wouldn't it end up looking like this --> 1-to-∞-to-1? Isn't that a 1-to-1 based on looking at the two ends which are both 1s?!?!)

I get that it looks that way but that is exactly how we do a many-to-many (∞-to-∞) relationship. Think of what can be in the junction table; Multiple album records and multiple songs can be in there; each of which can appear more than once!

• I Do, Firecracker
• Welcome Home (Sanitarium), Master of Puppets
• I Do, CRAZY compilation
• Welcome Home (Sanitarium), CRAZY compilation

(But we've set it up with 1-to-many relationship between Artist and Albums! What do we do about the compilation CD with many artists?)

Good catch! For now, we'll add an artist called 'Various'. If you've ever created or bought MP3s from compilation albums, you've probably seen this before (admittedly less so nowadays).

Now you have everything you need to create the other relationships. Once you're all done, go ahead and view the relationships.


You should end up with something like this. (You may have to drag stuff around to get it to look like this)


The next step is to create forms to enter data. We'll rely heavily on the wizards for this. Not because they are the best tool, but they are quick and easy.

Creating forms

Open up the form wizard.


Select tblAlbums and add all the fields.


Then add all the fields from tblTracklisting.


Then select to view your data 'by tblAlbums' and have the 'Form with subform(s)' option selected. Click Next.


Then select 'Tabular' as your layout. Click Next.


Viola!, you're presented with the following monstrosity.


Right-click on the form and select design view. The drag and resize things until they are visible on screen and situated a little better.

To navigate between albums, you need to use the record selector on the very bottom. Now you can add or delete songs associated with the album.


Now repeat the process but this time using tblArtist and tblAlbums, and you'll have a form that you can assign albums to an artist. This is just to give you an idea of what we can do with forms.

Next, let's move on to creating a simple query.

Creating a query to join all the data together

Again, we'll rely on the wizard. Go ahead and open that up.


Select 'Simple Query Wizard'.


Add the following fields from the following tables:

1. tblArtists
1. ArtistName
2. tblAlbums
1. AlbumName
2. YearReleased
3. tblTrackListing
1. TrackNumber
4. tblGenre
1. Genre

Then select the option to view a detail query.


Now should have something like this. If your results aren't correct, double-check that your relationships are set up correctly.


Last step is to create a report. Almost at the end!

Creating a report

Surprise, surprise, we'll be using the wizard again.


Add all the fields from our newly created query.


Select to view your data 'by tblArtists'.


Don't add any other groupings for now.

Sort by 'Track Number'


Select 'Outline' as your layout option. This helps to see the relationships and data hierarchy.


Save the report as rptAllData


Again, you're presented with a monstrosity. What I'm showing here is a slightly better formatted and more distinctly colored version. Obviously, with more effort you can get it looking however you want (within the limits of Microsoft Access and your database design).

Closing remarks

You've made it to the end! Way to go! I hope that you found this article helpful.

Some caveats: I made this sample using very simplistic assumptions in order to make it easy to follow. You may have already noted that there are issues with this design. This is by design: to make this article easier to follow and not overwhelm you. That being said, in this follow-up article I will introduce you to a more complex version of this database that attempts to deal with some of those issues by revising the table design (which will be issues you'll likely encounter when designing a database).

Additional Links

• Sample file
• Description of the database normalization basics
• Singular vs Plural table naming
• • Pro-Plural: https://medium.com/@fbnlsr/the-table-naming-dilemma-singular-vs-plural-dc260d90aaff
  • Pro-Singular: https://www.teamten.com/lawrence/programming/use-singular-nouns-for-database-table-names.html