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 guide, I am going to show you how to utilize some of the more advanced features in Access.

Here are some of the concepts I’ll be covering throughout this guide:

  • Linking tables together
  • Writing SQL statements
  • Displaying results in an Access Form
  • Applying Conditional Formatting

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

  • The following 5 stocks will be tracked: Apple, Microsoft, Google, Amazon and Netflix
  • A flag will be created in Access to notify you to sell the stocks once their prices cross a certain threshold – a threshold that will be defined by you

Here are the rules that you may define and then track:

CompanyStock SymbolIncludeThresholdFlagging rules
AppleAAPLYES$160If the price of Apple exceeds $160, then FLAG as sell, otherwise FLAG as keep
MicrosoftMSFTYES$80If the price of Microsoft exceeds $80, then FLAG as sell, otherwise FLAG as keep
GoogleGOOGLYES$1,000If the price of Google exceeds $1000, then FLAG as sell, otherwise FLAG as keep
AmazonAMZNYES$980If the price of Amazon exceeds $980, then FLAG as sell, otherwise FLAG as keep
NetflixNFLXYES$200If the price of Netflix exceeds $200, then FLAG as sell, otherwise FLAG as keep
IBMIBMNO$150If the price of IBM exceeds $150, then FLAG as sell, otherwise FLAG as keep

 

You’ll notice that I added a 6th stock, IBM. Since we only wish to track the first 5 stocks, we will see how to create a feature in Access to include only the stocks that we wish to track.

In the next section, I’ll review the steps needed to create the tracking mechanism in Access

Steps to create a tracking mechanism in Access

(1) The first step that you’ll need to perform is to create a table in Access with the following information:

CompanyStock SymbolIncludeThreshold
AppleAAPLYES$160
MicrosoftMSFTYES$80
GoogleGOOGLYES$1,000
AmazonAMZNYES$980
NetflixNFLXYES$200
IBMIBMNO$150

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

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

Save the table as ‘Stocks’

This is how your ‘Stocks’ table would look like in Access:

 

Table in MS Access

 

(2) Now you’ll need to create a second table that will contain the stocks and their daily prices.

Here, the intention is to import the market data (i.e., daily prices of the stocks) to this 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!

Let’s call your second table as ‘Prices’ where you’ll need to add the following information:

CompanyStock SymbolDaily Stock Price
AppleAAPL$165
MicrosoftMSFT$78
GoogleGOOGL$1,000
AmazonAMZN$985
NetflixNFLX$197
IBMIBM$140

Notice that I put some random values for the field/column ‘Daily Stock Price.’

I added those values for illustration purposes, but you can definitely create an automate mechanism to import/load information (i.e., the daily stock prices) into this table on a daily basis…

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

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

Save the table as ‘Prices’

This is how your ‘Prices’ table would look like in Access:

 

MS Access Table

Linking the tables together

(3) Let’s now link the ‘Stocks’ table with the ‘Prices’ table.

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

 

Query Design

 

Add both the ‘Stocks’ and the ‘Prices’ tables. And then, link the ‘Stocks’ table with the ‘Prices’ table using the Stock Symbol field:

 

Linking Access tables together

 

Please note that you could also link the tables using the Company field, as it contains common values (that are unique) under both of the tables. But for now, you can just use the Stock Symbol field to connect the two tables.

Now you’ll need to add the following fields, by double-clicking on the fields’ names under both of the tables:

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

 

Fields in MS Access

 

In the next section, I’m going to review the SQL syntax that you’ll need to write in order to create the tracking mechanism in Access.

Writing the SQL Syntax

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

First, switch to the SQL View:

 

How to Use Access – A Guide for Advanced Users

 

And this is how your SQL syntax would look like:

 

Example of a query in MS Access

 

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

 

Writing SQL statements in Access

 

Let’s write some additional syntax:

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 Stocks INNER JOIN Prices ON Stocks.[Stock Symbol] = Prices.[Stock Symbol]
where Stocks.Include = YES

 

Highlighted in green are the 3 parts, which you’ll need to add into the query:

  • The first part is a comma (,) that you will have to add after the field Stocks.Threshold. You will need to add this comma, as we have an additional element (i.e., the IIF function) that was added under the SELECT portion
  • 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:

 

Access table

 

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 IBM to be included, and hence, it is not displayed

Finally, save the above query as ‘Tracking’

Displaying the results in an Access Form

(5) In this section, we are going to create a simple form, where you’ll be able to:

  • Maintain the information (e.g., thresholds, the stocks that you’d like to include, 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 am going to select a dark grey color for the form:

 

Form in MS Access

 

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/Subreport in MS Access

 

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 :

 

Subform Wizard - Ms Access

 

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

 

Subform Wizard - Access

 

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

 

Form in MS Access

 

Let’s switch to the Layout View so that you can better design your ‘Stocks subform’:

 

Layout View - MS Access

 

Under the Layout View, you can use the mouse cursor to easily change the layout/size and 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 - MS Access

 

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

 

MS Access - Form view

 

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

Under the Form View the user can add/change/delete values for Tables only, but not for queries which 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:

 

Example of a form in MS Access

 

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 in MS Access

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 query ‘Tracking’)?

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 (as was captured in the above section).

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

Select the option of Use existing Tables and Queries from the dialogue box that will appear on your screen. Then Press Next:

 

MS Access - 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 - Subform Wizard

 

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

Let’s switch to the Layout View, where you can design the layout of your ‘Tracking subform’ (which is based on the Tracking query). This is how the results look like under the Layout View, after using the mouse cursor to change the size/proportion and location of the ‘Tracking subform:’

 

Form in MS Access

 

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:

 

Displaying results in an Access Form

 

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 Apple stock from &160 to $170 under the ‘Stocks subform’ (note that you will not be able to change the value under the ‘Tracking Subform’). Once you perform a refresh, the results of your change will be reflected under the ‘Tracking Subform’ where the Flag will change from SELL to KEEP for the Apple stock:

 

Forms in MS Access

 

In the next and final section, I am going to show you how to apply conditional formatting in Access.

Apply conditional formatting in Access

(6) In this final chapter of this guide, I’m going to show you how to apply conditional formatting for the ‘Tracking subform’ in order to highlight the cells the meet a defined criteria.

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 the Conditional Formatting:

 

Applying Conditional Formatting

 

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

 

Applying Conditional Formatting in Access

 

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

Then type SELL in the adjacent box:

 

How to apply conditional formatting in Access

 

Select the color Red and then press OK:

 

MS Access - conditional formatting

 

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 option ‘equal to.’

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

 

Conditional formatting in Access

 

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

 

Conditional formatting rules manager

 

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

 

Display a table on an Access form

 

Don’t forget to save your work…