Applications Session 5 - But Wait; there's more

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]