Session 6 - Excellent Functions cool pretty stuff

Session 6 - Advanced Formulas, Functions and Conditional Formatting

Advanced Formulas

All of the sample files from today's lesson are available from Moodle.

Logical Functions

Nesting Ifs

 Have a look at the following spreadsheet.

A reminder of what the IF function looks like and the components required.

The top one is a simple If function with a calculation. In this case, our logical test will be whether the savings are less that $4000. If the amount is less than $4000, the condition will be true and the rate would be 2%. If the amount is not less than $4000, then condition is false (the amount must be $4000 or higher), which will display the rate of 5%.

=IF(A8<4000,2%,5%)



N.B. You can do the same function by placing the interest rates in cells on your spreadsheet and using a cell reference for the interest rates.

We can also NEST an IF Function. Nesting means that for the true or false activity, there is another IF function. The second example uses a nested IF function.

EXERCISE 1

Complete EXAMPLE 1 and EXAMPLE 2 in the 2016LogicalFunctionsIf spreadsheet.

AND Function

The AND function is used to compare more than one condition. It returns TRUE only if all of the conditions are met, and takes the format:

=AND(condition1, condition2,…)

For example, you could use the following formula:

=AND(B2 > 400, C2 < 300) where,

B2 > 400 is the first condition being tested

C2 < 300 is the second condition being tested

This will only return the result TRUE if the value in cell B2 is greater than 400 and the value in cell C2 is less than 300. In all other situations, the result will be FALSE.

EXERCISE 2

Complete PART A and PART B in the 2016LogicalFunctionsAnd spreadsheet.

The OR Function

The OR function is also used to compare more than one condition. It returns TRUE if any of the conditions are met, and takes the format:

=OR(condition1, condition2,…)

For example, you could use the following formula:

=OR(B2 > 400, C2 < 300) where,

B2 > 400 is the first condition being tested

C2 < 300 is the second condition being tested

This will return the result TRUE if either the value in cell B2 is greater than 400 or the value in cell C2 is less than 300. The result will be FALSE only if neither of the conditions are met.

EXERCISE 3

Complete PART C in the 2016LogicalFunctionsAnd spreadsheet.

LOOKUP FUNCTIONS

Choose

The lookup function we will use is the CHOOSE function. The CHOOSE function is designed to allow you to make a choice from a list of items. The format of this function is:

CHOOSE(lookup-value, list of items)



Create the following spreadsheet

To use the CHOOSE function, each of our values need to be given a name, not the whole data table.
Click on the first rate – cell B24 in the example and in the name box, type rate1. Call B25 rate2, B26 rate3, B27 rate4.

The CHOOSE Function will be:

=CHOOSE(C4,rate1, rate2, rate3, rate4)

Have fun nesting one or more of these functions together.

COUNTIF, SUMIF, AVERAGEIF

The functions Sum, Average and Count can be used to provide statistical information about a range of data.

These functions also have a counterpart that allows you to incorporate conditions.

Count how many have a branch of Cranbourne: =COUNTIF(B4:B29,"Cranbourne")

This is what the SUMIF looks like

EXERCISE 4

Using 2016StatisticalIf spreadsheet perform the following:

• Sum the Q1 values that are over $5000
• Sum the values in Q1 that are not Frankston.
• Count how many are Dandenong
• Count how many reps earned more than $5000 in Qtr 1
• Total the sales for Cranbourne

Multiple Conditions

Use the SUMIFS, COUNTIFS, AVERAGEIFS to have multiple conditions that are to be tested.

Count how many Reps in Cranbourne that earned over $5000 in Qtr 1

EXERCISE 5

Count of Reps in Dandenong who earned a total of more than $20000
Count of Reps who earned more than $5000 in ALL Quarters
How many of the above were Dandenong?

Conditional Formatting

Data Bars

In later versions of Excel (2007 - 2013), under conditional formatting, you can create an inline chart. I personally think these are cool!! For those visual people, you can see the data as a number as well as represented as a chart bar.

Use the same spreadsheet - 2016StatisticalIf

Highlight the values in Qtr 1. Under conditional Formatting and Data Bars. Hover over one of the buttons and see what it does.

I know, right!! AWESOME isn't it. Just a little thing, but it can mean a lot more than just the dollar figures when it comes to comparing - how easy is it now to see where the larger amounts are???

Color Scales

Another way to be more visual with your data is to use Color Scales. Rather than creating a bar, your cells are coloured based on the values, making it easier to find the lower amount and/or the high amounts.

For the default color scale, Excel calculates the median or middle value.  That cell is coloured yellow. The cell that holds the minimum value is coloured red and the cell that holds the maximum value is coloured green. All other cells are coloured proportionally around these.


If you want to set your own scale and/or rules for formatting, you can.

Icon Sets

These work in a similar way to the color scales except that icons are used rather than the cells being coloured.

If you just want to see the icons and not the values, you can check the box Show Icons Only.

With a 3 icon set, Excel, by default will take the maximum and minimum values and create 3 percentiles for the amounts in between. Again, you can change the rules to suit yourself.

Session 5 - Alas, all good things must come to an end

And so today, we say goodbye to Access and await the amazing assessment task.

Final Bits

So, today we will finish up with some final bits on Access and of course give you all the details for your ASSESSMENT TASK.

Calculated Fields

The last of the special Access field types is Calculated Fields!! You can probably guess from the name what this means...

Rules:

    The calculation cannot include fields from other tables or queries.
    The results of the calculation are read-only.

You can also use any of the in-built Access functions

Here is an example. In our Video Vidiots database, we want to be able to keep track of how long each rental is.

To do this, we need to take the Returned date and subtract the date borrowed - this will give us the loan period in days.

Step 1: Add the calculated field to the table.

Step 2: When the expression builder window appears, put in the calculation for the field.

Click OK and make sure that you save the new table design.

Step 3: Add the field to your form. I added mine to the Rentals and the Returns Subform

Now view the form and see that the calculation works!!

Basic Coding - Advanced Search on Form

This is optional as it does require a little bit of coding - just a taste of what you can do!! It doesn't really matter whether you understand it all, so long as you can duplicate it to suit your circumstances.

What this will enable you to do is to find the first record that matches the criteria and then click through the records that match until you find the one that you are after. This uses the principle of Find First and Find next.

Events

The use of macros and coding in Access requires you to understand what an event is - an event is anything that you can do as a user i.e. click on something, type something, load something etc. The list of available events for an item on a form are within the properties for that item.

To start with, I am going to make sure that the Headers and Footers for the form are displayed -  I want to add a search box within the form footer.

To display headers and footers, right-click on the Detail bar.

 

I minimised the Header, but increased the size of the Footer so that I could add some search boxes and button etc.

In the Footer, add 2 text boxes - they should both be unbound.

In the Properties Sheet, make sure you give them meaningful names (and remember what they are).

The Membership one is called SrchMembership
The Surname one is called SrchSurname

You will also need to add a Command Button - this will be where we will run the macro from. If this button is clicked (The EVENT), then the macro will run.

Once you have your text boxes and your command button, you are ready to type in your first bit of code. We will assume that we want to include the wildcard in the search so that it is not relying on an exact match.

Select the button and make sure that the properties sheet is displayed. Click on the drop down arrow next to On Click and select [Event Procedure]. Click on the ... button.

You will then be taken to the Visual Basic editor. Don't worry too much about it at this stage, just type in the same as you I have using the names that you have called your text boxes and fields etc.

 

Save and test your form - does it find the first record that matches your criteria? You can leave it at this, or you can add some more "Tweaks".

In my example, I have added a Find Next command Button, with the view that you can scroll through a list of "matches" until you find the one that you are looking for. The code is identical except for the FindNext instead of FindFirst. Because we have included the like and * wildcard, the match does not have to be exact - one letter is enough. The easiest way is to cut and paste the code and change the FindFirst to FindNext.

Bookmark will return the user to the current record.

Slightly More Advanced

You can add multiple buttons to search using different fields. I allowed a search on either membership number or by last name. I used a test to determine which of the fields had a value entered into it and created the search accordingly. Here is my code.

SEE SEPARATE DOCUMENT WITH ASSESSMENT TASK DETAILS.

Options - turning off stuff

If you were creating this for someone to use who does not know Access, you would customise it so that the user would only be able to do the things that are in your menu.

You will find all of the things that you can turn on or off in the Options for the Current Database. Turn off all of the things that you don't need as well as setting the Man Menu to be the form that displays when the database is opened.

Of course, turning off stuff also means that YOU are unable to access some of the tools that you may need to make changes to your database. To open the database so that you have all the essentials available, hold down the Shift key while double clicking the file to open it.

EXCEL Basics

It is expected that you can perform the following things in Excel before we move onto some more advanced Excel stuff.

• Create a New Spreadsheet
• Add Text, numbers and Dates into Cells
• Use the AutoFill tool
• Use formulas to perform calculations
• Understand when to use absolute and relative addresses in formulas
• Format the spreadsheet to look pretty
• Create a basic chart
• Use some basic Functions

If you are confident with the above, you do not need to stay for the quick refresher task

Session 4 - But Wait, there's more - a set of steak knives

Your Steak Knives - Reports, Queries and Special Field types

Reports

While I much prefer to use the Form Design mode for creating forms, I am more than happy to use the Report Wizard to create simple reports. Reports are usually designed to be viewed on the screen and then printed, whereas forms are mostly for screen use. Many reports are straightforward listings and will require very little "tweaking".

Simple Report

A simple listing style report is created by using the Report button on the Create Ribbon. Just select the table or query and click the button.

Click on the table that you want to use for your report, and click on report.

This will create a report that contains every field in the table selected. Mine goes over 2 pages, which I don't want, so I will have to modify the design and reduce the width of a couple of fields.

I am happy with the final result as it now fits on a single landscape page.

Activity 1

• Create a Report to list of of our products.
• Create a Report to list all of our Members.

Custom Report

When you created the Members report, you would have found that by including everything, the report created is very cumbersome. We can create many different reports for different audiences and/or purposes. An example could be a Members report that just contains phone contact information. To do this, use the Report Wizard.

Follow the prompts to create a custom report.

Activity 2

• Create a phone contact list for your Members table
• Add this report to your Main Menu

*** Advanced Exercise

• Create address labels for your members table
• Create Barcode labels for your products (include Barcode plus Title) - HINT: you will need a barcode font to have an actual barcode on the labels. Use Avery J8164 labels.
• Add these to your main menu. 

Queries

Queries are where Access really comes into its own. A database choc-a-block full of information is not much use unless we can get the information we need from it, when we need it.

Query Wizard

Simple queries can be created using the Query Wizard. In Access 2013, there are a number of different types of queries that can be performed.

A simple Query allows you to get information from your tables, selecting the fields that you need.

A Crosstab Query is a method of summarizing data, calculating statistics and then grouping the results. It creates a matrix where the column headings come from the values in a field.

A Find Duplicates Query allows you to find records with duplicate values in a table or query.

A Find Unmatched Query allows you to find records that are in one table but not in a corresponding table. This is useful if you didn't set the referential integrity when setting up relationship or when a relationship has been set up after data has been entered.

Simple Query

A simple query allows you to specify what fields you want in the resulting table.

 

It does not allow you to put in any search criteria to filter out the records, but you can use this as a base and go into query design to add the criteria

or you can just use Query Design and bypass any of those wizard questions. The result from any query will look like a table - the advantage of this is that you can do anything with it that you can do to a table - create a report or form or even another query!!

Query Design View

Now we can get into the nitty gritty of some more fun stuff with Access. Query Design View allows you to take a simple query, or create a simple query, and add boolean operators to filter the records in your table or tables.

Within Queries, you can use any of the standard Access operators.

For more information on Access operators, you can visit https://support.office.com/en-AU/article/Use-query-criteria-in-Access-2013-4b102653-7b6f-4358-9751-1ab51fe89469

Once you have created a simple query, you can go into Design View to add your criteria.

In the above simple query, you can see the table that is being used along with each field in the table underneath. The tick in the box tells Access to include this field in the result - you can have fields that you use for criteria, but you don't have to have them in the end result.

Adding criteria is as simple as using the first Criteria line.

Placing more than one criteria on the same row will create an AND query. With an AND query, both conditions must be true for the record to be in the result.

To create an OR query, simply place your criteria on two separate lines.

Combine with comparison or special operators to create complex queries.

The above example was initially a simple query using the Query Wizard and changing to design view. You can also create a query from scratch using Design View.

Design View

Start by selecting the tables - this will retain any relationships that may be in place.

At the bottom of  the screen, add your fields by dragging, double-clicking, or selecting from the drop down list.

With relationships in place, you can very easily incorporate information from multiple tables within your query.

And of course add any criteria to filter the records.

Activity 3

Think about and then create the following queries:

• Create a list of all members who have not returned an item yet (regardless of whether it is overdue) - include in the result: membership number, name, phone, title of movie/game
• Create the same query, but this time only for those who have movies that are overdue.
• Create a query to determine the popularity of a particular movie by seeing who has borrowed it, include the membership number, customer name, and the date that it was borrowed
• Create the same query, but just for last month.

Report from a query

As mentioned before, once created, you can use Queries in reports instead of tables. This allows you to display your query result in a professional manner.

Activity 4

Create an overdue report
Create a Lost product report - i.e. any movie that is overdue by more than 14 days

Query Trick

If you want to have one query that you can use multiple times - i.e. change the value of the criteria each time it is executed, place a prompt between square brackets

[enter criteria]
[Enter date for the ovedues]
Between [start date] and [end date]

Other Field Types

Now that we have grasped the main parts of Access, we will progress on to look at a few of the field types that we missed out on at the beginning.

Attachment

"An attachment field type is used to Attach images, spreadsheet files, documents, charts, and other types of supported files to the records in your database, similar to attaching files to e-mail messages." In other words, an attachment field type allows you to add multiple attachments to each record in your table.

In our case study, we want to place a copy of the DVD/BluRay/Game Cover for each item in our Products table.

Step 1: Download appropriate images using google images for your movies!! There are some websites specifically for getting CD or DVD covers, but for mine, I just used Google Images and the name of the movie.

Step 2: Open your Products Table in Design View so you can add the Attachment field.

Use the insert key to insert a new field or just add it to the bottom - it really doesn't matter. I called mine Extras

Choose Attachment as the field type and Save then close your table.

Step 3: Add your images. To add your attachments - i.e. your image files, click on the paperclip image and use the dialog box to add each image to the appropriate record. You can have more than one attachment, but for our exercise, we only need one!!

Step 4: Add the field to your Movies Form. Open your main movies form - the one that we use to add and delete movies, and add the Extras field onto the form.

Once you have added it to the form, Save and then change to Form View. Hopefully you will now see an image against each movie in your table.

Test the form - use your navigation to scroll through the movies in your table.
 
You can attach a maximum of two gigabytes of data (the maximum size for an Access database). Individual files cannot exceed 256 megabytes in size.

Lookup Field

A lookup field type will create a many to many relationship with data in another table. That probably doesn't really mean too much to you, but what it allows you to do is to have multiple values within the one field. The best way of understanding this is to see it in action.

Step 1: Start by creating a new table - I created one called Keywords. It will just have the Autonumber ID field plus a keyword field.

Click on Create and then choose Table.

Add the two fields by clicking in the column heading section of the table (or change to design view if you prefer). The field type will be short text.


Type in the Field Name of Key Words.


Then add a whole lot of  keywords - I had a bit of brain drain and couldn't really think of any, so mine are just random.

Once finished, Save the table as Keywords.

This will become our looked up table.

Step 2: Add the Lookup field to the Products table.

Open up your Movies Table and change to Design View

Create a new field called keywords and select Lookup Wizard as the Data Type.

 

Follow the prompts. 

First you will be selecting a table that will be providing the values for the lookup.

Choose the fields

Pretty sure I said that you should always uncheck the Hide Key Column - well this is one occasion when it is OK to keep it hidden.

Use Keywords for the label, and importantly, put a tick next to the Allow Multiple Values.

Click Finish.

 

The Keywords field will have a field type of number when it is finished as that matches the autonumber ID field. 

Open your Movies Form again and change to Design View. Click the button to display the fields and drag your keywords field onto the form

 

 

Save the form and go back to Form View. Go through each record and select keywords for each movie

This field can then be used in queries.

Activity 5

Add a field to your animals table for Behaviours and temperaments.
You will also need a table to lookup Behaviours and temperaments - Call this table Traits.

• ID (Autonumber)
• Trait Description (Text)

• Create a report for all of the animals in your care - grouped by animal type

A lady rings to see if you have found any Yellow Labradors in the past week - worried sick about her beloved pet that escaped during the New Years eve fireworks. Create a query that will determine whether you have any animals matching her missing furbaby. (add some potential matches into your table to make sure that you get some items in your report)