Category: MS ACcess

MS Access: Use comparison operators and dates in queries

On By Ben Larson Ph.D.In Data Architecture, data modeling, databases, Free Courses, MS ACcessLeave a comment

This tutorial was created as supplemental material for my undergrad course in database design. You can find the full course here: Course

For this example, I want to create a new table. I have attached an Excel file below that you can download.

emp_table_accessDownload

From access: External Data> New Data Source > From File > Excel

Check First Row Contains Column Headings and click Next

You can change the data types of the column, but I am just leaving them as is.. click next

Let Access add primary key > click next

Name your table and hit finish

Now if you click on the Employee table in the table list on the left you will see the results

Comparison Operators

Comparison operators are the symbols that let us check if something is equal to, greater than, less than, etc

Lets create a query using comparison operators

Click on Create > Query Design

Drag the Employee table into the query workspace

Add all the fields below and in the Criteria spot for Age, put >40

Right click the Query Tab and click Datasheet View

You can now see the results with employees only over the age of 40

Play around with it, try less than 40, >= or <=, just try some different queries

Also remember, you can right click on Query1 tab and select SQL View to see the SQL code that runs the query

You can also use Between to select Criteria- Below will return everyone aged between 30 and 45

Dates

Now lets try querying dates

When working with dates, you need to put #’s before and after the date. If your Access is set to USA settings, we go MM/DD/YYYY, European (and most of the rest of the world) goes DD/MM/YYYY

The below query will return employees hired after Jan 1 2010

And here are the results

MS Access: Query two related tables

On By Ben Larson Ph.D.In Data Architecture, data modeling, databases, MS ACcess, sqlLeave a comment

The entire purpose of related tables is that they allow you to query information from multiple tables at once. In this example we will be creating a query that looks at the Class and Teacher tables we built in the intro to MS Access Lesson: link to lesson

To start, we will select Create from the Menu bar and Query Design from the Ribbon

Next drag the Teacher and Class table over to the blank space for Query1

down below you can select the columns (fields) you want to bring in from the tables.

Next, put all the all the available columns in the Fields below

You’ll notice the Fields all have their table names in front of them in the drop down. This is common SQL notation for <table>.<field>

Right click on the query tab and select datasheet view.

Note the two tables are matched up by the teacherID in two different tables. That is the relationship. That is how relational database allow tables to interact

Lets remove the teacherID from the query

Go back to design view and uncheck the columns to hide them from the query

To see the results, click on the Query1 tab and select Datasheet view

So you can see, our query returned information found in 2 different tables

Now, let’s right click on Query1 tab and select SQL View

This is how you would write this query using SQL

Select Class.ClassID, Class.Class_NM, teacher.teacher_name — This means that we want to see these three columns. Note the table name is in front, followed by the column or field name, separated by a ‘.’ This is common practice in SQL. It tells the database which table the field is in. And in situations like the teacherID column that is found in both tables, it clarifies which one you want.

from teacher INNER JOIN Class ON teacher.teacherID= Class.TeacherID; — this is a typical join statement. It says use both teacher and Class tables, and match the records up using the TeacherID field.

For more information in Joins, check my SQL Intro to Joins and SQL 4 Types of Joins

Back to Main Course Page: Course

MS Access: Import Excel File, Sort and Filter Data

On By Ben Larson Ph.D.In databases, Free Courses, MS ACcess2 Comments

In this lesson, we will be importing an Excel file into MS Access and learning to use the sort and filter functionality. If you want to follow along, download the Excel file below:

KMeans1Download

I am using the same database I build in the previous lesson. You can start a new one if you want, but since most of these early lessons will be sandboxing( a programming term for playing around with a software platform) in Access it doesn’t really matter if you just use the same database for everything.

From the Home screen in Access, click on the External Data tab and open up the External Data ribbon

Select New Data Source > From File > Excel

Browse for your excel file, select Import the source data into a new table and click ok (note, if you want to use a different Excel file, you can)

My excel file has column names in the first row, so I make sure that is check and I click next

Up top, you can change column names, and data types, but in this case, Access did a good job of assigning datatypes for me. Next>

I check Let Access add primary key, you could just select your own from the drop down though. Next >

Name your table whatever you want and hit Finish

If you spent a lot of time fixing data types and column names or if you are going to upload a file like this on a regular basis, you can save import steps, but I am going to skip this for now, just hit close

Click on your new table to open it up

Sorting

Filters and sorting work like they do in Excel, here I select my column and Sort Largest to Smallest

Filter

Here is select Model and filter down to a single model (Accuvix A30)

Back to Main Course Page: Course

Database Development and Design

On By Ben Larson Ph.D.In Data Architecture, data modeling, databases, Free Courses, MS ACcess10 Comments

Week 1

  1. Databases: What are they, and why do we need them?

Week 2

  1. Database Design: First Things to Consider
  2. Database Design: The Development Process
  3. SQL: Intro to SQL Select, From, Where, Order by
  4. Lab 1 Walkthrough

Week 3

  1. Database Design: Initial Requirements and Use Cases
  2. SQL: Intro to Joins

Week 4

  1. Database Design: The Model
  2. Data Jobs: Careers in Data

Week 5

  1. Database Design: Developing a Data Model
  2. Database Design: Lab 4 Walkthrough

Week 6

  1. Database Design: Generalization and Specialization
  2. Database Design: SQL Sub Languages
  3. SQL: Create tables and insert data
  4. Lab 5 Walkthrough

MS Access: Intro to Access

On By Ben Larson Ph.D.In Data Architecture, data modeling, databases, MS ACcess3 Comments

MS Access is a all in one database solution provided as part of MS Office. Access was much more popular years ago when more powerful solutions such as Oracle an SQL Server couldn’t be effectively run on desktops. While Access’s time in the sun is definitely in decline, for someone new to concepts of databases, Access can be a great introduction.

Let’s start by creating a simple database. Open up Access and click on Blank Database

Give your blank database a name in the pop up window

By default, you’re new database will start with a Table1 with an ID column. Click the drop-down on the next column space to add a new column. For this example, let us select Short Text as our data type. Short text can handle any text up to 255 characters

After you set the datatype, you can click on the column name and rename it

Let’s add another column, set the datatype to number

Now click the X next to table1 and you will be prompted to name your table

Another way to build a table is through the table design feature

You can name your columns and select data types: Note AutoNumber is an auto incrementing datatype that works to provide you with an auto populating ID number

Access requires primary keys for all tables (something not required, but recommended in other systems like MySQL or SQL Server). To create a Primary Key, right click on ClassID and select Primary Key

Finally, let’s just use SQL to create a table. Select Create an then Query Design from the ribbon menu

Select SQL View

Put in the SQL below

create table teacher (
teacherID int primary key,
teacher_name varchar(255))

Click on the new teacher table in the left and you can fill in some data

Now let’s create a relationship between the Class and Teacher tables. Click on Database Tools > Relationships

Drag the Class and Teacher table into the blank sheet and click Edit Relationships

Click create new, select your tables and columns from the drop down, and select Enforce Referential Integrity and select the Create button

A Relationship line will appear, showing you your connection

Back to Main Course Page: Course