Access 2000 Final Lab Assignment

6/1/2004 TUESDAY

Create a new database. Save this database as Tuesday.mdb on your disks.

Follow the guides below to generate the database correctly.

Tables

The contents of the table, namely All is given below:

Model / Product Name / Height / Width / Depth / Volume / Price
Arçelik / 5080NF / 191 / 70 / 66 / 435 / 1,556,000,000.-TL
Arçelik / 5086NF / 187 / 70 / 66 / 435 / 2,118,400,000.-TL
Beko / BK-9122 NF / 177 / 70 / 66 / 409 / 1,410,300,000.-TL
Beko / BK-9202 NF / 186.5 / 75 / 71.5 / 500 / 1,982,700,000.-TL
Siemens / KS36U21NE / 170 / 70 / 60 / 354 / 1,408,000,000.-TL
Siemens / KS44U600NE / 200 / 70 / 60 / 435 / 1,660,800,000.-TL

Design the table and type the data by hand.

  1. Decide on the primary key field(s) and reflect it to the design of the table All.
  2. Correct the data type and field size properties, wherever necessary.
  3. The Model field should be selectable from a drop down list of Arçelik, Beko and Siemens.
  4. The Depth field should be between 60 and 75. You should warn the user otherwise. It should not have any default value as well.
  5. Format the Price field as you see.

The contents of the table, namely TM is given below:

Name / Web Site / Call Number
Beko / / 444 1 404
Arçelik / / 444 0 888
Siemens / 444 0 747

Design the table and type the data by hand.

  1. The table TM should not have any primary key.
  2. Correct the data type and field size properties, wherever necessary.
  3. Format the Call Number field as you see
  4. Arrange the field properties of Name such that one must enter a value in this field.

Reports

Create the report entitled “THE NF FAMILY OF REFRIGERATORS” given on page 3.

  1. The style of the report is formal.
  2. The report is sorted in descending order of the field Price.
  3. Make the necessary control location and size changes.
  4. Make the other format changes on each control.
  5. Type your own name and surname after “This report is designed by” in the report footer.
  6. The page footer contains today’s date in the right corner. Make the necessary format changes. (3 is for page number, you can skip it)
  7. Add the dots style line between each detail line.

Queries

Design and run the following 5-queries. Note that the name of each query is typed before its explanation.

Simple sort

Display the model, product name, depth, and price of the refrigerators sorted in descending order of depth, and then by price.

Price less than 1,6 M

Display the model, product name and volume of those refrigerators that have a price less than 1,600,000,000 TL and are produced by Arçelik or Beko.

Average Price and Number of Products by Trademark

Display the model, average price and number of products in this model group. Format the average price in TL (as in All table) and give the caption Average Price. Give the caption Number of Products to number of products.

Dollar Price

Display the model, product name, and price in dollars of each refrigerator given that 1$=1,500,000 TL. Do not forget to name the calculated column as Dollar Price and formatted in the currency format ($).

Create Both Prices Table

The result of this query should be generating a new table called “Both Prices” which will contain all the fields of the All table and a calculated field called Dollar Price which shows the price in dollars given that $=1,500,000 TL. Do not forget to run your query. Verify that the Both prices table exists and contains the correct data. Update the design of the Both Prices table to reflect the primary key and the formatting on Price ( as in the All table) and the Dollar Price (currency in $).

DO NOT FORGET TO UPLOAD YOUR DATABASE FILE Tuesday.mdb.

HAPPY NEW YEAR!

1