Sam’s Stock

Make - Mazda Man/Auto - manual Colour – Red Model – 121 Funtop Description – 3 door hatchback Price - $3,990 Year – 1990 Kilometres - 130,000

Make - Subaru Man/Auto - auto Colour – Silver Model - Vortex Description – 2 door coupe turbo Price - $4,000 Year – 1989 Kilometres - 240,000

Make - Toyota Man/Auto - manual Colour – Silver Model – Supra Description – 2 door coupe Price - $2,995 Year – 1986 Kilometres - 100,000

Make - Honda Man/Auto - auto Colour – White Model – CRX Description – 2 door coupe Price - $2,995 Year – 1984 Kilometres - 82,000

Make - Lada Man/Auto - manual Colour – Red Model - Samara Description – 4 door sedan Price - $2,990 Year – 1994 Kilometres - 27,000

Make – Toyota Man/Auto - auto Colour – White Model – MR2 Description – 2 door coupe Price - $3,990 Year – 1986 Kilometres - 158,000

Make – Toyota Man/Auto - manual Colour – White Model – GT Sprint Description – 2 door coupe Price - $3,999 Year – 1987 Kilometres - 179,000

Make – Mitsubishi Man/Auto - manual Colour – White Model – Starion Description – 2 door coupe Price - $3,990 Year – 1982 Kilometres - 94,000

Make – Nissan Man/Auto - auto Colour – Black Model – 280 ZX Description – 2 door coupe Price - $4,500 Year – 1979 Kilometres - 37,000

Make – Subaru Man/Auto - auto Colour – Blue Model – Legacy GT Description – 4 door sedan turbo Price - $4,795 Year – 1990 Kilometres - 100,000

Planning the Database

Task 1 (1.1) (1.3)

Using the information Sam has provided, complete the following design for a suitable database.

The design must identify each field and the field name to be used. For each field a suitable data type must be specified. For text fields you also need to specify a field size that suits the information to be stored in that field. The first field has been completed for you.

	Field Name	Data Type	Field Size
Field 1	Make	Text	15
Field 2
Field 3
Field 4
Field 5
Field 6
Field 7
Field 8

Task 2 (1.2)

In your own words describe the purpose of this database and how it will help Sam.

Task 3 (1.3)

Based on the information Sam has given you, identify at least two features the database might use in order to meet Sam’s requirements and briefly explain how each of these features will meet Sam’s needs.

Producing the Database

Task 4 (2.1) (2.3)

Using the design in Task 3, create a new database called AutosBySam on your disk and save it to a location specified by your Assessor.

Create a table and add the field names, data types and field sizes specified in your design.

Task 5 (2.2)

Add the information from Sam’s cards on page 2 to your table.

Task 6 (3.4) (4.1)

When all the records have been added, compare the table with the original information provided by Sam.

Ensure the fields are formatted correctly and the field sizes are sufficient to hold the data.

When you have checked the table for accuracy, print preview the table to ensure it fits on one page and print a copy.

Finally save and close your database.

Using the Database

Task 7 (3.1)

Show your Assessor the location of the folder or disk you have saved your database to, and open the database.

Signed ………………………..……… Assessor

Task 8 (3.1) (2.2)

Sam decides the Lada has got to go. It is not attracting much interest. He drops the price to $150 and it sells the next day.

Delete the Lada from the table.

It is not a good month for white cars. Sam takes his four white coupes and repaints them red.

Update these four records with the new colour.

Task 9 (4.1)

Sam wants to know all the cars he has for sale.

Create a report from the table. Show all fields on the report.

Group the records by Make of vehicle.

Give the report the title “All Vehicles” and save the report

Check the report for accuracy, readability and legibility. Format the report for a professional presentation. Ensure all fields are formatted correctly.

Print preview the report to ensure it fits on one page and print a copy.

Task 10 (3.1) (2.2) (3.4)

Sam wants more Australian cars on his yard.

He buys a blue 1987 Ford Laser 5 door hatchback manual with 211,000kms and a red 1986 Holden Barina 5 door hatchback manual with 100,000kms. He will sell both cars for $3,500 each.

Add these two records to the table.

Print another copy of the report created in Task 9.

Task 11 (3.2) (4.1)

Sam wants his stock sorted by Make of vehicle.

Create a report which shows his cars sorted by Make in ascending order.

Give the report the title “Cars Sorted By Make” and save the report.

Check the report for accuracy, readability and legibility. Format the report for a professional presentation.

Print preview the report to ensure it fits on one page and print a copy.

Task 12 (3.2) (4.1)

Sam wants a second report sorted by the age of the vehicle.

Create a report which shows his cars sorted by age (Year) in ascending order (from oldest to newest).

Give the report the title “Cars Sorted By Age” and save the report.

Check the report for accuracy, readability and legibility. Format the report for a professional presentation.

Print preview the report to ensure it fits on one page and print a copy.

Task 13 (3.3) (4.1)

Sam wants a report of all his cars with manual transmission.

Create a query that lists only manual cars and prepare a report from this query.

Give the report the title “Manual Cars” and save the report.

Check the report for accuracy, readability and legibility. Format the report for a professional presentation.

Print preview the report to ensure it fits on one page and print a copy.

Task 14 (4.2)

Sam is impressed with the results so far and decides he needs to add more information to the “Description” field.

Modify the size of this field in the table if necessary so that it is able to hold up to 60 characters.

Task 15 (3.3) To be done in front of assessor/teacher

¨ Add two new fields to your Table. The first field title will be Number; the second field will be Total Value.

¨ Save your changes

¨ Enter the number of vehicles as indicated by your assessor

¨ Create a query that multiplies the Price by the Number in the Total Value field. You will need to collect the Group by row through View, Totals and change Group by to Expression. Beside the Total Value field name insert a colon and the expression required, that is Total Value:[Price]*[Number]

¨ Run the query.

¨ Print another Report showing the “Total Value of each Model of Car Currently in Stock”. Use all fields in your Report and sort by number of cars.

¨ Print in Landscape view, fitting data to one page.