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:
|Company||Stock Symbol||Include||Threshold||Flagging rules|
|Apple||AAPL||YES||$160||If the price of Apple exceeds $160, then FLAG as sell, otherwise FLAG as keep|
|Microsoft||MSFT||YES||$80||If the price of Microsoft exceeds $80, then FLAG as sell, otherwise FLAG as keep|
|GOOGL||YES||$1,000||If the price of Google exceeds $1000, then FLAG as sell, otherwise FLAG as keep|
|Amazon||AMZN||YES||$980||If the price of Amazon exceeds $980, then FLAG as sell, otherwise FLAG as keep|
|Netflix||NFLX||YES||$200||If the price of Netflix exceeds $200, then FLAG as sell, otherwise FLAG as keep|
|IBM||IBM||NO||$150||If 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:
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:
(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:
|Company||Stock Symbol||Daily Stock Price|
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:
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:
Add both the ‘Stocks’ and the ‘Prices’ tables. And then, link the ‘Stocks’ table with the ‘Prices’ table using the Stock Symbol field:
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
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:
And this is how your SQL syntax would look like:
For simplicity, I reorganized the same syntax in a manner that would be easier to follow:
Let’s write some additional syntax:
Prices.[Daily Stock Price],
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:
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:
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:
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:
Place the Subform/Subreport on the main form itself. A dialogue box will appear on your screen. Press Next:
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 :
Assign the name of ‘Stocks subform’ and then press Finish:
This is how your ‘Stocks subform’ would look like in the Design View:
Let’s switch to the Layout View so that you can better design your ‘Stocks subform’:
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:
Now let’s switch to the Form view. This view will eventually become the User Interface 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:
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…):
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:
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:
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:’
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:
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:
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:
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:
Under the ‘Format only cells where the:’ section, select the option ‘equal to’
Then type SELL in the adjacent box:
Select the color Red and then press OK:
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:
Once you have the two rules in place, press OK again:
Here is how the result would look like under the Form View:
Don’t forget to save your work…