A tax inspector is doing a random survey of local businesses. He uses database software to help him generate reports.

Today he is at a car warehouse in Alabama counting stock.

   Observations

1. The database table has 6 fields - the ID field is the Primary Key field. It looks like an Autonumber generated by the database application.
2. There are 151 records of data.
3. The tax inspector looks pretty cool.

Sophisticated database software allows users to generate reports. Of course, the reports often only display certain data so before making a report we often need to make a query.

The tax inspector needs to create a report on all green cars, grouped by color, which are not Lexus or Lincoln and show how many of each color is in stock.

Now that we have the data required for our report, we can make the report. The tax inspector will export the report as a PDF document and email it to his boss in LA.

Wait! Checkout the calculated field in the report footer!

   Skill-up 1 - Creating a report

Download the csv file about car stock in Alabama.

Import the data into your database application. There is already an ID field so use it. Your database application does not need to make a new one. Carefully consider the datatypes of each field during import.

Follow these instructions carefully:

1. After importing the data, take a screenshot of the table in Design View. Save it in your evidence document.
2. You will create a report which shows the average engine size and number of cars in stock for all manufacturers which have 10 or less cars in stock. You will first need to create a query to do this.
3. The report will calculate the total number of cars listed in the report footer.
4. Your report will look like this

Save a screenshot of your query and your report in Design View in your evidence document.

data file

Be smart! Download the file and save it in a new project folder. Don't leave it in the downloads folder of your computer. You are not a newbie!

    Challenge 1 - Another report

Create a new query. This will create a new calculated field called Tolerance. This is calculated as Engine size + (Engine Size*10%)

Use the query to create a report that shows all manufactures who have more than 10 cars in stock. It shows the each manufacturers' number of cars in stock and average tolerance.

In the footer of the report, it shows the average of all of the average tolerances!

All numbers are displayed to 2 decimal places.

The report has the title Tolerance Data.

Your name is in the page footer.

Save a screenshot of your query and your report in Design View in your evidence document.

   Evidence

In your evidence document, write 3 things that you now know about reports.