Applications Session 4 - Keeping up your relationships

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