viewing tip: while viewing this presentation in your browser, select browse, full screen. then, when...
TRANSCRIPT
Viewing Tip:While viewing this presentation in your browser, select Browse, Full Screen.
Then, when you want to pause the show, Right Click and select Screen / Pause.
Click to Start
For this lab you should have followed these steps, in “EXACT” order.
1. Fill in a “Task Sheet” for your database.
2. Fill in 2 “Subject Sheets”. One for each table identified on the Task Sheet.3. Create a new Database with two tables using the data from your two subject sheets.
4. Define a relationship between the two tables.
5. Print a Table Definition for each table created.
6. Print a datasheet for each table.
Using the task sheet, you should identify and record the task at hand with a brief description.
The Task Sheet
Track Customer and Pet Visits
To provide a record of Customers and each corresponding pet visit.
Invoicing and Mailing Lists
You should then list all of the unique pieces of information (fields) that you will need in your database.
The Task Sheet
Track Customer and Pet Visits
To provide a record of Customers and each corresponding pet visit.
Invoicing and Mailing Lists
CustomerNum
CustomerName
Address
PetID
Pet Name
You should then list all of the unique pieces of information (fields) that you will need in your database.
The Task Sheet
Track Customer and Pet Visits
To provide a record of Customers and each corresponding pet visit.
Invoicing and Mailing Lists
CustomerNum
CustomerName
Address
PetID
Pet Name
At this point, it doesn’t matter what order the data is listed in, just as long as it is listed.
Keeping in mind that Access is a relational database, you should also identify the subject (table) that each piece of data will eventually end up in.
The Task Sheet
CustomerNum
CustomerName
Address
PetID
Pet Name
Track Customer and Pet Visits
To provide a record of Customers and each corresponding pet visit.
Invoicing and Mailing Lists
Customer
Customer
Customer
Pet
Pet
The Task Sheet
CustomerNum
CustomerName
Address
PetID
Pet Name
Track Customer and Pet Visits
To provide a record of Customers and each corresponding pet visit.
Invoicing and Mailing Lists
Customer
Customer
Customer
Pet
Pet
On the task sheet, column #1 is for the field name and column #4 is the table name. If you feel the need to describe a field, use the description column. Ignore the usage column (Column #2) for our labs.
CustomerNum
CustomerName
Address
PetID
Pet Name
Track Customer and Pet Visits
To provide a record of Customers and each corresponding pet visit.
Invoicing and Mailing Lists
Customer
Customer
Customer
Pet
Pet
When the task sheet has been completed, count the number of tables identified in column #4 and make up one subject sheet for each table.
The Subject Sheet
Task Sheet
Subject Sheet
Subject Sheet
CustomerNum
CustomerName
Address
PetID
Pet Name
Track Customer and Pet Visits
To provide a record of Customers and each corresponding pet visit.
Invoicing and Mailing Lists
Customer
Customer
Customer
Pet
Pet
Task Sheet
Subject Sheet
Subject Sheet
CustomerNum
CustomerName
Address
PetID
Pet Name
Track Customer and Pet Visits
To provide a record of Customers and each corresponding pet visit.
Invoicing and Mailing Lists
Customer
Customer
Customer
Pet
Pet
Notice below that two tables have been identified, the Customer Table and the Pet Table.
Task Sheet
Customer Subject Sheet
Pets Subject Sheet
CustomerNum
CustomerName
Address
PetID
Pet Name
Track Customer and Pet Visits
To provide a record of Customers and each corresponding pet visit.
Invoicing and Mailing Lists
Customer
Customer
Customer
Pet
Pet
We would therefore need two Subject Sheets, one for the Customer Subject (table) and one for the Pet Subject (table).
Identify the table name and description of each subject at the top of each subject page.
The Subject Sheet
Customer
Pets
Indicate the relationship to any other tables for each subject.
example:Related Subjects: Name Relationship
Pets Many or One to Many
The Subject Sheet
Pets
Customer One to Many
Task Sheet
Subject Sheet
Subject Sheet
Transcribe the data fromthe task sheet, to each respective subject sheet listing the field name in column #1 and then indicate what the data type is for each field in column #2. Ignore the Validation Rule column for our labs.
CustomerNum
CustomerName
Address
PetID
Pet Name
Customer
Customer
Customer
Pet
Pet
Track Customer and Pet Visits
To provide a record of Customers and each corresponding pet visit.
Invoicing and Mailing Lists
CustomerNum
CustomerName
Customer
Pets
Task Sheet Customer Subject Sheet
CustomerNum
CustomerName
Address
PetID
Pet Name
Customer
Customer
Customer
Pet
Pet
Track Customer and Pet Visits
To provide a record of Customers and each corresponding pet visit.
Invoicing and Mailing Lists
CustomerNum
CustomerName
Customer
Pets
Task Sheet Customer Subject Sheet
Pets
Pets One to Many
CustomerNum
CustomerName
After all the data has been transcribed to each respective Subject (table) sheet, it’s time to identify the data type for each Data Name. (see page AC 2.05 in your book for the different data types available)
Next, create a new, blank database named Lab1nnnn.mdb, where nnnn is the last 4 digits of your Student ID Number, and using your subject tables, create the tables and fields indicated on the subject sheets. (pay close attention to data types)
To create a new database, you must first start Microsoft Access. See Page AC 1.07 in your book for instructions on starting the program.
From your task bar, select“Start” “Programs” “Microsoft Access”
As Access launches, you will see this screen appear.
Select the Blank Access database radio button above, and then click the OK button.
The next screen that will appear is the “File New Database” Screen. This will ask you two very important questions.
First, you must tell Access where you want your new database, by selecting the Folder in the “Save In:” box shown above.
Next, you must name your new database in the “File Name:” box below.Remember, if you plan on keeping your database, name it something other than DB1.mdb.
Finally, click on the “Create” Button below to create your named database in the folder that you selected.
After clicking on the “Create” button, a new, blank, database will be created and open in the Database window.
To create a new table, click on the Tables object tab on the left, then click on the New command button on the top. (see page AC 2.06 in your book)
Since we have two Subject Sheets, (Customer and Pets) we should create two tables, one for each, and name them accordingly.
Select the “Design View” option from the “New Table” dialog box and then click on the OK button.
You are now ready to define your new table using the information from your subject sheet.
Customer Subject Sheet
Pets Subject Sheet
Fill in the required data for each table using the data on it’s respective subject sheet.
When the two tables have been completed and saved, from the Menu Bar, selectTools / Analyze / Documenter.
Note: do this for each table.
Select the table, and click the OK button.
This will produce a documentation report of the table in print preview mode. Look it over carefully, it gives you a lot of information regarding your table. (and it is what will be used to grade your lab)
RelationshipsRelationshipsRelationshipsRelationships
Next it’s time to create our relationship between the two tables.
RelationshipsRelationships
Remember or example about the customer and order tables?OneCustomer,hopefullymany orders?
RelationshipsRelationships
We can join the two tables together by joining the common field in theOne Side,[Customer].[CustomerNum]to the common field in theMany Side,[ Order].[CustomerNum].
RelationshipsRelationships
To do that, we start at the database window, select Tools from the Menu Bar and then select Relationships.
To do that, we start at the database window, select Tools from the Menu Bar and then select Relationships.
RelationshipsRelationships
From the resulting “Relationships” window, we select Relationships, and Show Table ...
RelationshipsRelationships
From the resulting “Show Table” window, we select Our “One Side” table (customers in this case), and then click “Add”.
From the resulting “Show Table” window, we select Our “One Side” table (customers in this case), and then click “Add”.
RelationshipsRelationships
We then select our “Many Side” table, (order in this case) and then again click “Add”.
We then select our “Many Side” table, (order in this case) and then again click “Add”.
RelationshipsRelationships
Then, simply click the Close Button.Then, simply click the Close Button.
RelationshipsRelationships
We will then be back to our Relationships window with the Customer and Order tables showing.
We will then be back to our Relationships window with the Customer and Order tables showing.
To create a relationship (join), click on the common field (CustomerNum) in the One Side (left side), hold the button down, and drag to the common field (CustomerNum) in the Many Side (Order Table) and release the button.
To create a relationship (join), click on the common field (CustomerNum) in the One Side (left side), hold the button down, and drag to the common field (CustomerNum) in the Many Side (Order Table) and release the button.
As soon as we release the mouse button on top of the CustomerNum field in the Order Table, the Edit Relationships window appears. This window first, gives us a chance to verify that we selected the correct fields and the ability to change them if we did not.
As soon as we release the mouse button on top of the CustomerNum field in the Order Table, the Edit Relationships window appears. This window first, gives us a chance to verify that we selected the correct fields and the ability to change them if we did not.
We then can “Enforce Referential Integrity” if we so desire by simply clicking in the “Enforce Referential Integrity” check box.
We then can “Enforce Referential Integrity” if we so desire by simply clicking in the “Enforce Referential Integrity” check box.
Just what is Referential Integrity?
Simply put, using an example,if you have Referential Integrityenforced, when you enter acustomer number in your ordertable, and that customer number does not exist in your customer table, Access will will give you a message saying that you can not enter that customer number as it does not exist in the customer table.
If we choose to “Enforce Referential Integrity” we then have two more options available to us, Cascade Update Related Fields and Cascade Delete Related Records.
If we choose to “Enforce Referential Integrity” we then have two more options available to us, Cascade Update Related Fields and Cascade Delete Related Records.
Notice Access automatically determines the Relationship type.
Notice Access automatically determines the Relationship type.
If you know that you have a one to many relationship, and this says you have a one-to-one relationship, check the fields above to make sure that you have selected the proper common field in both tables.
If you know that you have a one to many relationship, and this says you have a one-to-one relationship, check the fields above to make sure that you have selected the proper common field in both tables.
RelationshipsRelationships
The final step is to click on the Create Button.
The final step is to click on the Create Button.
RelationshipsRelationships
If we selected to “Enforce Referential Integrity” we will see a join line showing the one on the left and the infinity sign (many) on the right.
If we selected to “Enforce Referential Integrity” we will see a join line showing the one on the left and the infinity sign (many) on the right.
If we selected Not to “Enforce Referential Integrity” . . .If we selected Not to “Enforce Referential Integrity” . . .
we will see a plain join line without the one on the left and the infinity sign (many) on the right.
we will see a plain join line without the one on the left and the infinity sign (many) on the right.
RelationshipsRelationships
The final step is to select File and then Save from the Relationships window.
The final step is to select File and then Save from the Relationships window.
RelationshipsRelationships
and then select File and Close.and then select File and Close.