You are here,

Course categories:


An Introduction to Microsoft Excel
Tutor: Software tutor
Cost: $250.00

An Introduction to Microsoft Excel

The ability to efficiently use a spreadsheet has almost become a mandatory skill for anyone working in a modern business environment. This course introduces you to Excel, Microsoft's powerful spreadsheet and database application for personal computers. It will provide you with the capability and confidence to use Excel professionally.

What's a spreadsheet?

Spreadsheets consist of many cells formed by the intersection of rows and columns. Numbers (and text) can be placed in these cells and spreadsheet applications such as Excel can use formulae that you enter (and functions that it provides) to manipulate the cell contents quickly and easily. Excel is thus ideally suited to adapting calculations from paper and the calculator to the computer. 

This course

We concentrate on presenting the 'big picture' - an overview of Excel's ability to manipulate numbers and text. We show you Excel working across many disciplines and teach you to harness the power of Excel to make 'spreadsheeting' a smooth, efficient and rewarding process.

During the course, you will learn to record, total and sort data; process your data into graphs, view spreadsheet data in a number of different ways and also to use Excel as a database.

Here is a link to some of the instructional movies in the course:

Click here to download and play one of the movies from the course. It shows Excel working at its simplest level. If the movie (a 1.5 Mb file) works well on your computer, then your equipment is suitable for the  course.

Click here to play a movie illustrating the value of setting absolute cell references in Excel worksheets.

Click here to play a movie showing how to split a window in an Excel worksheet.

Click here to play a movie which shows how macros can be used to good effect in Excel to speed up tedious operations.

 

Syllabus for An Introduction to Excel

 Module

    Description

Introduction (this module)

Starting Excel. Using desktop shortcuts. Add up some sales figures, using the AutoSum feature.

Spreadsheet Process

Opening a workbook and using a spreadsheet to calculate the interest payable on a loan. We vary the amount borrowed and the interest rate and examine the result. Using an Excel workbook containing a list of class names - rearranging (sorting) a list of names in ascending and descending order.

Some Preliminaries

Excel and the Windows and Macintosh operating systems. Coping with different versions of Excel. Formatting diskettes and copying workbooks to disk for others to share. Tuning your computer system. Installing Excel on your computer. Creating the right environment for Excel. Setting up the program so that it opens in the folder where most of your spreadsheet data is stored. Backing up your data.

The Excel Interface

The user interface (screen). The title bar, cells, cell identifiers, toolbars, naming sheets and saving workbooks. The editing box (or formula bar). The importance of the mouse pointer shapes. Adjusting row and column widths and heights. Selecting and copying cells. Splitting the screen and hiding rows and columns. Toolbars & tool tips. Manipulating toolbars. Formatting cells in a spreadsheet. Formatting numbers and text. Entering and correcting data. Inserting rows, columns, cells and comments. Inserting and deleting worksheets. Moving, copying and renaming worksheets. Selecting some or all worksheets within a workbook.

Spreadsheet Fundamentals

Some examples of tasks performed with Excel - financial analysis, medical research results, work attendance records, databases. Using existing spreadsheets and modifying data, viewing formulae, changing the display of lists and charts. Using the AutoSum feature. Printing a spreadsheet. Setting print zones and adding custom headers. Using the AutoFill feature and the Fill feature (which can insert series with incremental steps). Types of data - constant values and formulae. Cutting, pasting and copying. Sorting, zooming and charting.

Calculations

Performing calculations with Excel. Entering formulae.  Using operators in formulae. Using functions. Calculating an average. Displaying and printing formulae in a spreadsheet. Formula of constants. Formulae containing cell references. Operators and their order of calculation. Joining text. Logic operators. Locking cells. More Excel functions - minimum cost, average cost, total costs, number of costs and the count function. Error messages.

Relative and Absolute References

A focus on using relative and absolute references skillfully in spreadsheets. Switching between relative and absolute references. Combining all your skills by building a payroll spreadsheet.

Using Excel as a Database

Using Excel as a database. Tips for database design. Managing a stock list for a small business. Using AutoFilters to display only the required information from a larger set of data.

Printing to PDF

A bonus module. Installing a PDF writer in your computer. Testing its functionality by printing Excel spreadsheets to a PDF file (for distribution to others).

Running an Excel Macro

When it becomes necessary to automate tasks in the Excel environment, we develop and run short program fragments ~ macros. We run a macro to show how selection of kitchen cabinetry might be automated in a fabrication factory.

Intermediate Excel
Tutor: Software tutor
Cost: $250.00

This course follows the previous course ('An Introduction to Excel') and provides you with an introduction to some of the more advanced features of Micosoft's premier sreadsheet program.

As much as possible, we show how the program works using real world examples. We cover such things as handling the Excel 2007 interface, the use of functions, the IF statement, pivot tables, relative and absolute cell references, advanced charting, the database functionality of Excel, goal seeking, scenarios, grouping and more. We show exampes using both Excel 2003 and Excel 2007.

 

 

 

 

 

 

Sample some of the training materials from the course

 

 

Intermediate Excel

 

This course follows our earlier course ('An Introduction to Microsoft Excel') and is organized in much the same way. We aim to take your further along the path to mastering Excel and importantly to see how using the program can improve your efficiency at work. The course contains instructional material examples created using both Excel 2003 and Excel 2007.

As much as possible, we show how the program works using real world examples. We cover such things as the use of functions, handling the Excel interface, the use of conditional formulae using the IF statement, we take a closer look at the use of relative and absolute cell references in formulae, advanced charting, the database functionality of Excel, goal seeking, scenarios, grouping and more.

Select the modules below, preferably in the order shown. After you have completed a module, click the back button in your browser to return and select the next module. You may need to allow Active X controls to run (just click the yellow bar at the top of the screen and Allow Blocked Content. When returning to this menu of modules, you may have to click the back button twice.

    Module

    Description

Introduction

Our learning system, downloading sample files. Revising your understanding of some of Excel's basic capabilities by developing an expenses spreadsheet. We ensure that you understand the differences between relative and absolute cell references by working on a spreadsheet which is used to calculate payroll. Excel as a database tool, Excel as a web site tool. Collecting field data, manipulating lists.

Charting

This module covers the generation of charts in Excel more comprehensively than was done in the earlier course. We survey chart types, creating various charts using the chart wizard, show how to graph non-contiguous data, select data ranges, add to data ranges etc. Emphasis is placed on formatting charts for ease of data interpretation. Instruction is provided on moving and re-sizing a chart, changing chart data, 3D charts, adding extra data, placing charts on separate sheets, adding a trend line to a graph, linking charts to other applications using both DDE and OLE links. 

Spreadsheet design

In this module, we consider some of the mechanisms which can be used to increase the functionality and usefulness of spreadsheets. We emphasize that the 'look and feel' of an Excel application is important to the user. Advice is provided on the value of dividing a workbook into a logical set of sheets. The value of an input sheet. Formatting cells for input and output. A brief look at error checking, cell validation, naming ranges etc. We emphasize the value of including diagrams as an aid to the interpretation of spreadsheets.

The Excel Interface

Examining the user interface - the appearance and arrangement of elements in the Excel screen. The role of title bar, locating cells, columns and rows, cell identifiers, toolbars, naming sheets and saving workbooks. Creating new toolbars. The differences in the interface between earlier versions of Excel and Excel 2007. The editing box (or formula bar) and its role. The importance of watching the shape of the mouse pointer as you work with Excel. Adjusting row and column widths and heights both by dragging and auto fit. Selecting and copying cells from one location to another. Splitting the screen and hiding rows and columns to make viewing large spreadsheets easier and facilitating the comparison of columns of data which are widely separated. Toolbars & tool tips. Manipulating toolbars. Formatting cells in a spreadsheet, both numbers and text. Entering and correcting data in cells. Inserting rows, columns, cells and comments. Inserting and deleting worksheets in a workbook. Moving, copying and renaming worksheets. Selecting some or all worksheets within a workbook in order to cut down on data entry time.

Using drop down lists & the LOOKUP function

Improving the quality of data entry by the use of drop down lists. Drop lists on the same sheet as data. drop lists on different sheets. Experimenting with and using this great time saver in formulae. Examining a sheet which automatically grades student results. Using VLOOKUP to create drop down lists of allowed input values.

Performing Calculations

REVISION MODULE:

Using static values in a worksheet. Entering formulae in cells. Using Excel to perform calculations. The value of the F9 key to recalculate values in a sheet - generating a set of random numbers. Displaying formulae in a sheet. Printing a master copy of your formulae for audit purposes. A general discussion regarding functions in formulae. Some common functions - minimum cost, average cost, total costs, number of costs and the count function. Using operators in formulae. Using constant values in formulae. Displaying formulae in a spreadsheet. Order of calculation - operators. Using logic operators - IF, NOT, AND in formulae. Locking data (and formulae) in cells. The importance of correctly formatting data in cells.

Using Forms

There are two aspects to form - copies of paper forms and 'smart' forms. We look at sevceral 'paper' forms and then use a forms to provide a convenient mechanism for entering data into spreadsheets. We use a find facility built into the form and then show how to build your own form and populate it with action buttons from the toolbox. We show that there are two different ways to create forms - the 'auto' worksheet form and the forms using Visual Basic programming to connect text boxes to data contained within the sheet. 

Using named ranges

Why use named ranges in Excel? Using ranges effectively. Examining the various methods of creating named ranges. Using named ranges across different sheets in a workbook. Using ranges in a formula which draws on data in different sheets and calculates average values on a summary sheet. Ranges live in memory - creating a named range which reports year in a cell. The value of named ranges when using drop down lists and Data Validation. Case study: creating range names in a spreadsheet of your own. 

Functions

A quick revision of the use of functions in Excel. Inserting functions in a formula, changing the range of cells over which the formula applies. Creating a spreadsheet to calculate loan repayments. Installing 'Add Ins' - the Data Analysis Pack and its use to perform Student's T Test on two sets of samples.

Using the IF statement

Using the IF statement to set up conditional statements. An example where we set up a formula to check if values above and below a nominated number. Using the IF statement to creating a formula which calculates the payment of bonuses to sales staff based on performance. using the IF statement to automatically grade student's work. A case study - some detective work in a spreadsheet which uses the IF statement.

Avoiding Excel Errors

Methods for ensuring that your spreadsheets are free from errors. What to do about error messages, intermediate values, validating your spreadsheets.

Importing data into Excel

Bringing data from main frame computer databases into Excel.

Using MailMerge

Using Excel to manage your mailing lists in conjunction with Microsoft Word.

Conditional Formatting

Formatting cells according to different criteria.

Relative & Absolute Cell References

REVISION MODULE. A focus on using relative and absolute references skillfully in spreadsheets. Switching between relative and absolute references. Combining all your skills by building a payroll spreadsheet. 

Using Macros

Creating macros to speed repetitive tasks.

Downloads

Visit the downloads page if you have not already done so.

 

Advanced Excel
Cost: $250.00

Advanced Excel

This course builds on the courses 'An Introduction to Excel' and 'Intermediate Excel'. We cover the more advanced features of Excel usage, including an examination of many more functions [VLOOKUP, HLOOKUP, INTRATE etc.], the use of the IF statement in formulae, creating and using named ranges, creating sophisticated charts, goal seeking, pivot tables, etc. It is important to note that while we show many case studies involving client problems, here we concentrate on teaching you to use the tools in Excel more effectively. The course does not set out to provide advice on solving your particular Excel problem. We cannot develop of complex algorithms for you. For that, you need a Excel consultant who will work with you to understand your particular problem.

Click here to play a movie which summarizes the earlier course. The movie is quite large (5Mb), but if you are contemplating taking this course with us, we would encourage you to download and play it.

Click here to play a movie which illustrates the use of the VLOOKUP function.

 

  Module

   Description        

Using functions

A quick revision of the use of functions in Excel. Inserting functions in a formula, changing the range of cells over which the formula applies. Creating a spreadsheet to calculate loan repayments. Installing AddIns - the Data Analysis Pack and its use to perform Student's T Test on two sets of samples.

Using the IF statement

Using the IF statement to set up conditional statements. An example where we set up a formula to check if values above and below a nominated number. Using the IF statement to creating a formula which calculates the payment of bonuses to sales staff based on performance.

Revisiting the Excel Interface

A second look at the Excel interface. Examining the 'Getting Started' pane. Manipulating toolbars. Problems occurring when the standard and formatting toolbars are combined. A look at the functions available on the various Excel toolbars - standard, formatting, borders, chart, control box, drawing, exit design mode, external data, forms, formula auditing, list, pivot table, visual basic, word art, picture, reviewing and web toolbars. Creating a new custom toolbar; populating toolbars with icons. Adjusting the properties of a toolbar item.

Naming sheets, switching between worksheets, copying and moving worksheets around in a workbook. The value of input sheets to improve spreadsheet design. The importance of mouse pointer. Manipulating toolbars. Splitting and freezing the screen.

Using forms

Forms provide a convenient mechanism for entering data into spreadsheets. We use a spreadsheet which contains a form used to scroll through a large data list. We use a find facility built into the form and then show how to build your own form and populate it with action buttons from the toolbox. We show that there are two different ways to create forms - the 'auto' worksheet form and the forms using Visual Basic programming to connect text boxes to data contained within the sheet. 

Ranges

Why use named ranges in Excel? Using ranges effectively. Examining the various methods of creating named ranges. Using named ranges across different sheets in a workbook. Using ranges in a formula which draws on data in different sheets and calculates average values on a summary sheet. Ranges live in memory - creating a named range which reports year in a cell. The value of named ranges when using drop down lists and Data Validation. Case study: creating range names in a spreadsheet of your own. 

Charting

This module covers the generation of charts in Excel more comprehensively than was done in the earlier course. We survey chart types, creating various charts using the chart wizard, show how to graph non-contiguous data, select data ranges, add to data ranges etc. Emphasis is placed on formatting charts for ease of data interpretation. Instruction is provided on moving and re-sizing a chart, changing chart data, 3D charts, adding extra data, placing charts on separate sheets, adding a trend line to a graph, linking charts to other applications using both DDE and OLE links.

Spreadsheet Design: - from a user perspective

In this module, we consider some of the mechanisms which can be used to increase the functionality and usefulness of spreadsheets. We emphasize that the 'look and feel' of an Excel application is important to the user. Advice is provided on the value of dividing a workbook into a logical set of sheets. The value of an input sheet. Formatting cells for input and output. A brief look at error checking, cell validation, naming ranges etc.

Performing Calculations

Using static values in a worksheet. Entering formulae in cells. Using Excel to perform calculations. The value of the F9 key to recalculate values in a sheet - generating a set of random numbers. Displaying formulae in a sheet. Printing a master copy of your formulae for audit purposes. A general discussion regarding functions in formulae. Some common functions - minimum cost, average cost, total costs, number of costs and the count function. Using operators in formulae. Using constant values in formulae. Displaying formulae in a spreadsheet. Order of calculation - operators. Using logic operators - IF, NOT, AND in formulae. Locking data (and formulae) in cells. The importance of correctly formatting data in cells.

Using drop down lists & the LOOKUP function

Experimenting with and using this great time saver in formulae. Examining a sheet which automatically grades student results. Using VSVLOOKUP to create drop down lists of allowed input values.

Goal Seeking

Using Excel to find the 'solution' to a problem. We change a single input in a cell and have Excel calculate a desired target result - the sales that one department of our company needs to make in order to reach 25% of total sales. Other examples involve a calculation of the number of cups of a certain beverage type we need to sell to maximize profit.

Solver

The companion approach to goal seek. Using Excel to find the 'solution' to a problem in which there are multiple variables.

Scenarios

Using the scenarios tools in Excel.

Grouping

Using the group option to 'pull' data from different workbooks as a summary. Grouping and outlining. hiding details, promoting and demoting groups.

Managing Stocks & Shares 

Using Excel to keep track of a share portfolio. Setting up a share analysis worksheet, buy and sell spreadsheets covering various stock market sectors. Advanced sorting techniques are illustrated and links from Excel to Access databases are established.

Access/Excel interactions

Using Excel to hold database information. Excel as the best 'root' container for your Access applications.

Pivot tables

Using the pivot table wizard, building pivot tables. Using the pivot chart wizard. Pivot table reports.

Mail Merge

Mail merge is not a simple process. However, when Excel is used in combination with Microsoft Word, merging can be set up to proceed smoothly. In this module we using Excel as a database to hold details of names and addresses, create a form letter in Word, review the data source information, add merge fields and merge this data with the form letter. The filtering data functions in Excel/Word are used to produce letters for specific sub groups.

Some Preliminaries

Exchanging spreadsheet data with different versions of Excel. Scrubbing diskettes for safe export of data. Different methods of starting Excel. Creating a suitable folder structure to hold Excel workbooks. Changing the user preferences - adjusting the AutoSave time and back up type. Changing the International settings.  Installing 'add-on' applications such as Microsoft Map and statistical packages.

Export/Import

Understanding Excel's data format. File types supported in Excel. Text files; *.prn, *.txt, *.CSV, Backup files; *.xlk, *.bak, Data Interchange Format; *.dif, SYLK files; *.slk, Toolbars; *.xlb, Add-Ins; *.xla, xll, Templates; *.xlt, Workspaces; */xlw, dBase files; *.dbf, Works 2.0; *.wks, Quattro Pro; */wq1, Lotus 1-2-3; *.wk?, Web Pages; *.htm etc.

Importing data by opening CSV files. Exporting data from Excel - space delimited & comma delimited files. Linking Excel to Microsoft Access - two methods of sharing data. Sharing Excel sheets among different users. Exchanging workbooks with other users.

Installing Add-Ins to the Excel environment

Installing additional functions in Excel. Developing a spreadsheet for calculating the interest rate on an investment using one of these extra functions - the INTRATE function. We develop a model for calculating the 'proper' rate of return on an investment. Installing tools for statistical analysis.

Using Excel Filters

A case study updating and enhancing a spreadsheet used to manage attendance of teams of workers working rotating shifts. Using the COUNT function, a practical example of the database functions in Excel.

Fundamentals

A review of spreadsheet fundamentals. Displaying formula in a sheet. Static (constant) and dynamic data. AutoFill. Creating series of different types.

Making Excel perform calculations

Reviewing your knowledge of building formulae. Entering formulae, editing formulae, functions, AVERAGE, MAX, MIN, COUNT, mathematical operators, sequence of operations, logic operators, range checking. Exposing formulae in a sheet. The mechanism for locking cells containing with formulae. Protecting worksheets.

Cell references

A review of relative and absolute cell references. Copying cells with references, switching between absolute and relative references. Using Excel as a database, using filters.

Macros and Visual Basic for Applications

The first step in automating tedious tasks in Excel is to learn to create macros. We create and edit various macros. Groups of macros can be combined using the Visual Basic for Applications programming language.

Designing & building spreadsheet models

Designing a model on paper before moving to the computer, specifying the problem in pseudo - language, identifying the variables needed to solve the problem, gathering together any algorithms that we may need to solve the problem, build the sheet.

Data lists

Database applications in Excel. Using Auto filters, advanced filtering, creating a custom filter, finding records, sorting sub-totals etc.

Analyzing scientific data

XY charting, Chi square test, t test, linear regressions, etc.

Validating spreadsheets

Making sure that your spreadsheet gives the correct answers. Testing your spreadsheet on a regular basis for compliance.

Graphic elements in spreadsheets

Using the graphics toolbar, types of graphic files suitable for use in the Excel environment, formatting objects, resizing graphics.

MYOB to Excel

Importing data from accounting packages such as MYOB and QuickBooks. Using Excel to present accounting data in a palatable form.

Excel functions

An overview of the use of functions in formulae using some case studies.

An Introduction to Microsoft Access
Tutor: Brian Hanisch
Tutor: Software tutor
Cost: $250.00

This course introduces you to Access, Microsoft's powerful relational database tool for personal computers. Access is perhaps the most commonly used database in the business world.

The ability to efficiently use databases has almost become a mandatory skill for anyone working in a modern business environment. This course will provide you with the capability and confidence to use Access professionally.

We will work with you and show you how to use the tool to build a simple database application containing several tables. We will demonstrate the use of forms as an aid to put data in and queries to get data out. We will emphasize that Access is a relational database and illustrate how relationships are created between one table and another. This greatly increases the efficiency of database storage and information retrieval.

Click here to play a sample instructional movie from the course.

    Module

     Description

The Database Process

When should you use Access? When not to use it! Thinking about your database requirements. The database features of Excel vs. those of Access. Getting Access to do some work for you by using some databases that others have developed. Copying sample databases to your hard disk. Starting Access and using a previously built database - the North Wind Traders database. Run some forms from the North Wind database. Opening a Contact Management database - one with a menu system. Entering information into an Access database and using data controls at the bottom of a form. Making a more professional contact management database, one which is easy for inexperienced users to use.

Database Components

Some brief comments of the need to plan database applications before you begin. A look at the structure and organization of a database that you will build in this course - a student record database. Data flow in Access applications. Exploring the database window and the 'user interface' using the database application which will be developed in this course. The main parts of an Access database. Selecting and using various Access objects. Moving to 'Design View'. Editing information in a form.

A brief overview of the function and operation of the various objects in an Access database - Tables, Queries, Forms and Reports. Suggested file naming conventions in Access - Leszynski Naming Convention - tbl, frm, rpt, qry. Altering a form in Design View changing the size of a field on a form.

Creating a new database 'from scratch'. Using an in-built wizard to create an Event Management database. Using  an online Wizard. Examining a customized Contact Management database for a software company with a group of customers world wide - PetroSys.MDB.

Some database theory. A discussion of some database concepts and terminology.

Preliminaries

Some steps to take before you start using Access. Configuring Access for ease of use. Inter alia, setting up Access to open files in a particular folder.

Access Tables

Creating a brand new table in Access with a number of fields. Deciding on the type of data to be placed in a field. Field sizes. The value of including a meaningful description when defining fields.  Inserting, deleting and modifying fields in a table. Entering data directly into a table.

Access Forms

A form can be used as a navigational device. Examining a complex form and its relationship to its table. Creating a new form using the form wizard. Alternative methods of creating forms - design view, wizards, Pivot Forms, AutoForm and Chart wizard. Using a form. The importance of data controls. Entering data via a form. Searching through a database using a form - the binocular tool. Setting search criteria. The importance of the 'home' field when searching a database using a form. The value of special keys - PgUp, PgDn etc. Editing forms - using into Design View - moving controls, placing labels on a form, altering the size of labels, placing labels in the header and footer. Using cut, copy paste and undo to speed data entry. Linking database objects to form items. The importance of the toolbox.

Access Queries

Running queries in a sophisticated database, creating new queries, saving and altering queries. Creating a query which uses a parameter. Example: - one query can be used to select all students each of the states of Australia, simply by typing the abbreviation for the state name.

Access Reports

Running existing reports, creating a new report using a wizard, basing the report on an existing query. Setting the sort order in a report. Altering the layout of a report in design view.

Access Relationships

Explaining the advantages of using relational databases. Setting up database keys. Creating two new tables and building relationships between them. Creating forms and queries that depend on relationships. Referential integrity. Designing and building multi-table queries, reports and forms. Adjusting the sort order in a report. Creating combo boxes. A detailed review of database planning.

Future directions - where to from here?

Future directions for your Access programming. Compiling Access databases, creating documentation, testing and validation, importing data from mainframe applications, exporting data to other applications.

Advanced Microsoft Access
Tutor: Software tutor
 This course requires an enrolment key

This course follows 'An Introduction to Microsoft Access'. It builds on the concepts and skills learned in the earlier course, starting with a module on using the Switchboard manager to make your databases more user friendly and moving on to other aspects of improving your database building capabilities.

This course provides a more in-depth look at Access - Microsoft's relational database for desktop computers. We assume that you have taken our course 'An Introduction to Microsoft Access' and now wish to improve your skills. As you are aware, softwaretutor.net courses are a very affordable way to improve your skill level. We must stress that we are unable to offer custom database solutions to your particular problem. For that you need a database consultant who can meet with you face to face and analyze your particular problem.

    Module

    Description

Introduction

Our learning system. Examining a well developed Access database. Revising your understanding of relational databases, the need for planning a database application.

Documentation

Professional database developers make sure that they document their application and keep the documents up to date. Access 2007 has a tool to help manage this and in this module, we explore that tool.

Making your database user friendly

Documenting an existing database and creating a menu system to make management easier and allowing the Access application itself to be hidden from casual users. Exploring the new navigation system in Access 2007 - a replacement for the older switchboard menu system. The navigation pane now conforms to the single document model advocated for Office 2007 products.

Tables

A close look at tables. Data types, hyperlinks, text fields, number fields, yes/no fields, input masks, naming fields, reserved words, null values, order of fields, storing calculated values, descriptions, number that are not numbers, validation rules for fields, validation rules for tables, lookup fields.

Forms

A second look at Forms and their use. Using the Form Wizard. Moving to layout view and reorganizing the appearance of a form. Applying themes to a form. Moving to the Design View of a Form. Using the toolbox to populate forms with objects.

Queries

Select Queries. Sources for Queries. Types of Queries. Editing via Queries, SQL and SQL language Queries, the Query Wizard, calculated fields, editing SQL expressions in calculated fields. Concatenating field names in a Query.

Reports

 

Web Access (Pages)

 

Revisiting Relationships

Some revision from the introductory course. Everything you ever wanted to know about relationships. Please cover the material in this module before any other. Add stuff on tables.

Project charter

Build a project definition with your client. Find out what is required. Mock up the user interface, get lots of feedback before beginning to develop a database.

Database design

Defining your problem. Database design is an iterative process. Create tables, print relationship diagram, test by entering data, refine your design, split tables, test again, document and document some more!

Protecting your data

Backing up, compacting and repair, analyzing the content etc.

Case Study 1

The importance of planning your entire application before you start. We create a database for a company managing the issue of record keeping for safety equipment, licenses and certification and training records.

Case Study 2 

Information in and out. Interacting with a mainframe database holding information on batches of wool which have been scoured. Importing information from Excel, from comma delimited text files, fixed width text files, from another Access database, from other databases, from a HTML file, from XML. Export to other programs, linking to another database.

Case Study 3

School administration. Developing a database for managing enquiries for school enrolments. Includes the development of a project charter.

Case Study 4

Developing a database for managing contact with members of indigenous communities.

Case Study 5

Managing contact between Contract Research Organizations and study site monitors.

Keeping information accurate

Use data type and filed type settings to restrict data. Using input masks to restrict data, use validation rules, using lookup lists. Updating information in tables, deleting information. The FrontPage question and answer database as an example.

Action Queries

Make table, append table, delete table, cross tab, select and SQL queries etc.

Pages and Modules

Creating static web pages, exporting Visual Basic for Applications, Using Visual Basic for Applications (VBA) to create a web page, creating a data access page with AutoPage, creating a data access page using the page wizard, allowing others to analyze data on the web.

Mail Merge & Access

The raw data for the generation of form letters can come from a variety of sources - mainframe computers, specialist databases such as the Bureau of Statistics, databases such as Access, FileMaker Pro, SQL Server, Oracle etc. It is not difficult to obtain that information from those sources in the form of ASCII text files. Excel can load those files expertly and you can use the combination of Microsoft Word and Excel to generate form letters, filtering data as you go if that is what is required. the filtering can also be done in the database before outputting the ASCII text file.

Securing your information

Encrypting and decrypting an Access database, assigning passwords, sharing databases, replicating a database, splitting databases, setting up a workgroup, maintaining a workgroup, preventing changes to a database, securing a database for distribution.

Case Study 7:

Web integration. Using Access to publish web pages. First just HTML, but later publishing to a Microsoft SQL server.

An Introduction to Microsoft Project
Tutor: Software tutor
Tutor: Brian Hanisch
Cost: $250.00

This course introduces you to the classic Windows tool for project management - Microsoft Project. If you want to manage timelines, resources and share information about projects, this is the tool for you.

Project is a software tool used to answer question such as:

  • What tasks must be done to produce the deliverable - what needs to be produced as a result of all the hard work by you and your team?

  • Who will complete these tasks?

  • What is the best way to communicate project details to people who have an interest in the project?

  • When should each task be performed?

  • How much will it cost?

  • What happens if some tasks are not completed as schedules?

Note that good project management does not guarantee the success of every project, but poor project management usually guarantees failure.

Project is a very sophisticated application indeed. Since it is so complex, we will not be able to cover all aspects of it in this 'introductory' course, but after you complete the course, you will be certainly be able to manage some modest size projects.

Here is a link to a typical instructional movie from the course.

 

Syllabus

Module

Description

Stage 1: Project definition

Getting Started with Project (Based on Chapter 1 Chatfield & Johnson)

Understanding the family of Office Project 2003 products, starting Project Standard, identify major parts of the Project Window. Create a new project using a template. A template for building a residential house. Examining various templates, both local and online. Switching from one project to another via Window. Using views to work with project plan details in different ways, use reports to print project plan details. Create a project plan and enter a project's start date, set the working and non working time for a project, enter a project plan's properties.

Preliminaries

Some steps to take before beginning serious work with Project.

Simple Tasks (Chapter 2)

Creating a task list. Entering task information. Estimating and entering how long each task should last. Creating a milestone to track an important event. Organizing tasks into phases. Create task relationships by linking tasks. Recording task details in notes and inserting hyperlinks to World Wide Web content in a project. Checking a project plan's overall duration.

Simple Resources (Chapter 3)

Setting up resources.  Here we set up basic resource information for the people who will work on the project. Set up basic information for the equipment that will be used. Enter basic resource information on the materials that will be consumed as the project progresses. Set up cost information for resources.

Simple Assignments (Chapter 4)

Assigning resources to tasks. Controlling how Project schedules additional resource assignments, assigning material resources to tasks.

Formatting & Printing
 (Chapter 5)

Customizing a view of a project by running a Gantt Chart wizard , adding extra information to a Gantt chart. Adding notes to and drawing on a Gantt Chart.  

Develop your own project

Developing a project definition of your own. Some case studies: A project for Greening Australia - revegetation.

Stage 2: Project tracking and fine tuning

Tracking Progress on Tasks
(Chapter 6)

Saving current values in a schedule as a baseline. Recording progress on tasks through a specific date, recording a task's percentage of completion, entering actual start, finish, work and duration values for tasks.

Fine Tuning Task Details
(Chapter 7)

Adjusting task links to have more control over how tasks are related, applying a constraint to a task, identifying the tasks on the critical path, viewing the critical path, split a task to record an interruption to work, create a task calendar and apply it to tasks, change a task type to control how Project schedules tasks, record deadlines for tasks, enter a fixed cost and specify how it should accrue, set up recurring tasks in the project schedule.

Fine Tuning Resource & Assignment Details

Set up different pay rates for resources, set up pay rates that change over time for a resource, set resource availability to change over time, delay the start of a resource assignment, control how a resource's work on a task is is scheduled over time by using work contours, apply different cost rates for a resource assigned to different kinds of tasks, enter variable consumption rates for material resources.

Fine tuning the Project Plan

Examining how resources are scheduled to work over the duration of a project. Edit a resource assignment to resolve a resource allocation, resolve resource allocations automatically, see detailed and overall project costs, see tasks on the critical path that determine a project's finish date.

Organizing & Formatting Project Details

Sort task and resource data, display task and resource data in groups, filter or highlight task and resource data, create a custom table, create a custom view.

Printing Project Information

Change page setup options for views and reports, print a view, print a report.

Publishing Project Information On Line

Take a snapshot of a Gantt Chart as a GIF image, publish Project information in HTML format, control how Project information is saved in HTML format.

Sharing Project Information with Other Users

Copy and paste data to and from Project, generate a new Office document that contains essential project details and an illustration, use Project to open a file produced in another program, save Project data to other file formats using import/export maps.

Tracking Progress on Tasks and assignments

Update a previously saved baseline plan, record actual work for tasks and assignments, record daily actual work values, interrupt work on a task and specify the date on which the task should start again.

Viewing and Reporting Project Status

Determine which tasks were started or completed late, view task costs at summary and details levels, examine resource costs and variance, use custom fields to create a stoplight view that illustrates each task's cost variance.

Getting a Project Back on Track

Assign additional resources to tasks to reduce task durations, edit work values for resource assignments and replace resources assigned to tasks, assign overtime work to assignments and change task relationships to compress the overall project duration.

Advanced Formatting

Format the Gantt Chart view, format the network diagram view, format the Calendar view.

Customizing Project

Copy a  customized element such as a table from one project plan to another one suing the Organizer, record and playback a simple macro, edit a macro in the Visual Basic Editor, create a custom toolbar.

Measuring Performance with Earned Value

Set a status date and see earned value indicators for schedule performance, see earned value cost performance indicators.

Consolidating Projects and Resources

Create a resource pool to share resources across multiple projects, look at resource allocation across multiple projects, change resource assignments in a sharer plan and see the effects in the resource pool, change a resource's working time in the resource pool and see the effects in the sharer plan, make a specific data nonworking time in the resource pool and see the effects in the sharer plan, create a project plan and make it a sharer plan for the resource pool, manually update the resource pool from a sharer plan, insert project plans to create a consolidated project, link tasks between two project plans.

Planning Work with Project Server

Understanding the components of a Project Server-based enterprise project management solution, create a new project plan based on an enterprise template, assign work resources from an enterprise resource pool based on criteria such as location and availability, publish a plan to Project server after developing it in Project professional.

Tracking Work with Project Server

See how resources report their progress on assignments and non working time through the timesheet in Project Web Access, see how resources report their progress from the Outlook calendar to Project Server via the Outlook in integration add-on, see how project managers approve task changes (such as casual work) from resources in Project Web Access and update project plans in Project Professional, see how executives and other stakeholders can see project status at a glance and drill into the details that most interest them in Project Web Status.

Managing Risks, Issues and Documents with Project Server

Create a risk in Project Web Access and associate it with a project, create an issue, associate it with a project and assign it to someone for resolution, create a document library for a project and upload a document.

Revision

Project is certainly sophisticated software. We have covered a lot of ground. Some revision of the material is perhaps warranted.

An Introduction to Microsoft Publisher
Tutor: Software tutor
Cost: $250.00

Microsoft Publisher is a software tool (a computer application) used to produce 'high quality' publications for printing on a local in-house printer, by a commercial printer or published on the web.

Among other things, the software enable you to:

  • Assemble text and graphics on the document page in a very controlled and precise way.

  • Create documents (pages) of almost any size.

  • Build documents consisting of either single or multiple pages.

  • Apply a common design theme to all pages leading to a consistent and coherent style across all pages in a multi-page document.

  • Edit stories in Microsoft Word.

  • Apply an automatic design check to a document before printing.

  • Package up a document and all the relevant font and images files ready for transport to a commercial printer without the risk of leaving out a critical element.

Publisher allows you to have much more control over the flow of text in single and multi-page documents than does a word processor such as Microsoft Word. In Word, text and graphics on a page often become scrambled or misaligned when you move material around.

Publisher is especially useful when material on a page within a document needs to be related to (link with), columns on other pages in the same way that articles in a magazine or newspaper start on one page and continue on pages toward the back of the magazine.

In short, Publisher is used to create high quality brochures, catalogues, stationery, magazines, flyers, annual reports, advertisements, greeting cards etc. It is possible to use Publisher as a mail merge tool.

The list of uses for Publisher is limited only by your own creativity.

Click here to play a sample instructional movie from the course.

Module

Description

The Process of Desk Top Publishing

A first look at Publisher. Creating your first 'pieces' of work - a poster for a motorcycle race meeting and a two page product information document for a distributor of medical devices. The importance of using templates is emphasized, especially ones that you create yourself. The advantages and disadvantages of Desk Top Publishing (DTP) are discussed. We comment on the need to learn to use additional tools such as vector drawing programs, image editors, scanners and digital cameras for successful DTP. The benefit of producing PDF files from Publisher is also illustrated with examples created by some of our students.

Preliminaries

Some steps to take before beginning serious work with Publisher. Installing Publisher  and some type of Type Manager. Installing a print driver. Installing a new Windows font. Postscript type 1 fonts. Check out your printer, making sure that you can accurately define the print area. Setting Publisher preferences - units of measure, save times, toolbars displayed on startup etc.  Creating a suitable set of folders for Publisher projects. Getting to know key people at your local print shop. Computer requirements for successful use of publisher.

The Publisher Interface

The arrangement of the Publisher screen. Title bar, drop down menus, standard, formatting objects and measure toolbars. Surveying the tools available from drop down menus. The importance of fonts in determining the look and feel of a document. Understanding sans serif and serif fonts. Using boundaries and guides as an aid to layout. Page layout tips, columns and rows. Using more complex wizards. Using the catalogue. Applying colour schemes and layout themes to documents. Proof reading vs. copy editing. Installing freeware fonts.

Publisher in Action

You prepare for a 'real' Publisher job. Before beginning, we illustrate the value of building a catalogue of design ideas for documents. Having sketched our design ideas, we then build two publications from start to finish - a label for the spine of an A4 folder and a brochure advertising a service. Each job is then printed; the label on an 'in-house' printer and the brochure is packaged ready to send to a commercial print house. We examine the Pack and Go feature.

Entering information

Creating Artwork. Planning publications.  A case study - building an three fold advertising flyer. Checking Publisher designs. Graphic file formats and their use in Publisher documents. Personal information sheets, publishing a calendar. Using a wizard to create a newsletter. Building a newsletter from scratch - (a newsletter for a motorcycle club). Text Frames, manipulating graphics, editing stories, linking frames and creating compound multi page documents. Creating and inserting diagrams, charts and tables into Publisher documents.

Editing

Changing information in Publisher documents. Working with frames, the value of the measurement toolbar. Editing text in situ. Formatting text, the Autofit option. Editing text in Microsoft word. Changing the properties of a text frame. Adjusting word wrap. Mastering the various editing commands. Grouping and ungrouping objects. Automatically aligning images and frames.

Using text styles

Speed up your publications by using text styles. Examining various styles used in a restaurant menu. Saving a document as a template.

Print  Output

Getting information out. Printing a document. Using the 'Pack and Go' feature to send material to a commercial printer. Packaging materials for a copy centre.

Tips and Tricks

Design ideas. Things to do in Publisher and things not to do. guidelines from a commercial publishing house (for a web offset newspaper).

An Introduction to Microsoft FrontPage
Tutor: Software tutor
Cost: $250.00

FrontPage, Microsoft's powerful tool for creating and maintaining web sites is a premier tool for creating and maintaining web sites. If you can use Microsoft Word, you can develop web sites using FrontPage.

FrontPage Webs

Webs created using FrontPagecan either be be mounted on the Internet or hosted on a computer 'in-house' (an Intranet). We show you how to use FrontPage to design and create web sites that focus on your (or your organization's) individual needs. By the end of the course, you will be able to create (and maintain) your web site without the need to rely on others to update content.

Syllabus:

An Introduction to Microsoft PowerPoint
Tutor: Software tutor
Cost: $250.00

This course is designed to enable you to master the preparation and delivery of presentations (talks to groups) using PowerPoint; a tool included in the Microsoft Office suite of software.

You will create several presentations in the course and cover issues such as preparing and formatting slides, altering the content of slides, rehearsing a talk and changing the order of slides in a talk.

We will also show you how to prepare graphics (illustrations) of your own and import these into the PowerPoint environment so that your slides have the look and feel appropriate to your message.

Syllabus: