Lab Test 3 MS Access CS 101 PRACTICE #2
This is a practice test.
- Create a folder on the Desktop- you choose the name of it.
-
Create a blank Database file in MS Access. Name it with the filename YourName.accdb.
and save it with the name YourName.accdb in your folder on the Desktop.
Then open it with MS Access.
- Create a new table called Properties.
It needs 6 fields, called House ID (Short Text), Street Address (Short Text), City (Short Text), State (Short Text), Zip (Short Text), Property Value (Currency).
- Add 5 records to the table.
The data that should go in the fields is
House ID | Street Address | City | State | Zip | Property Value |
P1234 | 123 Main Street | Lexington | KY | 40506 | $250,000.00 |
P2552 | 199 South Broadway | Lexington | KY | 40507 | $300,000.00 |
P4251 | 82 Second Street | Berea | KY | 40712 | $98,000.00 |
P8141 | 78 Main Street | Berea | KY | 40741 | $80,000.00 |
P8293 | 5923 Nicholasville Road | Lexington | KY | 40151 | $150,000.00 |
- Make House ID the primary key of the table.
- Create a new table called Agents.
It needs five Fields, called Agent ID (Short Text),
First Name (Short Text), Last Name (Short Text), Phone Number (Short Text),
and Licensed? (yes/no).
-
Add four records to the table.
The data that should go in the fields is
Agent ID | First Name | Last Name | Phone Number | Licensed? |
A119 | Ralph | Brown | 238-1414 | no
|
A234 | John | Smithson | 923-1413 | yes
|
A425 | Mary | Jones | 123-4567 | yes
|
A455 | George | Murphy | 238-1241 | yes
|
- Make the Agent ID field the primary key. (It may already be the key.)
- Change the size of the First Name field to 20.
- Change the size of the Last Name field to 50.
- Change the size of the Phone Number field to 15.
- Close the table.
-
Save the database if needed. (This step is just for your protection
in case you are interrupted during your work. You will finish
with ONE database file, YourName.accdb.)
- Create a new table called Sales.
It needs five Fields, called Sales ID (Short Text),
House ID (Short Text), Agent ID (Short Text), Date of Sale (date/time),
and Commission (Single).
Remember you have to make the Commission field type NUMBER, then go down to the Properties grid at the bottom and change "Field size" to the right
type of number.
-
Add three records to the table.
The data that should go in the fields is
Sales ID | House ID | Agent ID | Date of sale | Commission |
S2311 | P4251 | A425 | 1/15/2011 | 0.04 |
S4151 | P1234 | A119 | 12/14/2010 | 0.05 |
S9238 | P8141 | A455 | 11/22/2010 | 0.035 |
- Make the Sales ID field the primary key (if it isn't already).
- Close the table.
- Open the Relationships view. Make sure your tables are closed.
- Create a relationship between the Agents table and
the Sales table, specifically between the
Agent ID field in the Agent table
and the Agent ID field in the Sales table.
It should be one-to-many from the ID field in the Agents table to
the ID field in the Sales table.
Enforce referential integrity. Do not cascade update
or cascade delete.
- Create a relationship between the Properties table and
the Sales table between the House ID field in
the Properties table
and the House ID field in the Sales table.
It should be one-to-many from the ID field in the Properties table to
the ID field in the Sales tables.
Enforce referential integrity. Do not cascade update
or cascade delete.
- Close the Relationships view.
- If you have trouble with the Referential Integrity, an error that says
the data in a table is wrong, cancel the relationship, go back to your tables
and proofread the data in the fields in the tables. That is usually the problem.
Make sure you save the table and try the relationship again.
- Create a query from the Properties table, giving all
fields of the records where the Property Value field was
greater than $150,000.
Sort the query result by the Property Value field, ascending.
Do not display the Property Value field twice in the result.
Save the query with the name "Value Query".
-
Save the database if needed.
- Create a query using all three tables.
You want the fields Last Name (from Agents), Phone Number
(from Agents), Date of Sale (from Sales), Street Address
(from Properties) and City (from Properties).
Sort the query result by the City field, ascending.
Save the query with the name "Agents who have Sales".
-
Save the database if needed.
- Create a query using the Properties table and the Sales table.
You want the fields Street Address, City, Property Value from the Properties
table and Agent ID and Commission from the Sales table.
The criteria: the Property Value is greater than $200,000
or the Commission is greater than 3.5 percent (0.035).
Save the query with the name "High End Properties".
-
Save the database if needed.
-
Make a Report using the Report Wizard including the fields
First Name, Last Name, Commission, Street Address.
Put a title on the report of "Monthly Report".
Group by Street Address.
Change to landscape orientation.
- Save the database if needed.
- Select the Sales table and export it as a PDF file. Save it in
the folder with your database file.
- Make the Last Name field from the Agents table
to be indexed, with Duplicates allowed.
- Create a form to show the details (one record at a time)
of the Sales table. It should appear in the list of Objects
to the left of the screen.
- Make sure these things are saved in the database.
- Close Access.
-
Now you should have ONE database file, YourName.accdb and one PDF file,
Sales.pdf.
-
Create a zip file that contains your files
named YourName.zip. Put this zip file on the Desktop in your folder.
-
Files should be zipped as usual, and submitted through Canvas.
-
Delete your folder and all your files from the Desktop.