Friday, 20 March 2015

Ms Access Exercise 2



1. Create a database and save as Task2 to your Computer Desktop.
2. Design the database table to contain the following fields and data type.
Name – Text
Birthday – Date/Time    ………… Format – Short Date
Gift Received – Yes/No
Card Received – Yes/No
Gift Sent – Text
Cost – Currency
3. Save the table as Gifts. Do NOT create a primary key.
4. Enter the information in each field in datasheet view using the information below.

Name
Birthday
Gift
Received
Card
Received
Gift
Sent
Cost
Grandma Edith
6/6/1929
Y
Y
Lace Shawl
$22.50
Grandpa Nat
11/25/1928
Y
Y
Tie
$12.00
Grandma Pauly
4/16/1910
Y
Y
Gold Bracelet
$99.99
Grandpa Maury
11/27/1912
Y
Y
Sweater
$74.22
Mom
4/26/1947
Y
Y
Sweater
$49.95
Dad
12/18/1939
Y
Y
Tennis Racket
$40.00
Roberta
2/22/1964
N
N
None
$0.00
Andy
5/12/1966
Y
N
Mug
$5.00
Uncle Neil
3/19/1944
N
N
None
$0.00
Aunt Jane
7/30/1938
Y
N
Plant
$10.00
Sean
9/22/1956
Y
N
Book
$4.95
Cousin Joan
2/27/1964
N
Y
None
$0.00

5. Modify the database based on the information listed below.
a. You are no longer talking to your friend Roberta.  She hasn’t remembered your birthday in years.  Delete her record from your database.
b. You forgot to include your sister, Pamela, in your database.  Add a record for her.  Her birthday is 9/9/73; she always sent you a gift and card.  Last year, you gave her a watch that cost $67.00.
6.  Adjust the column widths of your table to accommodate the longest line of text.
7.  Change the setup of the database to Landscape AND Print your table. 

PERFORM THE FOLLOWING QUERIES BELOW IN QUESTIONS 8-14. 
8.      Show all fields for the gifts that cost you $40 or more.  Sort by cost in descending order.  Save as Q8.
9.      Show all fields for the people that you received a gift from.  Save as Q9.
10.  Show all fields for the people that you did not receive a card from. Save as Q10.
11.  Display the name and birthdate for those people who were born after January 1, 1950. Save as Q11.
12.  Display the name, cost, and gift sent for those gifts sent beginning with the letter “T”. Sort by gifts sent in ascending order.  Save as Q12.
13.  Display the name and gifts sent and cost for those gifts that cost more than $75.00 and less than $5.00.  Sort by cost in descending order.  Save as Q13.
14.  Show all fields for the information about your Mom and Dad.  Save as Q14.
Propellerads