Home » Access

Category Archives: Access

Access Left Outer Joins

In our prior post we reviewed how to make a relationship to “join” two tables together using a query to produce a single data set. This query allowed us to see our employees and their related hours.

The join that we made between these two tables is called an inner join. An inner join takes the common records between the two data sets and displays just those common records. An easy example of this can be shown as:

Table A has employees Tyler and Steve
Table B has employees Bob and Tyler

If we were to join these tables together using an inner join, only the Tyler records would show.

To get around this predicament, we can use a left outer join.

Left Outer Joins

A left outer join allows you to display ALL of the records from table A as well as the records from table B where the join conditions are met. Using the above example, our query would show records for both Tyler and Steve, while still excluding Bob.

Jumping back into our database, take a look at the records we have in our query. We have three records, three employees. Open up the Employee_Data table and notice that we actually have four employees in this table; Taylor Lacky is missing.

This is because Taylor Lacky’s EMPLID, 56789 does not exist in Employee_Hours, so we only show the three matched employees.

 

[the_ad id=”1391″]

 

Changing the Join Type

When we made our join in the last post we made an innner join, Access’ default join type. We clicked on EMPLID in Employee_Data and drag-and-dropped it onto EMPLID in Employee_Hours. This motion established our Left (A) and Right (B) tables. Table A being Employee_Data and table B being Employee_Hours.

To change the inner join we have to a left outer join

  1. Double Click the line that is joining the two tables
  2. Choose the second option
  3. Click OK

You should now see a line with an arrow rather than just a normal line joining the two tables.

Run your query again and you should now see employee data for Lacky,Taylor while the hours information is null (blank).

Next we will go over joining multiple tables together, making unions,  and writing expressions (formulas).


If you liked this content and found it helpful, please like the page and leave a comment below. You can also ask questions on the Forum to get personalized help on your issues and questions.

[the_ad id=”1393″]

Access: Making a Relationship Between Two Tables

Microsoft Access is considered a Relational Database system. A Relational Database allows you to establish relationships within your data to tie them together to form a single data set. In our prior Query Introduction post we went over how to…

  • Build a query based on one table
  • Filter via query criteria

In this post we will go over how to make a query that includes two separate tables to make one data set.


 

Files

Below are the tables I am using in my database. I’ve exported them into Excel so that you can import them into your database to follow along is you wish.

File:RelationalDB Tables

Below is what my database looks like once I add in all of the tables. None of my tables have a primary key, it is simply the data from the spreadsheet. Instead, I will be using EMPLID and Country/Country Code as my unique identifiers.

RelationalDB1


 

Building Relationships

Objective 1: Build a query to show employee names and hours
Objective 2: Build a query to show employee names, hours, and country/region descriptions

  1. Make a new query via the query design button on the “Create” tab.
  2. Bring in table Employee_Data and add the three fields to the query.
    Your query should look like the below.

RelationalDB2

Now that we have our Employee_Data, lets now bring in the Employee_Hours data.

  1. Add the table Employee_Hours to our query
  2. Add the fields from Employee_Hours to our query.

Note that you typically would not need another EMPLID field in your query but we are adding it here for the sake of instruction and learning.

  1. Run your query

Notice how your query is a jumbled mess and looks nothing like the data in either table. Joe for example is the only person that has worked 50 hours. What’s happening is that Access has no idea which employee hours goes with which employee. You haven’t told the query that EMPLID 12345, 50 hours, actually belongs to Joe which is causing Access to allocate each set of hours to each employee name.

[the_ad id=”1391″]

RelationalDB3

  1. Go back into the design view of your query.
  2. Click on the EMPLID field in your Employee_Data table and drag-and-drop it on top of the EMPLID in your Employee_Hours table.

You should see a line connecting the two now. You have told access that these two fields are related and have joined them together. Run your query again and you should now see your employee Names and Hours correctly.

RelationalDB4RelationalDB5

Next we will go over joining multiple tables together and then the different types of joins/relationships you can make between your data.


If you liked this content and found it helpful, please like the page and leave a comment below. You can also ask questions on the Forum to get personalized help on your issues and questions.

[the_ad id=”1393″]

Query Introduction and Making a Simple Select Query

Making a query in MS Access allows you to do a multitude of things with your data and database. The most common purposes of a query are to:

  • Search and filter your data to find something specific
  • Aggregate and Summarize data
  • Automate processes

The simplest of queries to make is the Select Query. The select query, as it’s name indicates, allows you to select/retrieve data from your table(s). You can select specific fields, and specific records that meet your filters criteria that you define.

In our prior posts, we were working with tables that contained Employee Hours and Employee Addresses. Let’s make a simple Select query on the Employee Hours table.

Steps:

  1. Go to the Create Tab
  2. Choose Query Design
  3. Select the table tbl_employee_hours from the Tables Tab
  4. Click Add
  5. Click Close

AccessCreateSelectQry

You can see our table is now shown within our query as well as the field names within the table; however, trying to run the query right now will result in an error from Access because we haven’t told the query what to select yet. All we have told it so far is that we want “it” to be from this employee hours table.

To add all of these fields to the table, double click on the Asterisk at the top.  The asterisk at the top means “All” in Access and in SQL programming language. Adding the asterisk to your query will add all fields within this table to your query.

Click the view or Run icons in the top left to run your query on this table.

AccessSelectQ1

AccessSelectQ2

What’s the point, it’s just our table

You might be asking yourself what the point of this query is and honestly, in it’s current state, none. The query provides the exact same data results as what is in our table so let’s change that.

Go back into design view on your query and remove the field tbl_employee_hours.*
You can do this by hovering your mouse cursor just barely above the field name until it shows as a black down arrow. Left clicking will select the field and pressing the delete key will remove it.

AccessSelectRemoveField

This time, I want to add only the fields Name, Hours, and EMPLID.

Steps:

  1. Double Click on Name
  2. Double Click on Hours
  3. Click and drag-and-drop the field EMPLID into the first field/column slot. I want EMPLID to be the first field in my table and doing this will bump the other two fields over for me.
  4. View/Run your query.

Now we have only the data we want to see.

Lets go back to design mode.

Notice the row descriptions for the fields we added
We have:

  • Field – Displays the field name from the table. Can also display expressions (formulas)
  • Table – Displays the Table the field is from
  • Sort –  Allows you to sort the field
  • Show – Allows you to show or hide a field or expression
  • Criteria/Or – Allows you to set filters for your Data

AccessSelectQ3

Where Criteria:

For the last bit of this Select Query, let’s add in a filter to only see the record for John Doe. John’s employee ID is 12345. Add that to the Criteria for the EMPLID field and run your query.

Our query is now only showing John’s record.

This concludes the introduction to Queries and Select Queries. We will continue to dive into further detail on Select Queries and all facets of MS Access very soon.

[the_ad id=”638″]

MS Access: Data Types Summary

Now that we have our first tables created in our database, it’s now time to talk about Data Types. Every field in a table has a data type which defines how Access will handle that field, as well as the type of data that can go into it.

You can easily see these data types by going into design view on one of your tables.  In the address table, for example, we have Number, Text, and Date data types.
AccessDataTypeExample

Please visit this page to view a list of all primary Access Data Types.


Data Type Limits

To show how these data types control the behavior and type of data that can go into each field…

  1. Go into “View” mode on your table
  2. Add a zero prior to any of the EMPLID numbers like I have for Lilly.
  3. Hit Enter to exit the field

AccessDataType01AccessDataType02

The zero is now gone. Zeros prior to a set of numbers are called leading zeros and the data type of “Number” does not support this format; which, technically makes sense, right? Numbers are shown as 12345, 1000, 9999.


I need leading zeros

Let’s say that you are working with data that has leading zeros, or maybe you wanted to add them to your five digit EMPLID to make the ID seven digits long. This can be done by first changing the data type of your EMPLID to Text.

Text is a data type that is used for alpha characters (ABCD)  as well as a combination of alpha and numeric characters. The Text data type also only supports up to 255 characters.

  1. Go into design mode on your Employee_Addresses Table
  2. Choose “Text” from the EMPLID data type drop-down
  3. Go into “View” mode on your table
  4. Add a zero prior to any of the EMPLID numbers like I have for Lilly.
  5. Hit Enter to exit the field

AccessDataType03
AccessDataType04

We now have our leading zero in our EMPLID!


To Note:

It’s important to note that the data in these tables came from Excel.  Access auto-identified the data types when we pasted it into the table. Even if we had pre-formatted our EMPLID field to be Text in Excel, Access would still have made this field a number.

MS Access: Creating a Table

In this tutorial we will learn how to create a small table and fill it with data. These tables will be filled in manually by us and will be maintained within the Access Database.

The two tables will be:

  • Employee Hours Billed
  • Employee Addresses


Excel Files:
Employee Hours Billed
Employee Addresses

 


Steps:

  1. Open the blank database you created earlier
  2. Go to the Create tab
  3. Click on Table

    Notice that a screen has popped up that resembles an Excel spreadsheet with a row and column grid. There is also a “Table1” in the left “Access Objects” Panel

  4. You can type in the values manually or copy and paste them. Lets do the latter to make things a bit easier
  5. Use the attached Employee_Hours_Billed spreadsheet and select the range A1:E5
  6. Copy the data
  7. Go to your Access Database
  8. Right click in the “Click to Add” column and choose “Paste as Fields”
    1. If you get a warning that the field name you provided is a reserved word, click “OK”
  9. Access will notify you on how many records you are about to add to the table (4)
  10. Press “Yes”
  11. You will now see your data in the Access table just as it was in the Excel spreadsheet.

Access_Paste_Tbl_data

You might notice that there is an ID column and an EMPLID column in our table. When you make a table in this manner, Access tries to establish it’s own unique identifier. The default is an ascending number for each row and since we already have a unique ID for our employees, we can delete the default from our table as it is not needed.

Access terms this “Unique ID” as the “Primary Key”

  1. Go to design view (far left icon) for our table, you will be prompted to save
  2. Save the Table as “tbl_Employee_Hours
  3. Select the “ID” field
  4. Right click
  5. Choose delete rows
  6. Access will double check and make sure you want to delete the row, choose “Yes”
  7. Access will warn you that you are deleting the “Primary Key”, choose “Yes”
  8. Click the “View” button (far left icon) to view our data
  9. You will be prompted for a save, choose “Yes”

Access_remove_PKey


Congratulations! You have now created your first table in Access!
Repeat these steps for the Employee Address information

MS Access: Getting Started

In this MS Access series we will be using a 2010 version of Access. Please keep in mind that versions 2003, 2007, and 2013 are very similar to 2010 but some of the controls may look different or be located in different places.


Summary:

In this post we will go over:

  • How to open MS Access
  • Create a blank database (DB) and save it
  • The general controls available to you that we will use in the next few tutorials

  1. Open Microsoft Access and you will start on a screen that looks like the below.
  2. Click on New in the left panel if your screen does not look like this
  3. Click Blank database if it is not already selected
  4. On the right panel, choose the name you want for your database and where you want to save it to.
  5. Click Create

AccessOpen


Your database may open with a table called Table1 already created. Go on ahead and close this by either right clicking on the tab and selecting close, or click on the gray “X” on the far right.

AccessOpen1

Home Tab:

Your Home tab is your default tab in Access. If you have used any other Microsoft programs before, you will likely recognize most of the options here. All options on the home tab are currently grayed out but will be usable once you start making objects (tables, queries, forms) in the database.

The controls on the home tab allow you to:

  • Change the view type of the table, query, or form you have selected
  • Copy and paste data or database objects
  • Sort and filter your data
  • Refresh the tables and queries in your database
  • Format the data or controls in your tables and user forms

AccessHomeTab

Create Tab:

The create tab is where you will generally go to make anything in your database.

Avoiding specifics for now, this tab allows you to create:

  • Tables – Houses your information
  • Queries – Selects, filters, and/or combines information from your tables
  • Forms – Provides a user interface to interact with your database
  • Reports – Similar to queries in that they select and summarize information but Reports tend to run off of the work a query has already performed
  • Macros – A set of Access commands or VBA code that run via user input to perform a series of processes automatically

AccessCreateTab

External Data Tab:

The external data tab allows you to import and link your database with other sources of information. For example, if you had a list of information in an Excel spreadsheet, you could link or import information and work with it within Access. The possibilities for what you can connect to here are really endless as it can range from an Excel Spreadsheet, a Corporate data warehouse, or a website.

Types of Data Connections:

Linking allows you to continually have the most up-to-date information in your database. If you link to a list on a SharePoint site and then go to that site to make a change to the data, that change will be reflected in your Access Database.

Importing provides a static copy of the information at the time it was imported.

AccessEDTab

Database Tools Tab:

This tab provides a series of tools to analyze your database’s efficiency, clean and repair the database data and structure, and move information to other databases and data warehouses. This tab will not be used much during this series and can be disregarded for now.


 

Up Next we will go over how to create a table, fill it with data, and run a query on it.

MS Access: Overview

Overview

Microsoft Access is a Database Management System (DBMS) that provides tools to create relationships between information and provides a user interface that is easy to understand, but flexible enough that it can house the most complex of processes.

Microsoft Access provides its own data storage services as well, which in my opinion, puts it in the forefront of programs one should learn when trying to grow their technical skillset. Other DBMS’ for example, like Microsoft SQL Server, or Oracle may store data, but they then require some form of programming knowledge in order to retrieve it.

Before we dive further into what MS Access is, what it looks like, and how to work in it, I would first like to go over a few terms that we will be using later on.

Terms:

Table:
A table is a place that you store information in a database; think of it like a Microsoft Excel Spreadsheet. You have your columns, columns headers, your rows, and your data.

Query:
A query allows you to pull and review a table(s) information. It can be used to Select only columns you want to see, to filter out information, to add information together, or to relate two tables of data together to combine them.

Field:
A field is a column in a table or query. Whereas in Excel you have columns A, B, and C, in Access, the field is typically represented by a field name. In an Employee Table for example, you may have Fields called “EmployeeID”, “Name”, “Country”, “Zipcode”, and “Address”.

Note that fields can also be referred to as a number, starting with the number 0. In the above example, Field 0 would be EmployeeID and Field 3 would be “Zipcode”

Record:
A record is a row of data in your table. Similar to Excel’s rows 1, 2, 3…

Unique Identifier:
Most tables you make or use will have have a Unique Identifier (UID). A Unique ID is something that distinguishes a record from all other records. An example would be “EmployeeID” or “Social Security Number”. No other record in that table should have that number.

EffectiveDate or AsOf_Dates:
As a table is used throughout time, you may want to make use of an Effective Date field. And effective date allows you to make multiple records with the same Unique ID, but have them dated to a period of time. For example:
On 10/1/2005 John Doe lived as 1234 Park Place.
On 5/1/2012 John Doe moved to 1111 New Address Road.
Having a history of data is useful if it is ever needed; however, if I wanted to select the most recent/current Address, I would want to query on the most recent EffectiveDate for John Doe.

Criteria and Where Clauses:
Criteria, or “where” clauses, are placed in a query to filter for the data that you want to see.  The employee table for example could have ten thousand records in it for ten thousand Employees, but if I only want to find the employee information for John Doe, I would place his Name, or better yet, his EmployeeID in the query criteria to only pull his record(s).

Relationships:
In a database, a relationship is something you make between two tables. Think of our Excel VLOOKUP for this one. We have two tables (two data sets). One table is Employee Names, the other is Employee Addresses. Both Tables have EmployeeID, our unique identifier. We make a relationship, or “Join”, the two tables by EmployeeID in order to tie the data together.