Sunday, 29 March 2015

Ms Access Exercise 4



1.            Design the database called Goods and a table to contain the following fields and data type.
PRODUCT ID – Text
DESCRIPTION – Text
COST – Currency
SELLING PRICE – Currency
CODE – Text
2.            Save the table as Goods. Do NOT create a primary key.
3.            Enter the information in each field in datasheet view using the information below.

PRODUCT ID
DESCRIPTION
COST
SELLING PRICE
CODE
CH04
Chess Set
Ksh.26.75
Ksh.28.90
WW
DI24
Dinosaurs
Ksh.3.75
Ksh.4.95
MS
GL18
Globe
Ksh.27.50
Ksh.29.95
MS
JG01
Jigsaw Puzzle
Ksh.5.40
Ksh.6.95
MS
PC03
Pick Up Sticks
Ksh.8.50
Ksh.10.95
WW
ST23
Stationery
Ksh.3.95
Ksh.5.00
AR
TD05
Tidly Winks
Ksh.13.75
Ksh.15.95
WW
WI10
Wizard Cards
Ksh.7.50
Ksh.9.95
MS
WL34
Wildlife Poster
Ksh.2.50
Ksh.2.95
AR
YO12
Wooden YoYo
Ksh.1.60
Ksh.1.95
WW
4.            Change the field name CODE to VENDOR CODE and save the design change.
5.            A new field called ON HAND is to be added to the database table in design view.  Insert the new field before the COST field.  Data type – Number
6.            Enter the information for the ON HAND field using the information below (shaded area).
11,       14,      2,         3,         5,         8,         6,         10,       15,      9
7.             The following product item is no longer in stock – PC03 Pick Up Sticks.  Delete this record. 
8.             Adjust the column widths of your table to accommodate the longest line of text.
9.            Change the page setup to Landscape. Print the Museum table in datasheet view. 

Create the following queries and make sure they are saved under the Queries Object when you finish.

10.            Display the Product Id, Description, Cost, and Vendor Code for the products where the Vendor Code is MS.      Sort by Description in ascending order. Save as Query10. 
11.            Display the Product Id and Description for the products where the description begins with the letters, Wi.   Sort by Description in ascending order. Save as Query11.
12.            Display the Product Id, Description, Vendor Code, and Cost for the products that cost more than Ksh.20.     Save as xxQuery13. 
13.            Display the Product Id, Description, and Selling Price for the products that have a Selling Price of Ksh.5 or less.   Save as Query13. 
14.            Display all fields for those products that cost more than Ksh.10 and where the number of units on hand is less      than 10. Sort by cost in descending order.  Save as Query14. 
15.            16. Display all fields for those products that have a Vendor Code of WW or have a Selling Price less than Ksh.10. Save as Query15. 
Propellerads