Details on Data Relationships

Diagramming a Relationship

Introduction

1. Start Microsoft Access and create a Blank Database
2. Set the name of the database as Bethesda Car Rental2 and click Create
3. On the default table, click Add New field and type Category
4. Save the table as Categories and switch it to Design View
5. Complete the table as follows:
    

   Field Name	Data Type	Caption	Field Size	Format
   CategoryID (Primary Key)	AutoNumber	Category ID
   Category	Text		50
   Daily	Number		Double	Fixed
   Weekly	Number		Double	Fixed
   Monthly	Number		Double	Fixed
   Weekend	Number		Double	Fixed

6. Save the table and close it
7. On the Ribbon, click Create and, in the Forms section, click Form Design
8. Save the form as Rental Rates and change the following characteristics in the Properties window:
   Record Source: Cars
   Caption: Rental Rates
   Default View: Continuous Forms
   Navigation Buttons: No
9. Design the form as follows (do not try to match the fonts; use whatever font you have on your computer):
    
   
10. Save the form and switch it to Form View
11. Create the following records:
     

    Category	Daily	Weekly	Monthly	Weekend
    Economy	34.95	28.75	24.95	24.95
    Compact	38.95	32.75	28.95	28.95
    Standard	45.95	39.75	35.95	34.95
    Full Size	50.00	45.00	42.55	38.95
    Mini Van	55.00	50.00	44.95	42.95
    SUV	56.95	52.95	44.95	42.95
    Truck	62.95	52.75	46.95	44.95
    Van	69.95	64.75	52.75	49.95

12. Close the form
13. To create a new table, on the Ribbon, click Create and, in the Tables section, click the Table Design
14. Create the table with the following fields:
     

    Field Name	Data Type	Caption	Field Size
    CarID (Primary Key)	AutoNumber	Car ID
    TagNumber	Text	Tag Number	20
    Make	Text		50
    Model	Text		50
    CarYear	Number	Year	Integer
    CategoryID	Number	Category	Long Integer
    Doors	Number		Byte
    Picture	OLE Object
    Condition	Text		50
    Available	Yes/No
    Notes	Memo

15. Click Condition and, in the bottom section of the table, click the Lookup tab. Change the properties as follows:
    Display Control: Combo Box
    Row Source Type: Value List
    Row Source: "Excellent";"Good Shape";"Needs Repair";"Must be Retired"
    Bound Column: 1
    Column Count: 1
    Column Widths: 1"
    List Width: 1"
16. Save the table as Cars and close the table
17. Create a new table in Design View as follows:
     

    Field Name	Data Type	Caption	Field Size
    EmployeeID (Primary Key)	AutoNumber	Employee ID
    EmployeeNumber	Text	Employee #	20
    FirstName	Text	First Name	50
    LastName	Text	Last Name	50
    Title	Text		100
    Notes	Memo

18. Save the table as Employees and close it
19. Create a form for the Employees table, save it as Employees, and design it as you see fit. Here is an example:
     
20. Save the form and create the employees
21. Close the form
22. Create a new table in Design View with the following fields:
     

    Field Name	Data Type	Caption	Field Size
    CustomerID (Primary Key)	AutoNumber	Cutomer ID
    DrvLicNumber	Text	Driver's License #	50
    FullName	Text	Full Name	100
    Address	Text		100
    City	Text		50
    State	Text		100
    ZIPCode	Text	ZIP Code	20
    Notes	Memo

23. Save the table as Customers and close it
24. Create a form for the Employees table, save it as Employees, and design it as you see fit. Here is an example:
     
25. Save the form
26. Create some customers before closing the form
27. Create a new table in Design View with the following fields:
     

    Field Name	Data Type	Caption	Field Size	Other Properties
    RentalOrderID (Primary Key)	AutoNumber	Rental Order ID
    EmployeeID	Number	Processed By	Long Integer
    CustomerID	Number	Processed For	Long Integer
    CarID	Number	Car	Long Integer
    CarCondition	Text	Car Condition	50
    TankLevel	Text	Tank Level	50
    MileageStart	Number	Mileage Start	Integer
    MileageEnd	Number	Mileage End	Integer
    TotalMileage	Number	Total Mileage	Integer
    StartDate	Date/Time	Start Date
    EndDate	Date/Time	End Date
    TotalDays	Number	Total Days	Integer
    RateApplied	Number	Rate Applied	Double	Format: Fixed
    TaxRate	Number	Tax Rate	Double	Format: Percent Default Value: 7.50
    OrderStatus	Text	Order Status	50	Default Value: "Unknown"
    Notes	Memo

28. Click TankLevel and, in the bottom section of the table, click the Lookup tab. Change the properties as follows:
    Display Control: Combo Box
    Row Source Type: Value List
    Row Source: "Empty";"1/4 Empty";"Half";"3/4 Full";"Full"
    Bound Column: 1
    Column Count: 1
    Column Widths: 1"
    List Width: 1"
29. Click OrderStatus and, in the bottom section of the table, click the Lookup tab. Change the properties as follows:
    Display Control: Combo Box
    Row Source Type: Value List
    Row Source: "Unknown";"Car On Road";"Order Completed";"Order Reserved"
    Bound Column: 1
    Column Count: 1
    Column Widths: 1"
    List Width: 1"
30. Save the table as RentalOrders and close it

The Relationships Diagram

Tables and fields relationships can be created and managed in a special window called the Relationships window. To display it:

• If no table is currently opened, on the Ribbon, you can click Database Tools. In the Show/Hide section, click the Relationships button
• If a table is currently displaying, on the Ribbon, you can click the Datasheet tab and, in the Relationships section, click the Relationships button

After clicking one of those:

• If no relationship exists among the tables in the current database, the Show Table dialog box would come up, asking you to select the tables whose relationship(s) you want to create. To add a table, select it, click Add and click Close
• If at least one relationship has been created between two tables, the Relationships window would come up and display that relationship or the already existing relationships

When you are working on the relationships of your tables, a window with a tab labeled Relationships displays in Microsoft Access. Also, the Ribbon is equipped with a tab labeled design and that includes two sections:

The Design tab of the Ribbon provides various tools to assist you with creating and managing the relationships. For example, if the Show Table dialog box has been closed and if you want to show it:

• You can click the Show Table button on the Ribbon
• You can right-click the body of the Relationships window and click Show Table...

You can create a relationship only between two tables that are present on the Relationships window. This means that even if a table is part of your database and you want to link it to another table (of your database), if the table has not been added to the Relationships window, you cannot create or manage its relationship to another table. Of course, there are other ways you can create relationships without using the Relationships window but the Relationships window gives you detailed means of creating and managing relationships.

To establish a relationship that does not yet exist between two tables, you can drag the primary key from the parent table to the foreign key of the desired table. If you drag and drop accurately, the relationship would be acknowledged and you can just click Create to make it formal. If you dropped the primary key on the wrong field, you would have time to select the appropriate fields in the Edit Relationship dialog box.

After working with the Relationships window, you can close it by clicking its Close button . You would be asked to save it in order to keep the relationship(s) created.

Practical Learning: Establishing Tables Relationships

1. The Bethesda Car Renatal3 database should still be opened.
   On the Ribbon, click Database Tools
2. In the Show/Hide section, click the Relationships button . The Show Table property sheet comes up:
    
   
3. Click Categories once and click the Add button
4. Double-click Cars
5. On the Show Table dialog box, click the Close button
6. To add another table as if we forgot it, right-click an empty area in the Relationships window and click Show Table…
7. In the Show Table dialog box, click Customers
8. Press and hold Shit
9. Click RentalOrders and release Shift
10. Click Add and click Close
11. Drag the CategoryID field from the Categories table and drop it on top of the CategoryID field in the Cars table:
     
    
     
    The Edit Relationship dialog box would come up
     
    
12. Now you have a line relating these two tables.
    Drag any field from the Cars table and drop it on top of any field in the RentalOrders table if you missed the target
     
    
13. Once again, the Edit Relationship dialog box comes up.
    On the dialog box, under the left Table/Query, click the field that is selected to display its combo box. In that left combo box, select CarID and press Tab
14. Under Related Table/Query, click the field that is selected and click the arrow of its combo box and select CarID
     
     
15. Click Create
16. In the same way, drag EmployeeID from the Employees table and drop it on EmployeeID in the RentalOrders table
17. Click Create
18. Drag CustomerID from the Customers table and drop it on CustomerID in the RentalOrders table
19. Click Create
     
    
20. On the Relationships section if the Ribbon, click the Close button
21. When asked whether you want to save, click Yes

Introduction

A sub-datasheet is a means of displaying the dependent records of a parent record on a datasheet. Obviously to make this possible, each record on a table must be configured to have child records. In other words, there must be a table containing a foreign key so that the child table can be, or has been, connected to the current table. For example, imagine that, in a hotel application, various customers have previously rented some rooms and sometimes you want to see the records related to a particular room.

Once a relationship has been established between records, when you open the parent table in Datasheet View, each record would appear with a + button to its left:

This means that Microsoft Access is configured to recognize relationships and apply them to show the sub-datasheet.

By its definition, a sub-datasheet allows you to view the related records by clicking the + button. This would expand that record and display its related records. You can expand just one record by clicking its + button or a few records but clicking the + buttons of the desired records. Here is an example:

You can also expand all records. To do this, open the table in Datasheet View. In the Records section of the Home tab of the Ribbon, click More -> Subdatasheet -> Expand All.

When a record is expanded, it displays a - button to its left. After viewing a record, to collapse it back, you can click its - button. You can do the same for any other record. You can also collapse all records. To do this, open the table in Datasheet View. In the Records section of the Home tab of the Ribbon, click More -> Subdatasheet -> Collapse All.

Practical Learning: Introducing Subdatasheets

1. Start a new Blank Database
2. Set the name of the database to Video Collection3 and click Create
3. Close the default table without saving it
4. To create a new table, on the Ribbon, click Create and, in the Tables section, click the Table Design
5. Set the name of the first field to ActorID and set its Data Type to AutoNumber
6. While the field is still selected, in the Tools section of the Ribbon, click the Primary Key button
7. Set its Caption to Actor ID
8. In the upper section of the table, under ActorID, set the name of the second field to Actor with the Data Type as Text
9. Set the last field as Notes with a Memo as Data Type
    
   
10. To switch the table to the other view, on the Ribbon, click the View button
11. When you are asked whether you want to save the table, click Yes
12. Type Actors and press Enter.
13. Press Enter and enter the following names of actors:
     

    Actor ID	Actor
    1	Eddie Murphy
    2	Tom Cruise
    3	Ving Rhames
    4	Mel Gibson
    5	Charlie Sheen
    6	Jaime Pressly
    7	Sheryl Lee Ralph
    8	Daryl Hannah
    9	Joe Don Baker
    10	Victoria Rowell
    11	Grant Shaud
    12	Kevin McCarthy
    13	Charles S. Dutton
    14	Mia Kirshner
    15	Lane Smith
    16	Randy Quaid
    17	Philip Seymour Hoffman
    18	Delroy Lindo
    19	Rene Russo
    20	Gary Sinise
    21	Matthew Broderick
    22	Jean Reno
    23	Michael Douglas

14. Close the table
15. Create a new table in Design View with the following fields:
     

    Field Name	Data Type	Description
    DirectorID (Primary Key)	AutoNumber	Automatic number
    Director	Text	Name of a director such as "Mark Lynn" or directors as a group of such as "The Hughes Brothers"
    Notes	Memo	Observations about the director or group of directors

16. Save the table as Directors and switch to Datasheet View
17. Enter some directors as follows:
     

    DirectorID	Director
    1	Roland Emmerich
    2	Renny Harlin
    3	Tony Scott
    4	Ron Howard
    5	Sanjay Leela Bhansali
    6	Eddie Murphy
    7	J. J. Abrams
    8	Jonathan Lynn
    9	Mel Gibson
    10	Steven Spielberg
    11	Joel Gallen
    12	Oliver Stone

18. Create a new table in Design View with the following fields:
     

    Field Name	Data Type	Caption
    CategoryID (Primary Key)	AutoNumber	Category ID
    Category	Text	Category
    Notes	Memo

19. Save the table as Categories and switch it to Datasheet View
20. Enter a few categories on the table:
     

    Category ID	Category
    	Biography
    	Documentary
    	Cartoon
    	Fitness – Aerobic
    	Comedy – General
    	Comedy - Parody
    	Comedy – Police
    	Comedy – Politic
    	Comedy – War
    	Drama – General
    	Drama – Police
    	Drama – Politic
    	Drama – War
    	Sci-Fi – General
    	Sci-Fi – Comedy
    	Sci-Fi – Police
    	Sci-Fi – Politic
    	Sci-Fi – War
    	Adventure
    	Hindu
    	Religious

21. Close the table

Creating a Sub-Datasheet

As we have seen so far, to have a relationship between two tables, you must create a primary key in one table and the corresponding foreign key in another table. You can then establish a relationship between both tables in the Relationships window. As stated already, Microsoft Access can take it upon itself to show the related records. You do not have to establish a relation first in order to take advantage of the sub-datasheet effect. If you have created two tables, one with a primary key and another with a foreign key, you can create the sub-datasheet yourself.

To create a non-existing datasheet, open the table with the primary key in Datasheet View. In the Records section of the Home tab of the Ribbon, click More, position the mouse on Subdatasheet, and click Subdatasheet... This would open the Insert Subdatasheet dialog box. In the list of tables (or queries), you must click the table (or query) that has the foreign key that relates to the primary key of the current table. The names of the primary key and the foreign key would appear in the combo boxes. Once you click OK, Microsoft Access would take care of configuring the subdatasheet.

If a relationship has been established and a subdatasheet exists in a table but you do not want the subdatasheet to show anymore, you can remove it. To delete a subdatasheet, open the table with the primary key in Datasheet View. In the Records section of the Home tab of the Ribbon, click More -> Subdatasheet, and click Remove.

Practical Learning: Creating a Sub-Datasheet

1. Create a new table in Design View with the following fields:
    

   Field Name	Data Type	Caption	Field Size
   VideoID (Primary Key)	AutoNumber	Video ID
   Title	Text		120
   DirectorID	Number	Director	Long Integer
   CopyrightYear	Number	© Year	Integer
   Length	Number		Integer
   CategoryID	Number	Category	Long Integer
   Rating	Text		20
   Notes	Memo

   (To get the © character, you can open Microsoft Word, type (c) then select and copy it. Then paste it in the Caption property of the table field)

2. Save the table as Videos and switch it to Datasheet View
3. Enter a few videos:
    

   Video ID	Title	Director	© Year	Length	Category	Rating
   1	Distinguished Gentleman (The)	8		112	8	R
   2	Wall Street	12	1987	126	10	R
   3	Ransom	4		121	11	R
   4	Not Another Teen Movie	11	2005	100	6	Unrated
   5	Harlem Nights	6	1989	116	10	R
   6	M:i:III	7	2006	125	19	PG-13
   7	Devdas	5		175	20	Unrated
   8	Passion of the Christ (The)	9	2004		21	R
   9	Platoon	12	1986	120	13	R
   10	Day After Tomorrow (The)	1	2004	123		PG-13
   11	Beautiful Mind (A)	4	2001	135		PG-13
   12	Godzilla	1	1998	139		PG-13

4. Save the table and close it
5. From the Navigation Pane, double-click Directors: Table to open it
6. On the Ribbon, click Home. In the Records section, click More -> Subdatasheet -> Subdatasheet...
7. In the Insert Subdatasheet dialog box, make sure the Tables tab is selected. In the list box, click Videos and notice that DirectorID has been selected in the combo boxes:
    
   
8. Click OK
    
   
9. A message box information informs you and asks whether you want to create a relationship between both tables. Read it and click Yes
10. Click a + button and notice the related records. Close the Directors table
     
    
11. When asked whether you want to save, click Yes

Introduction

At this time, we know that there is useful functionality to creating relations between tables as they allow the user to select existing information instead of typing it. This flow of information brings up issues about what happens if data that exists in a parent table gets deleted while such information has been made available to another table.

When manipulating data that is in a relationship, it is very important to make sure that data keeps its accuracy from one table or source to the other. To accomplish that goal, some rules must be established to “watch” or monitor the flow of information between two tables. Data or referential integrity is used to check that two tables are related through one (sometimes more than one) field on each table used as the primary key and the foreign key, data entered in the foreign key of a child table must exist in the parent table, otherwise it would be rejected. Only two fields of the same data type are used to establish a relationship between two tables, the tables involved in the relationship belong to the same database.

Cascades on Related Records

After creating a legitimate relationship between two tables, you must make sure that when data changes in the parent table, this change is reflected in the child table. For example, if a bank customer changes her last name after getting married or after a divorce, you should be able to change her name in one object (table) and the related objects, such as the one used to process her transactions would receive the changes without your having to make the change on each object (table). In the same way, when data is deleted, the objects that are related to it must also have that data deleted.

To enforce the rules of data integrity, Microsoft Access provides three check boxes in the Edit Relationship dialog box. First, if you want Microsoft Access to monitor data flow, you can click the Enforce Referential Integrity check box. This would make available two check boxes.

The One-To-Many Relationship

As mentioned already, a relationship between two tables allows one table, the parent, to make its information available to another table (the child). Because in this case the user is asked to select information, it is likely that the same record on a parent table can be tied to various records in the child table. For example, one customer at a bank can deposit an amount of money today. The same customer can make another deposit tomorrow and even another deposit next month. In such a case, the relationship between the tables would show various entries of the same customer’s account number in the object (table) used to deposit money but with different transactions. This type of relationship is known as one-to-many because one entry in the parent table can result in many entries in the child table.

To create a one-to-many relationship, check all three referential integrity check boxes and click Create. The parent table would have a 1 sign on its side of the joining line. The child table would have the infinity symbol on its side of the joining line.

Practical Learning: Managing Referential Integrity

1. Re-open the Bethesda Car Rental2 database that you had created earlier
2. On the Ribbon, click Database Tools and, in the Show/Hide section, click Relationships
3. Right-click the line between Categories and Cars. Click Edit Relationship...
    
   
4. In the Edit Relationship dialog box, click the Enforce Referential Integrity check box
5. Click the two check boxes under it
    
   
6. Click OK.
   Notice the 1 and the ∞ symbol
7. Double-click the line between Cars and Rental Orders
8. In the Edit Relationship dialog box, click the Enforce Referential Integrity check box and click the two check boxes under it
9. Click OK
10. Do the same for the other two remaining lines
     
    
11. Save and close the Relationships window

Although one-to-many is the most common type of relationship applied to records of a table, in some databases, you may need to create a relationship in which many records from one table A can have many related records in another table B and vice versa. This type of relationship is known as many-to-many. For example, in our Video Collection database:

• It is possible to have one video that has many actors. In this case, if we had created a field to receive actors in the Videos table, we would enter many names of actors in that one field but this type of database would not be professional
• At the same time, one actor can have participated in many videos. In this case, if we had created a field to enter the titles of videos in the Actors table, the field would have too many entries, making it unprofessional

To implement this type of relationship, you can create what is called a junction table. A junction table is a table whose main purpose is to bring together fields from other tables, creating a type of cross relationship for the necessary fields:

A junction table contains at least three fields. The first field, almost less engaged, is used as the primary key, the same type of field almost every table has. The other fields hold data that would originate from other tables.

You can create a junction table either in Datasheet View or in Design View:

• To create a junction table in Datasheet View, you use the Lookup Column menu that would call the Lookup Wizard. From the Lookup Wizard, select the table that holds the desired field, then select the field itself, and click Finish. After adding the first column, repeat the same steps for each needed field
• To create a junction table in Design View, set the field's data type to Lookup Wizard and proceed the same way you would for the Lookup Column

You can also switch to either view to create a junction table. In other words, you can create one field in one view and create the other field in the other view.

Practical Learning: Creating Junction Tables

1. Open the Video Collection3 database you started earlier
2. To create a new table, on the ribbon, click Create and, in the Table section, click Table Design
3. Type VideoActorID as the name of the first field
4. Set its Data Type to AutoNumber
5. Right-click the VideoActorID name and click Primary Key
6. Complete the table with the following two fields:
    

   Field Name	Data Type	Caption	Field Size
   VideoActorID (Primary Key)	AutoNumber	Video Actor ID
   VideoID	Number	Video	Long Integer
   ActorID	Number	Actor	Long Integer

7. Save the table as VideosAndActors
8. Switch it to Datasheet View
9. Enter the records as follows:
    

   VideoActorID	VideoID	ActorID
   1	1	1
   2	3	4
   3	2	23
   4	4	6
   5	1	7
   6	9	5
   7	6	2
   8	4	14
   9	1	9
   10	2	8
   11	3	19
   12	1	10
   13	6	17
   14	3	20
   15	1	11
   16	4	16
   17	3	18
   18	1	15
   19	12	21
   20	6	3
   21	2	5
   22	1	13
   23	5	1
   24	12	22
   25	1	12

10. Close the table

The One-to-One Relationship

A one-to-one relationship is the type of junction between two tables A and B so that one record in table A can have only one corresponding entry in table B and vice versa. Because this is similar to one table of records, this type of relationship is hardly used since you can as well simply create one table.

Data Joins

Introduction

When creating relationships among tables, we were selecting the primary key of one table and the foreign key of a dependent table to join them. Once such a relationship is created, you can create a query that combines both tables to create a set of records, also called a record set, that would include either all records or isolate only the records that have entries. For example, imagine you have created a Persons table as follows where the Gender of a record is selected from a lookup field:

Suppose you want to create a query that includes the persons of this table and their genders. A question that comes is mind is: Do you want to create a list of only people who can be recognized by their gender, or do you want the list to include everybody? This concept leads to what are referred to as joins of queries. There are two ways you can get such queries: you can prepare the relationship between two tables to be aware of this type of relationship or you can directly create it when designing a query.

Inner Joins

When building a query, you select fields and ask Microsoft Access to isolate them as being part of the query. Most of the time, you will want only fields that include a type of validation of your choice (a criterion). An inner join is the kind of query that presents only fields that have matching entries on both tables of a relationship. For example, from the above table, if you want to create a query that includes only persons whose records contain the gender:

You would create it as an inner join. To specify that a relationship is inner join, after creating the relationship, while in the Relationships, you can display its Edit Relationship dialog box and click Join Type… The Join Properties dialog box presents three options that allow you to define the direction of the relationship between the tables.

To specify an inner join in a SQL statement, you include INNER JOIN in the statement.

Practical Learning: Creating Inner Join Relationships

1. The Video Collection database should still be opened.
   On the Ribbon, click Database Tools. In Show/Hide section, click the Relationships button
2. If either of the Videos or the Directors table is not displaying, right-click an area in the Relationships window and click Show Tables...
   In the list of tables, double-click the missing table(s) (Directors and/or Videos) and click Close.
   Click the joining line between the Directors and Videos
3. In the Tools section of the Ribbon, click Edit Relationships
4. After making sure that DirectorID is selected in each combo box, click the Enforce Referential Integrity button and click the two check boxes under it
5. Click the Join Type button.
   In the Join Properties dialog box, click the first radio button (it should be selected already)
6. Click OK twice

Outer Joins

The queries we have used so far and that were based on related tables allowed us to get only the fields that had entries based on the established relationships. Fields that did not follow the rules were excluded. Instead of excluding fields, the SQL allows you to create a query that includes all fields, not just those that follow rules, as long as the records are part of either table. Such a query is referred to as outer join. To manage the result of this type of query, the SQL considers the direction of a relationship.

When creating relationships, we learned to drag a primary key from one table, the parent, to a dependent table, the child. In this type of relationship, the table (or query) that holds the origin of the relationship is referred to as the Left table. The other table is referred to as the right table. Based on this, there are two types of outer joins: the left join represented in SQL as LEFT JOIN and the right join represented by RIGHT JOIN.

As done with the inner join relationship, the left and right joins can be prepared in the Relationships window on tables. If the query has already been created and you want to change its direction, you can change it in the Design View of the query.

Practical Learning: Creating Outer Join Relationships

1. The Video Collection3 database should still be opened with the Relationships window displaying.
   In the Relationships button, double-click the joining line between Directors and Videos
2. In the Edit Relationship dialog box, click Join Type
3. In the Join Properties dialog box, read all options then click the second radio button
   
    
4. Click OK and OK
5. In the same way, complete the relationships of the Video Collection database (when the relationship does not exist, you must create it)
    
   
6. Save and close the Relationships window
7. Open the Bethesda Car Rental2 database
8. Open the Relationships window and configure all relationships as follows:
    
   
9. Save and close the Relationships window
10. On the Ribbon, click Create and, in the Forms section, click Form Design
11. Save the form as Cars and change the following characteristics in the Properties window:
    Record Source: Cars
    Caption: Bethesda Car Rental - Cars
12. Design the form as you see fit. Here is an example (no need to match the fonts; use whatever font you have on your computer):
     
    
13. Save the form and switch it to Form View
14. Create the cars records and make sure you add the pictures from the resources of these lessons
15. Close the form

A Relationship Report

After creating a relationship or while working on relationships, you can create a visual report of the result and be able to print it when necessary. To create the report, in the Tools section of the Ribbon, click the Relationship Report button . This action would automatically generate a report with the relationships designed on it. To keep the report, you should save it and give it a name. You can then print it as you would print any other report.

Practical Learning: Creating a Relationship Report

1. The Bethesda Car Rental2 database should still be opened.
   On the Ribbon, click Database Tools
2. In the Show/Hide section, click the Relationships button
3. In the Tools section of the Ribbon, click the Relationship Report button
4. When the report has been created, close it
5. When asked whether you want to save, click Yes
6. Type Bethesda Car Rental Relationships as the name of the report and click OK
7. In the Navigation Pane, right-click the Bethesda Car Rental Relationships report and click Print...
8. Close the database

Lesson Summary

MCAS: Using Microsoft Office Access 2007 Topics

Exercises

Watts A loan

1. Open the Watts A Loan database.
   Open the CustomersTransactions form in Design View. Add a Text Box below the subform. Set its Name to txtTotalPayments then set its Format to Currency with 2 Decimal Places. Make it get its value from the txtTransactions text box of the sbfAccountTransactions form
   Add another Text Box below the subform and change its properties as follows:
   Name: txtCurrentBalance
   Control Source: =DLookUp("LoanAmount", "LoanProcessing", "CustomerID = " & CustomerID) - Nz(txtTotalPayments)
   Format: Currency
   Decimal Places: 2
   Save and close the form
2. Create a new table in Design View with the following fields:
    

   Field Name	Data Type	Additional Information
   LoanEvaluationID	AutoNumber	Primary Key Caption: Loan Evaluation ID
   LoanAmount	Currency	Caption: Loan Amount Default Value: 0
   InterestRate	Number	Field Size: Double Format: Percent Caption: Interest Rate Default Value: 0.0875
   NumberOfPeriods	Number	Field Size: Integer Caption: NumberOfPeriods Default Value: 12

   Save the table as LoanEvaluation and close it

3. Create a new form based on the LoanEvaluation table
   Save the form as LoanEvaluation
   Add a Text Box in its Detail section and set its properties as follows:
   Name: txtPeriodicPayment
   Control Source:
   =Abs(Pmt(Nz(CDbl([InterestRate]))/12,
   Nz(CInt([NumberOfPeriods])),Nz(CDbl([LoanAmount])),0,0))
   Format: Currency
   Decimal Places: 2
   Use the Command Button Wizard to add a button that can be used to close the form
   Design the form as follows:
    
   
   
   Disable the Maximize button and make the form Pop Up. Don't make it automatically center itself. Before saving the form, position it slightly to the middle-right side of the screen so Microsoft Access would remember that position
   Save and close the form
4. Open the LoanProcessing form in Design View. Using the Command Button Wizard to add a button that, when clicked, would open the LoanEvaluation form (remember that there is no relationship between both forms; therefore, you will Open The Form And Show All The Records). Set the button's Text to Loan Evaluation and its Name to cmdLoanEval.
   Save and close the form
5. Open the LoanProcessing form and use its Loan Evaluation button to open the LoanEvaluation form. In the Loan Evaluation form, evaluate a $1500.00 amount of at 12.50% paid in 28 months. After evaluating it, manually create a new personal loan in the Loan Processing form for the amount of $1500 at 12.50% for 28 payments. The loan is processed by the owner, for the 83-457-8 account on April 10th, 2002. Make the 1st payment due on May 20th of the same year and put a reminder that the payments are due every 22th of the month
   Evaluate other amounts and create loans for the other customers.
   Close both forms
6. Open the CustomersTransactions form to see the results