Session 3 - Relationships: More proof that Access is awesome

Activity 1

If you haven't already done so - add around 10 members to your Members table and around 30 products (Movies, Games, Consoles etc.) to your Products table.

Creating our Transaction table

The idea of our database is to keep our Members details, our Products details, and to keep track of borrowings!!

For this to happen, we need another table that will create a record every time an item in rented out.

What do we need in this table?

Date Borrowed (Date)
Date Due (Date)
Date Returned (Date)
Product barcode (Short Text)
Member Number (Short Text)

Activity 2

Create the Rentals Table (as per above)

Creating our Rentals Form

Setting up the Relationships

The Rentals form is the one that we will use every time a member borrows an item. Before we can create the form, we need to set up a relationship between the Rentals table and the Members and Product tables.

From the Database Tools tab, select Relationships

You will then be able to place the 3 tables on the relationship canvas.

To create the relationship, Drag the Membership Number from the Members Table on to the Member Number in the Rentals Table

 

Once you click Create, you will see the line connecting the 2 tables

 

Repeat the process with the barcode field in the Products table and the Product Barcode in the Rentals table

If you have done it correctly, it should look similar to the above screen.

Save and close the relationship window

Activity 3

Create the Relationship between your 3 main files.

Create the Form

Design a Blank Form using the Members table as the data source.

Add some basic information - enough to identify the member so perhaps address and mobile number

Use the Subform tool to draw the subform underneath the member details and follow the wizard prompts

I like to use the Membership number as a drop down box to select a member from.

Use the Combo Box tool and follow the wizard prompts

Test out the form.

Activity 4

• Create the Rentals Form/subform
• Test the form by selecting a member and "lending" a product to them. At this stage you will need to make a note of a valid product barcode

** Advanced Exercise - On your own, try and do the following:

• Add a Membership number combo box at the top of the form to select the customer from a list
• change the Barcode number in the subform to also be a combo box to select the correct movie from a list
• Change TAB order in the subform so that the membership number is first and Date returned last.
• Create a Form for RETURNS. This should have a drop down list to select the barcode and display the Loans in a subform. You can then scroll through the list and select the last one - it should have returned date as blank.

Class Exercise - Manor House Animal Rescue

Our practice exercise currently has 1 Table - our main table with the information about each animal. We need to create the table that keeps track of all activities that relate to each animal in our care.

Actions Table

What information do we need to keep in our Actions Table?

*** Activity on Whiteboard ***

Activity 5

• Add some animals to your main table - say 10
• Add the Actions Table to your Manor House Animal Rescue database
• Create a Form/Subform to record Actions for our Animals

If you would like to watch the video on YouTube, click here

OR you can watch it below

Creating a Menu

The next stage of our little application is to build a menu that will tie it all together.

From the Create Ribbon, choose Blank Form. Go into Design View and start by adding a label for the main heading.

Once you have the heading, you can add command buttons to open the forms and eventually print reports etc.

Here are the steps to have a button to add and edit members.

You can pretty it up by including a background colour and different colour text etc. As this is our main menu, it would also probably contain our company logo.

Activity 6

Create a menu form for our Video Vidiots system with the following buttons.

• Open your Member Form
• Open your  Products Form
• Open your Genre Form
• Open your Rentals Form
• Open your Returns Form

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 7

• 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 8

• 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.

Session 2 - Access forms and Macro Basics

Let's start this week with the class activity that we didn't complete last week - just to refresh all the wonderful goodness of Access.

Class Exercise

Manor House Animal Rescue is a small not for profit organisation operating in the Bass Coast region of Victoria. The organisation rescues and rehabilitates unwanted or lost animals - mostly dogs, cats and chickens, but some of the local injured wildlife also occasionally come into their care.

Manor House Animal Rescue would like a database that will allow them to keep a record of all of the animals in their care, details of any actions taken with the care of the animals, and details of rehoming or release.

Where to Start!!

• How many tables will we need in our database?
• What fields, field types will we need in each table?
• What forms will we need for entering data?
• What reports will we need?
• What items will we need on the menu - what actions do we want people who are working at the centre to be able to do?

To start with, we will need to create 2 tables:

• An Animals detail table which will contain the basic identifying information about each animal.
• A "transaction" file that will keep track of any actions that are taken with each animal

These will have a ONE-To-MANY relationship as there could be multiple actions taken against each animal in care.

1. Create the Manor House Rescue database
2. Create the Animals Table - See Whiteboard for table details
3. Create a Form that can be used to add animals

More on Access

Last week we used Access 2013 to create 2 tables for our Video Vidiots store. We created a Members Table and a Products table. We then designed customised forms for both tables and added 2 members to the members table. You all coped very well with the task, even those that had no or very little experience with Access.

This week we will continue to build on this database by adding more features to our forms as well as another couple of tables.

Adding Buttons to our Form

The first task we will do today is to add some buttons and simple Macros to our forms.

We want to add buttons to perform the following tasks:

• Add
• Delete
• Next Record
• Previous Record
• Find
• Exit (Close Form)

Start by opening up the Members form in Design View.

For this task, we will use the Button tool. This is what my form looks like before the buttons.

 

Click on the Button tool to select it. Draw a small rectangle towards the bottom of the screen and follow the prompts.

 

 

 

 

The Buttons will have generic Commandxx as the name. You can give the buttons a more meaningful name by clicking Next at the above wizard dialog box and then change the name. Then on the final option box you can click on Finish. This stage is not compulsory, but can make it easier to identify the button if you wish to change the properties.

 

Use the same process to add the other buttons.

Use the Button tool, draw a rectangle, select the Delete Record action and if you want, give the button a meaningful name

To add the button to go to the next record, use exactly the same process except choose Go To Next Record from the Record Navigation category.

Just to be different, I have used a picture for this button

 

And here is my form now with 3 buttons on it.

Change the View to form view to test the buttons.

Activity 1

Add the suggested buttons to your Members Form and your Products Form.

Data Validation with Forms

There are a number of ways to validate your data as it is being entered. You can add validation into the design of the table, or you can add it to the form. Today, we will add it to the form.

Setting a Default Value

A simple way to make sure that a field has data in it is to set a default value. This is great if you think that many of your records will use the default value.

As we are a video store, mostly renting to people who live in the local area, the state will most of the time be Victoria. So why not set that as default.

Open your Member Form and go into Design View

Select the State field

Click on the Properties and under the Data TAB, type in Victoria for the Default Value

When you go to add another customer, you will see that the State field will have Victoria already typed in.

Activity 2

Make Victoria the default value for state on your Members form

Using a Combo Box

That's all we really need to do to the Members File. The next "tweak" we will make is to be allow the Genre to be selected from a list. We can do that by including the Genres in as items in the list, or by linking a table that contains the genres. As this is an ADVANCED class, we will do the latter!!

So the first thing is to create the table - we will call it Genres (yeah I know, not very original). The Genres table contain 2 fields, the Genre and a description.



Note: Remember, to create a table, look under the Create section on the ribbon, and change to Design View. You could also create a form for ease of data entry and consistency!!

Here is what mine looks like. Two fields with Genre as a Primary Key.

And with a couple of Genres added

So now to link it to our Form. Load the Products form and go into Design View.

Select and remove the existing Genre field.

Select the Combo Box tool and draw on your form where the Genre field was located.

Follow the prompts from the wizard. Firstly you want to get values from an existing table. The reason we do this rather than type in a list is so that we can easily add more Genres by adding them to the table and not having to go into design view and change the property of the field.

If you use the TAB key to navigate your form, you should also change the TAB order as this field will now be at the end.

 

Now to test the Form!!

and there we have our drop down list using a file that can be easily updated and integrated into our end product.

Activity 3

Create the Genres Table and add a few Genres

Create a simple Genres Forms


Create the Genre drop down list on your Products Form

Try adding another Genre using your form and retest the Products form.

Combo Box list

Because the Ratings are set values, we can use a combo box to validate the data, but we don't need to link it to another table.

Open the Products form in Design View (if not already)

Click on the Rating field to remove it.

Drag a combo box to the same location.

Choose the second item - I will type in a list of items.

You may need to check the TAB order and make any changes.

Activity 4

Add a Combo Box for Rating

Use your Members Forms to new Members - you should have 10 or so members in your table

Use your Products Form to add 30 Products to the table