How to Use Access – A Guide for Advanced Users

MS Access is a powerful tool that offers numerous features to manage your data. In this Access guide, I’ll show you how to utilize some of the more advanced features that are available in Access.

To begin, here are the topics to be covered in this Access guide:

  • Overview of the example
  • Creating the tables
  • Linking the tables together
  • Writing SQL statements
  • Displaying the results on an Access Form
  • Applying Conditional Formatting

Note that you may also wish to refer to the following source for more basic concepts in Access.

The Example to be Reviewed in this Access Guide

To demonstrate the various features available in Access, I’m going to show you how to create a tracking mechanism to track the stock market, where:

  • The stocks of 6 companies will be tracked
  • A flag will be added in Access to notify you to sell the stocks once their prices cross a certain threshold

How to Use Access – A Guide for Advanced Users

In the next section of this Access guide, I’ll review the steps needed to create the tracking mechanism.

Creating the Tables

To start, create a table in Access with the following information (save the table as ‘Stocks‘):

Table in MS Access

The format for each of the above columns/fields should be as follows:

  • For the Company field, use the format of Short Text
  • For the Stock Symbol field, use the format of Short Text
  • For the Include field, use the format of Yes/No
  • For the Threshold field, use the format of Currency

Next, create a second table that will contain the stocks and their daily prices. Let’s call the second table as ‘Prices’ where you’ll need to add the following information:

Table with info

The format for each of the above columns/fields should be as follows:

  • For the Company field, use the format of Short Text
  • For the Stock Symbol field, use the format of Short Text
  • For the Daily Stock Price field, use the format of Currency

You may then choose to import the market data (i.e., daily prices of the stocks) to the ‘Prices’ table on a daily basis.

By doing so, you’ll be able to use MS Access to automatically tell you whether the daily stock prices exceeded the threshold, and whether it is time to sell the stock/s!

Linking the Tables Together

Let’s now link the ‘Stocks’ table to the ‘Prices’ table.

First, go to the Create tab, and then press on Query Design:

Query Design

Add both the ‘Stocks’ and the ‘Prices’ tables, and then link those tables using the Stock Symbol field:

How to Use Access – A Guide for Advanced Users

Next, add the fields below, by double-clicking on the fields’ names under both of the tables:

  • The Company field from the ‘Stocks’ table
  • The Stock Symbol field from the ‘Stocks’ table
  • The Include field from the ‘Stocks’ table
  • The Daily Stock Price field from the ‘Prices’ table
  • The Threshold field from the ‘Stocks’ table

Fields in MS Access

Next, I’ll review the SQL syntax needed in order to create the tracking mechanism in Access.

Writing the SQL Syntax

After you linked the ‘Stocks’ table to the ‘Prices’ table, it is time to write some SQL queries.

First, switch to the SQL View:

SQL View

 You’ll then see this SQL syntax:

Access Guide

For simplicity, I reorganized the same syntax in a manner that would be easier to follow:

Access Guide for Advanced Users

 Let’s add some additional syntax (highlighted in green):

Access Guide for Advanced Users

For reference, here is the complete query:

SELECT 
Stocks.Company, 
Stocks.[Stock Symbol], 
Stocks.Include, 
Prices.[Daily Stock Price], 
Stocks.Threshold, 

IIF(Prices.[Daily Stock Price] > Stocks.Threshold, "SELL", "KEEP") AS FLAG

FROM Prices INNER JOIN Stocks ON Prices.[Stock Symbol] = Stocks.[Stock Symbol]

WHERE Stocks.Include = YES

As you can see, 3 parts were added into the query:

  • The first part is a comma (,) that you’ll have to add after the Threshold field
  • The second part is the IIF function. It means that if the Daily Stock Price field exceeds the threshold, then MS Access should automatically place a flag to SELL. Otherwise, Access will place a flag to KEEP
  • Finally, the third part is a restriction argument to display only the stocks we checked to ‘Include’ under the Include field

Press Run to run the above query:

MS Access Guide for Advanced Users

Notice that the logic is working as defined:

  • Any Daily Stock Price that exceeds the Threshold is flagged as SELL. While, any Daily Stock Price that is below, or equal, to the Threshold is flagged as KEEP
  • Secondly, only the stocks that we checked to include (under the Include field) are actually included. In our example, we did not check the stock for Company F, and hence, it will not be displayed

Finally, save the above query as ‘Tracking

Displaying the Results on an Access Form

In this section, we are going to create a simple Access Form, where you’ll be able to:

  • Maintain the information (e.g., thresholds, the stocks that you’d like to include, the daily stock prices, etc.) under both the ‘Stock’ and ‘Prices’ tables; and
  • Track the results of your ‘Tracking’ query

Initially, go to the Create tab, and then click on Form Design to start a new form:

Form Design

You can change the background color of your form by going to the Format tab, and then choosing your desired color under the Shape Fill. Here, I selected a dark grey color for the form:

Access Guide for Advanced Users

Now let’s look at the steps to add the ‘Stocks’ and ‘Prices’ tables into the form.

First, go to the Design tab, and then select the Subform/Subreport option from the Controls:

Subform

Place the Subform/Subreport on the main form itself. A dialogue box will appear on your screen. Press Next:

SubForm Wizard

Now, add the ‘Stocks’ table (select the option of “Table: Stocks” from the drop-down list under the Tables/Queries section). Then, select the fields Company, Stock Symbol, Include, and Threshold. Finally, press Next:

Access Guide for Advanced Users

Assign the name of ‘Stocks subform’ and then press Finish:

Subform example

This is how your ‘Stocks subform’ would look like in the Design View:

Design View

Let’s switch to the Layout View in order to design the ‘Stocks subform’:

MS Access Guide for Advanced Users

Under the Layout View, you can use the mouse cursor to easily change the layout/size and the location of your ‘Stocks subform’ to fit your needs.

This is how the ‘Stocks subform’ looks like after applying some resizing under the Layout View:

Form1

Now let’s switch to the Form view. This view will eventually become the User Interface view:

Access Guide

Please note that under the Form View, the user can actually add/change/delete values within the ‘Stocks subform’. Those changes will get implemented once the user performs a refresh.

Also note that under the Form View, the user can edit values for Tables only, but not for query results that will be displayed on the form.

Repeat the above steps under this section to add the ‘Prices’ table into the main form.

This is how your ‘Prices subform’ would look like after adding the ‘Prices’ table into the form:

Access Guide for Advanced Users

You’ll see that I also added labels above each subform. Under the Layout or Design Views, you can add labels by choosing the Label option from the Controls, and then placing it on the main form (you can change the color of the label in the Format tab):

Label

Displaying the Results of your Query on the Form

You have seen how to display your tables on the main form, but what if you’d also like to display the results of your query on the main form (i.e., the ‘Tracking’ query)?

Conceptually, the process that you’ll need to apply, in order to display your query results on the main form, is the same as the one you already applied for tables.

First, you’ll need to switch to the Design View. Then, place another Subform/Subreport on the main form.

Select the option to “Use existing Tables and Queries” from the dialogue box that will appear on your screen. Then press Next:

SubForm Wizard

Select the “Query: Tracking” from the drop-down list. Then, select the fields: Company, Stock Symbol, Include, Daily Stock Price, Threshold and FLAG. Finally, press Next:

Access Guide

Assign the name of ‘Tracking subform’. Finally, press Finish.

Let’s switch back to the Layout View, where you can design the layout of the ‘Tracking subform’ (which is based on the Tracking query):

Access Guide for Advanced Users

Notice that the label of “Tracking Stocks” was also added.

Now let’s switch to the Form view, where you’ll be able to see all the pieces together:

MS Access Guide for Advanced Users

Save the main form as “User View – Tracking Stocks.”

As indicated previously, only the values under the subforms that are associated with the tables (i.e., the ‘Stocks’ and the ‘Prices’ tables) can be edited, but not those under the subform that is associated with the query (i.e., the ‘Tracking’ query).

Give it a try by changing the threshold value for the first stock from $160 to $170 under the ‘Stocks subform.’ Once you perform a refresh, the results of your change will be reflected under the ‘Tracking Subform’ where the Flag would change from SELL to KEEP for that stock:

Access Guide

In the final section of this Access guide, I’m going to show you how to apply conditional formatting.

Applying Conditional Formatting in Access

Let’s now apply conditional formatting for the ‘Tracking subform’ in order to highlight the cells that meet certain conditions.

Specifically, let’s highlight the values under the FLAG column in the following manner:

  • Highlight the value SELL in red
  • Highlight the value KEEP in green

While in the Form View, select the desired column that you want to apply the conditional formatting. In this case, press anywhere on the FLAG column under the ‘Tracking subform.’

Then, go to the Datasheet tab and press on Conditional Formatting:

Conditional Formatting

You’ll see that the “Show formatting rules for” is set for the column FLAG. You may choose different columns to apply the conditional formatting under the ‘Tracking subform.’ Now press on New Rule:

Conditional Formatting Rules Manager

Under the ‘Format only cells where the:’ section, select the equal to option.

Then, type SELL in the adjacent box:

MS Access Guide

Select the red color, and then press OK:

New Formatting Rule

Let’s add the second and final rule. As before, press on New Rule under the Conditional Formatting Rules Manager.

Under the ‘Format only cells where the:’ section, select the equal to option.

Then, type KEEP in the adjacent box. And finally choose the green color, and then press OK:

Access Guide

Once you have the two rules in place, press OK again:

Conditional Formatting

Here is how the result would look like under the Form View:

MS Access Guide

Additional Sources to Read

In this Access guide, you just saw some of the more advanced features which are available in Access.

There are other features you may also wish to explore:

Finally, you can also check the following Access guide which covers some basic features in Access.