task for excel & access

Tasks for Ms Excel & Ms Access

You work for an international company called Hothouse Design. You need to calculate some costs for the mail order department of a customer, Rootrainer Trees, to include in their publicity material. All currency values should be in £ sterling with the £ sign visible.

1 Create a data model which looks like this:

The cell to the right of Tax will hold the standard rate of tax in the UnitedKingdom.

The cells below Type contain the type of tree, below Country the country towhich the trees will be sent, below Cost the Cost of each tree and belowOrdered the number of trees ordered.

2 In the cell under Total Cost, enter a formula which calculates the Costmultiplied by the number Ordered

3 In the cell under Tax, enter a formula which:

if the Country contains the text UK, calculates the Total Cost multiplied by the Tax Rate

if the Country does not contain the text UK, contains the number zero.

By Navid Saqib: 0333-4259883

Beginner Level; Excel Task 1:


4 In the cell under Delivery, enter a formula which:

- if the Country contains the text UK, it calculates 4.8 + (Total Cost x 0.032)

- if the Country does not contain the text UK, it calculates 12.25 + (Total Cost x 0.044)

5 In the cell under Total, enter a formula which adds the Total Cost, Tax and the Delivery.

6 Format the Tax Rate as a Percentage to 2 decimal places.

7 Format the cells which involve currency in £.

8 Format the cells in the Ordered column as Integer values.

9 Replicate down all formulae entered in stages 2-5 so that at least 12 rows of data can be entered.

10 Set your page orientation to landscape.

11 Select a view of the sheet which shows all formulae. Adjust the column widths and row heights to ensure that all formulae are visible.

12 Save the data model with an appropriate filename and print a copy of the sheet showing (in full) the formulae used. Make sure that the printout fits on a single printed page.

13 Enter the following data into the model to test that it works correctly.



14 Save this test data and print a copy showing the values. Make sure that the printout fits on a single printed page.

15 Produce a printout showing only the rows where the Country is the UK and the number of trees Ordered is greater than 40.

16 Produce a printout showing only the rows for trees ordered from the USA or Mexico.



Hothouse Design requires you to create a data model which will enable the marketingdepartment to analyze the costs of holidays in Europe.

1. Create a new file with the following layout

2. Enter a formula next to the side heading Total amount. This calculates the total amount of all the holidays, using the data in the Total cost column.

3. Enter a formula next to the side heading Average cost of a flight. This calculates the average cost of a flight using the data in the Flight costs column.

4.

Enter a formula next to the side heading Total number of destinations. This calculates the total number of destinations, using the data in the Number of holidays sold column.

5. Save this file as HOLIDAY.

6. Enter the following on your model below the data:

7. Name this range of cells.

8. Enter a formula to calculate the total cost of a holiday; this will use the Number of holidays; Flight costs; Accommodation costs; and look up the Insurance rate in the named range of cells. Copy this formula for each holiday.




9. Use an IF function to place the statement Yes or No under the heading Discount if over $25000. When the holiday is greater than $25000, the message will display Yes; otherwise the message will display No. Copy this formula for each holiday.

10. Enter the following test data under the headings:

11. Save this file as HOLIDAY2

12. Print the spreadsheet values – ensure all data is fully displayed.

13. Change the display to formulae and print in landscape – ensure all data is fully displayed.

14. Select the holidays which are less than 20000 or greater than 45000 and extract their Cdetails.

15. Save this file as HOLIDAY3 and print the extract.



You work for an international company called Hothouse Design which has a new project for a business customer called Mobile Solutions. The project concerns designing and promoting a range of new mobile phone packages.

You are going to build a financial model that will calculate the profit from the sales of phones.

1 Create a data model using an appropriate spreadsheet software package. The layout should be the same as the one below.

2 In the first row November, enter a formula that will calculate the Sales Income.

You will need Number of Sales x Selling Price.




3 In the first row November, enter a formula that will calculate the Variable Costs.

You will need Number of Sales x Variable Costs.

4 In the first row November, enter a formula that will calculate the Profit or Loss.

You will need Sales Income - (Fixed Costs + Variable Costs).

5 Copy the formulae you have entered for Sales Income, Variable Costs and Profit or Loss into the months December to October.

6 Enter formulae that will calculate the annual total for the Number of Sales and Profit or Loss.

7 Enter the data shown in the table. Check you have entered all text and data with accuracy. The fixed costs are $50,000.00 for each month. The first row should give the results shown in the table above. 8 Format the columns Sales Income, Fixed Costs, Variable Costs and Profit or Loss to two decimal places with a dollar sign.

9 Adjust column widths so that all the data is shown.

10 Change the paper orientation to landscape.

11 Enter an IF statement to the right of the total profit or loss figure.

The IF statement should contain the following:- If the value in the profit cell is less than 0, it indicates Loss- If the value in the profit cell is greater than or equal to 0, it indicates Profit

12 At the bottom of the page add your name and today’s date. Save the spreadsheet model. Print the model showing all values.

13 Print the spreadsheet showing all formulae.

14 Fixed costs will have to increase for every month. Change the Fixed Costs from $50,000.00 to $150,000.00. You should find that the Annual Total Profit or Loss is negative.

15 Save the spreadsheet using a different file name and then print it.



16 Produce a printout showing only the rows where the profit is greater than $0.00. Make sure your name and today’s date is added at the bottom of the page.

17 Produce a p rintout showing only the rows where the profit is greater than $0.00 and the Variable Costs are less than $525,000.00. Make sure your name and today’s date are added at the bottom of the page.

You work for a food wholesale company called Food Chain. You have been asked by the salesdepartment to calculate the value of current food orders.All currency values should be in dollars with the $ sign visible._1 Create a data model which looks like this:




2 In the Information Table name the range of cells which hold the data for Code and Disc. Give this range the name Info

This range will be used to calculate the Discount.

3 In the main table in the cell under Discount, enter a formula using Lookup.This formula looks up the Disc in the Information Table using the Code.

4 In the main table in the cell under Value, enter a formula to multiply the Discount by Order.

5 In the Concession column enter a formula to perform the following calculations:

If the Order is greater than 1500, calculate Order multiplied by 5%. If the Order is less than 1500, the result will be zero.

6 In the cell under Total, enter a formula which subtracts the Value and Concession from the Ordere.g. Order – (Value + Concession)

7 Format the cells in the Discount column to a percentage format.

8 Format the cells in the Order, Value, Concession and Total columns to display the $ sign (dollar) with 2 decimal places.

9 Copy down all formulae entered in steps 3 - 6 so that 9 rows of data can be entered.




11 Save the data model and print a copy of the sheet showing the formulae used. Make sure that the contents of all cells are visible and that the printout fits on a single printed page.

12 Enter the following data into the model to test that it works correctly:

13 Save this data and print a copy showing the values. Make sure that the contents of all cells are visible and that the printout fits on a single printed page.

14 Produce a printout showing only the rows where the Total > 1500 and the Code >= 3.

15 Produce a printout showing only the rows where the Code = 5 and the Concession > 0.

You work for an international company called Gem Export, which sells jewels and precious stones. You have been asked by the marketing department to calculate the cost of the gems which are going to be advertised on the internet.

All currency values should be in dollars with the $ sign visible.





2 In the Cutting options table name the range of cells which hold the data for Gem cut and Cutting cost. Give this range the name cut.

3 In the main table in the cell under Cut Cost, enter a formula using Lookup. This formula looks up the Cutting Cost in the Cutting options table using the Gem cut and divides this value by the Carat.

4 In the cell under Insurance enter a formula to calculate the insurance cost.

If the Precious data is p, calculate Carat x 2.50If the Precious data is not p, calculate Carat x 1.50



5 In the cell under Total Value, enter a formula which multiplies the Carat by the Carat Value and adds the Cut Cost and Insurance.

6 Format the cells in the Carat Value, Cut Cost, Insurance and Total Value columns to display the $ sign with 2 decimal places.

7 Copy down all formulae entered in stages 3 – 5 so that at least 12 rows of data can be entered.



10 Enter the following data into the model to test that it works correctly.


12 Produce a printout showing only the rows where the Total Value is greater than 150 and Precious is sp

13 Produce a printout showing only the rows where the Gem cut equals faceted and Insurance is less than 5

You work for an international car hire company called Argon Hire. You have been asked by the sales department to calculate the hire charges for vehicles which are to be advertised on the internet.






2 In the Insurance table name the range of cells which hold the data for Category and Ins. Give this range the name ins

3 In the main table in the cell under Ins Rate, enter a formula using Lookup.This formula looks up the Ins in the Insurance Table using the Category.

4 In the main table in the cell under Insurance, enter a formula to multiply the Rate by Ins Rate.

5 In the cell under Total, enter a formula which adds the Rate to the Insurance

6 In the Deposit column enter a formula to show whether a deposit is required.

If the Rate is greater than 60, display the word “Yes” If the Rate is not greater than 60, display the word “No”



7 Format the cells in the Ins Rate column to a percentage format.

8 Format the cells in the Rate, Insurance and Total columns to display the $ sign (dollar) with 2 decimal places.

9 Copy down all formulae entered in stages 3 - 6 so that at least 8 rows of data can be entered.





14 Produce a printout showing only the rows where the Total is greater than 60 but less than 100 and Doors are 2

15 Produce a printout showing only the rows where the Total is greater than 50and the Category is B or C

You work for a stationery company called Pens4U. Your manager has asked you to calculate the value of current orders.





2 In the Information Table name the cell containing the data 0.05 as five Name the cell containing the data 0.08 as eight

These named cells will be used to calculate the Discount Value.

3 In the main table in the cell under Discount Value, enter a formula to calculatethe discount of the first order:

If the Order Value is greater than 125, then multiply the Order Value by the named cell eight;

if not, then multiply the Order Value by the named cell five

4 In the main table in the cell under Total enter a formula which subtracts the Discount Value from the Order Value

5 In the Information Table format the cells named five and eight to display the % value and 0 decimal places, e.g. 5%.

6 In the Information Table use a function to count the number of orders received using the Company column.7 Format the cells in the Order Value, Discount Value, and Total columns to display the $ sign (dollar) with 2 decimal places.

8 Copy down all formulae entered in steps 3 - 4 so that at least 12 rows of data can be entered.




10 Save the data model and print a copy of the sheet showing the formulae used. Make sure that the contents of all cells are visible and that the printout fits onto a single printed page.


12 Save this data and print a copy showing the values. Make sure that the contents of all cells are visible and that the printout fits onto a single printed page.

13 Produce a printout showing only the rows where the Company is equal to Aztec Supplies or Stokers and the Order Value is greater than 345

You work for a gym equipment company called Gymnastic. Your manager has asked you to calculate the value of current stock for exercise bikes and treadmills.





2 In the Information Table name the cell that holds the data for 0.05 as five Name the cell that holds the data 0.1 as tenThese named cells will be used to calculate the Mark-up price.

3 In the main table in the cell under Mark-up Price, enter a formula to calculate the mark-up on the first stock item:

If the Purchase price is greater than 500 then multiply the Purchase price bythe named cell ten to calculate the Mark-up price

If the Purchase price is less than 500 then multiply the Purchase price by thenamed cell five to calculate the Mark-up price



4 In the main table in the cell under Retail price enter a formula which adds the Mark-up price to the Purchase price

5 In the Information Table format the cells containing the data 0.05 and 0.1 to display the % value to 0 decimal places, e.g. 5%

6 In the Information Table use a function to count the number of Treadmills in stock. Place the result below the heading Treadmills

7 Format the cells in the Purchase price, Mark-up price, and Retail price columns to display the $ sign (dollar) with 2 decimal places.

8 Copy down all formulae entered in steps 3 - 4 so that at least 12 rows of data can be entered.





13 Produce a printout showing only the rows where the Type contains foldaway or folding



You work for an electrical retail company called Electry. Your manager has asked you to calculate the current stock position for stock items.






2 In the Information Table, name the cell that holds the data 0.03 three. Name the cell that holds the data 0.05 five.

These named cells will be used to calculate the Increase

3 In the main table in the cell under Increase, enter a formula using IF. This formula calculates the mark-up on the first item.

If the Purchase is greater than or equal to 300 then multiply the Purchase by the named cell five to calculate the Increase

If the Purchase is less than 300 then multiply the Purchase by the named cell three to calculate the Increase

4 In the main table in the cell under Sale, enter a formula which adds the Increase to the Purchase

5 In the Information Table, format the cells containing the data 0.03 and 0.05 to display the % value with 0 decimal places (for example 5%).

6 In the Information Table, use Countif to count the number of items where the Size of item is Small. Place the result in the cell to the right of the heading Small.

In the Information Table, use Countif to count the number of items where the Size of item is Large. Place the result in the cell to the right of the heading Large.

7 Format the cells in the Date column to a long date format (for example March12, 2004).

8 Format the cells in the Purchase, Increase, and Sale columns to display the $ sign (dollar) with 2 decimal places.

9 Copy down all formulae entered in steps 3 - 4 so that 12 rows of data can be entered.

10 Set the page orientation to landscape.






14 Produce a printout showing only the rows where the Size of item contains Small

15 Produce a printout showing only the rows where the Date is after 13 March 2005 and the Purchase is greater than 500



You work for a camera company called Dygitell. Your manager has asked you to calculate the retail price of current stock for digital cameras.






2 In the Information Table, name the cell that holds the data 0.05 five Name the cell that holds the data 0.07 seven

These named cells will be used to calculate the Increase

6 In the Information Table, use Countif to count the number of cameras where the Type is Novice. Place the result in the cell to the right of the heading Novice.In the Information Table, use Countif to count the number of cameras where the Type is Expert. Place the result in the cell to the right of the heading Expert.

7 Format the cells in the Date column to a long date format (for example March12, 2004).

8 Format the cells in the Purchase, Increase, and Sale columns to display the $ (dollar) sign with 2 decimal places.

9 Copy down all formulae entered in steps 3 – 4, so that 12 rows of data can be entered.

10 Set the page orientation to landscape.






14 Produce a printout showing only the rows where the Size of item contains Small

15 Produce a printout showing only the rows where the Date is after 13 March 2005 and the Purchase is greater than 500

Advance Excel Task 1 A-B

1 Using a suitable software package, load the file ASSA5PEN.CSV

2 Insert two new rows at the top of the spreadsheet.

3 Enter the values 50%, 100% and 150% in the first three cells of the top row. Name these cells ONE, TWO and THREE. They are the extra penalties charged for late payment of a congestion charge.

4 In the fourth cell on the top row, enter the date 31 Jan 2005 and name the cell CALCDATE

5 In row 2, enter the following column headings, which should be in bold type and left-aligned:

6 In the column headed Basic, enter a formula which looks up the basic penalty charge from the data in the file ASSA5CHG.CSV where EngineSize = Size.

7 Replicate this formula for each item.

8 In the MonthsLate column, enter a formula which

• Subtracts the date shown in the CALCDATE cell from the date shown in the DatePaid column• Divides this result by 30• Returns the integer part of this value



You may add extra column(s) to help.


10 In the Penalty column, enter a formula so that:

• If MonthsLate is 1, multiply ONE by Basic• If MonthsLate is 2, multiply TWO by Basic• If MonthsLate is 3, multiply THREE by Basic


12 In the Charge column, enter a formula which will add the value in Penalty to Basic


14 Format the Basic, Penalty and Charge columns so that numbers are shown to 2 decimal places.

15 Select only those entries where EngineSize is greater than 2000 and MonthsLate is equal to 3

16 Sort the table in ascending order of DatePaid and then in descending order of Charge.

The first 2 rows are shown below.

17 Create a header which says Large Engine Cars – Three Months Overdue and a footer which shows your name and today's date.

18 Print all details for these entries adjusting the page layout if necessary so that the whole table fits on a single page. Make sure that the contents of all cells are visible and that your name is printed.

19 Change the wording of the header to Formulae used

20 Show only the columns in which you have used formulae.

21 Print this extract in landscape format, showing all the formulae instead of figures. Show row and column headings.

Make sure that the contents of all cells are visible and that your name is printed.

22 In a new worksheet, use all the data to create a pivot table (cross-tab) which:



• Has MonthsLate as the row fields• Has EngineSize as the column fields• Has Charge as the data items

23 Change the field settings for Charge to show the count of Charge Select only the EngineSize less than 1900

24 Create a header which says Count of Debts by Engine Size and a footer showing your name and today's date.

25 Print this sheet. Make sure that the contents of all cells are visible and that your name is printed.

Advance Excel Task1- B

You are working in the transport department of the City of Tawara Beach. You are going to use a spreadsheet application to demonstrate some of the ways in which bus usage can be analysed.

1 Using a suitable software package, load the file ASSB5BUS.CSV


3 Enter the values 1.00 and 0.75 in the first two cells of the top row. Name these cells STANDARD, and SHORT. They are the fares charged for standard journeys and short distance journeys.


5 Insert a column headed Area between Route and StartPoint

6 In the JourneyTime column, enter a formula which works out the time taken for the journey. The formula will need to subtract the time in StartTime from the time in FinishTime

Format the cell to show the result in hours and minutes.

7 Replicate this formula for each journey.



8 In the column headed Area, enter a formula which looks up the Suburb from the data in the file ASSB5RTS.CSV where Route= RouteCode


10 The codes used for the start and stop point can be used to calculate the fare. Enter a formula in the column headed Fare which

• extracts the second digit from StartPoint• extracts the second digit from EndPoint• enters the value SHORT if these two digits are the same otherwise enters the value STANDARD


12 Format the Fare column so that numbers are shown to 2 decimal places.

13 Select only those journeys where the Route is 47 and Fare = 0.75

14 Sort the table in ascending order of StartPoint and then in ascending order of StartTime.

14 Create a header which says Short Journeys – Route 47 and a footer which shows your name and today's date.

15 Print all the details of these journeys, adjusting the page layout if necessary so that the whole table fits on a single page.


16 Change the header to Formulae used

17 Show only the columns in which you have used formulae.

18 Print this extract in landscape format, showing all the formulae instead of figures. Show row and column headings.


19 In a new worksheet, use all the data to create a pivot table (cross tab) which:



• Has Date as the row fields• Has Route as the column fields• Has JourneyTime as the data items

20 Change the field settings for JourneyTime to show the average JourneyTime.

Select only the journeys in February.Format JourneyTime to show hours and minutes.

The first 5 rows are shown below. Note that the numbers shown areexamples only; some are not correct and some cells may be empty.

21 Create a header which says Average Journey Times and a footer showing your name and today's date.

22 Print this sheet. Make sure that the contents of all cells are visible and that your name isprinted.

Advance Excel Task 2 A

You are working in the accounts department at the Stella Hotel, Tawara Beach. You are going to use a spreadsheet application to demonstrate some of the ways in which sales data can be analysed.

1 Using a suitable software package, load the file ASSA6BKG.CSV


3 Enter the values 20%, 10% and 5% in the first three cells of the top row. Name these cells HIGH, MED and LOW. They are the extra charges applied during certain periods of the year for late bookings.


5 Insert a column headed Month between Arrive and Nights

6 The Arrive date is shown as yyyymmdd – eg 20061002 would mean 2 Oct 2006. In the Month column enter a formula which extracts the month value as a number from the Arrive column. You may add extra column(s) to help in this operation

7 Replicate this formula for each booking.

8 In the column headed Season, enter a formula which looks up the season from the data in the file ASSA6RTS.CSV where Month= MonthCode



9 In the LateCharge column enter a formula which works out the late charge for the room. The formula will

• calculate the charge by multiplying the rate in cell HIGH, MED or LOW by Basic, according to the season indicated in Season

10 In the TotalCharge column, enter a formula which works out the total charge for the room. The formula will

• add the value in LateCharge to Basic• multiply this result by Nights

11 Replicate the formulae entered at steps 8, 9 and 10 for each item.

12 Format the Basic, LateCharge and TotalCharge columns so that numbers are shown to 2 decimal places.

13 Sort the table in ascending order of Nights and then ascending order of Month

14 Create a header which says High Season – Short Bookings and a footer which shows your name, candidate number and today's date.

15 Select only those bookings where the Season is high and Nights are less than 5 and print their details; adjust the page layout if necessary so that the whole table fits on a single page (make sure that the contents of all cells are visible and that your name is printed).

16 Change the wording of the header to Lowest Cost Bookings

17 From all the data, select all bookings where the TotalCharge is less than 250

For these orders, show only the columns Account, Month, Season, LateCharge and TotalCharge

18 Print this extract in landscape format, showing all the formulae instead of figures (make sure that the contents of all cells are visible and that your name is printed).

19 Create a new worksheet.

20 In this new sheet, enter formulae which will count all the med Season bookings in the file ASSA6BKG.CSV and show the count for each month:

Arrange the formulae so that you have a table like this:



21 Copy these cells to another area of the spreadsheet and transpose them so that you have a table like this:

22 Create a header which says Med Season Bookings by Month and a footer showing your name, candidate number and today's date.

23 Print this sheet (make sure that the contents of all cells are visible and that your name is printed).

Advance Task 3 A

You are working in the accounts department of the Tawara Pleasure Ferry Company. You are going to use a spreadsheet application to demonstrate some of the ways in which ticket sales data can be analysed.

1 Using a suitable software package, load the file ASSA7TKT.CSV


3 Enter the values 12%, 5% and 10% in the first three cells of the top row. Name these cells DNNR, BKFST and LNCH They are the charges added to the cost of the ticket for Dinner,Breakfast and Lunch which are provided on the ferry at certain times of day.

4 In row 2, enter the following column headings, which should be in boldtype and left-aligned:

5 Insert a column headed MealType between Hour and MealCharge

6 The Time is shown as a string which always starts with T. For example, T201556 would mean 20:15:56 In the column headed Hour, enter a formula which calculates the houras a number (eg 8 not 08) from the time column.

You may add extra column(s) to help in this operation.




8 In the column headed MealType, enter a formula which looks up the meal code from the data in the file ASSA7MLS.CSV where Hour= TimeCode.

9 In the MealCharge column enter a formula which works out the charge for the meal. The formula will• calculate the charge by multiplying the value in cell DNNR, LNCH or BKFST by Basic, according to the meal type indicated in MealType

10 In the TotalTakings column, enter a formula which works out the total income for the ferry journey. The formula will

• add the value in MealCharge to Basic• multiply this result by Sold

11 Replicate the formulae entered at steps 8, 9 and 10 for each journey.

12 Format the Basic, MealCharge and TotalTakings columns so that numbers are shown to 2 decimal places. Save this file as BOOKINGS

13 Sort the table in ascending order of Hour and then descending order of TotalTakings

14 Create a header which says Poor Ticket Sales – Breakfast and a footer which shows your name and today's date.

15 Select only those journeys where the MealType is BKFST and Sold is less than 50. Adjust the page layout if necessary so that the whole table fits on a single page.

16 Print these details. Make sure that the contents of all cells are visible and that your name is printed.

17 Change the wording of the header to Best Sales

18 From all the bookings, select those where Sold is more than 250 For these journeys, show only the columns Time, Hour, MealType, MealCharge and TotalTakings

19 Print this extract in landscape format, showing all the formulae instead of figures. Make sure that the contents of all cells are visible and that your name is printed.

20 Create a new worksheet.

21 In this new sheet, enter formulae which will add up all the LNCH MealType bookings in the file BOOKINGS and show the total for each hour:

22 Copy these cells to another area of the spreadsheet and transpose them so that you have a table like this:



23 Create a header which says Lunch Sales by Hour and a footer showing your name and today's date.

24 Print this sheet. Make sure that the contents of all cells are visible and that your name is printed.

Ms Access Tasks



Ms Access Task 1:

You work for the transport department in the City of Tawara Beach. You are going to develop adatabase for recording drivers who have applied for a reduction in the congestion charge. Thereduction is based on either the area in which they live or the kind of vehicle they drive.

1 Using a suitable software package, create a new database.

2 Import the files ARDA5VEH.CSV, ARDA5ZIP.CSV and ARDA5FUL.CSV

You will need to use the following information to create the tables:



3 Establish the following One-to-Many Relationships:

L.FuelCode 1----∞ ARDA5VEH.FuelARDA5ZIP.Code 1----∞ ARDA5VEH.ZipCode

4 Select details only of the vehicles using Petrol and based in the Beachhead area.

You will need to use the Description and Area fields to find this information.

5 For each vehicle, show only the Registration, Owner, ZipCode and Make.

6 Sort first in ascending order of ZipCode and then in ascending order of Make.

7 Save and print this list. Make sure that your name and today’s date are shown on the printout.

You are going to prepare a report which shows details of all the Ford cars.

8 Select from all the records, only the Ford cars.

9 Prepare and print a report showing this information:

• Include in the header: City of Tawara beach and today’s date• In the detail rows show: Registration, Owner, Area, AreaDisc (formatted to 2 decimal places) and the

total discount for this vehicle (AreaDisc + FuelDisc) (formatted to 2 decimal places)• Group the data by Fuel



• In each group heading show: Description, FuelDisc (formatted to 2 decimal places)

• In each group footer show: The sum of area discount for all vehicles in the group (formatted to 2 decimal places)

• Include your name at the bottom of the report

Your report may look something like this:

You are now going to prepare a report which summarises some information about the numberof vehicles in each area using LPG as a fuel.

10 Select from all the records, only details of the cars which use LPG. Include only the fields Make, Description and Area

11 Using this data, create a cross-tab (pivot table) which shows Make as row labels and Area as column headings. Show the numbers of each car make per area.

12 Add the title Summary – LPG cars per area to the report. Include your name and today’s date at the end of the report and then print it.



The first 3 rows of the report will look something like this:

Task 2:You are working in the transport department at the City of Tawara Beach and you are going to use a database to analyse some of the data concerning car sharing.


2 Import the files ARDB5PC1.CSV, ARDB5VEH.CSV, ARDB5PC2.CSV and ARDB5LIF.CSV





Ardb5PC1.PostCode 1----∞ ardb5veh.StartPostCodeArdb5PC2.PostCode2 1----∞ ardb5veh.EndPostCodeArdb5Veh.Registration 1----∞ Ardb5lif.registration

4 Select only the lifts which took place on Date 3 Jan 2005

5 Show only the fields Date, Registration, StartCityZone, EndCityZone and Passengers

6 Sort this data first into ascending order of StartCityZone and then in ascending order of EndCityZone

The first 2 rows are shown below.

7 Save and print this list.Make sure that your name and today’s date are shown on the printout.You are going to prepare a report which shows details of all the lifts which started in city zone 4 and had more than 1 passenger.

8 Select from all the records, only the lifts for 3 Jan 2005, started in city zone 4 and which had more than 1 passenger.

9 Prepare and print a report showing this information:• The header should include Tawara Beach – Car Sharing and StartCityZone• The detail rows should show Registration, Date and Passengers



• Group the data by EndCityZone and for each group show the total number of passengers carried

• At the end of the report show your name and today’s date

You are now going to prepare a report which summarises some information about the lifts.

10 Select from all the records, only the cars where Date is 4 Jan 2005 Include only the fields StartCityZone, EndCityZone and Passengers

11 Create a cross-tab (pivot table) which shows StartCityZone as row labels and EndCityZone as column headings. Show the numbers of passengers travelling between each zone.

12 Print out this cross-tab.

Make sure that your name and today’s date are shown on the printout.



The pivot table should look something like this:

Task 3:

You are working at the Hotel Stella, Tawara Beach. You are going to analyse some of the hotel’s data and produce reports.


2 Import the files ARDA6RMS.CSV, ARDA6FAC.CSV and ARDA6COD.CSV





ARDA6RMS.RoomNo 1----∞ ARDA6FAC.RoomNoARDA6COD.ItemCode 1----∞ ARDA6FAC.ItemCode

4 Search for only those rooms that contain 1 DVD Player You will need to use the Description and Quantity fields to find this information.

For each room, print the RoomNo, RmDescription and Type in descending order of RmDescription and ascending order of RoomNo

Print out this list.

Make sure that your name and today’s date are shown on the printout.

You are going to prepare a report which shows details of all the items which are not beds.

5 From all the records, select only the items which are not beds.

6 Prepare and print a report which:• has a report header including Stella Hotel• has detail rows displaying RoomNo, RmDescription, Quantity and ExtraCharge• groups the data by Description• shows the Description as a group header• shows the total extra charge for the extra facilities in the rooms as a group footer• displays your name and today’s date at the end



You are now going to prepare a report which summarises some information about thenumber of rooms of each kind per floor.

7 From all the records, select details of the types of room on each floor. Include only the fields Floor, RmDescription, RoomNo

8 Using this data, create a cross-tab (pivot table) which shows RmDescription as row labels and Floor as column headings. Show the numbers of each type of room per floor.

9 Add the title Summary – Rooms per floor, your name and today’s date to the report and then print it.Your report may look something like this:

Task 4:You are working at the Tawara Pleasure Ferry Company. You are going to analyse some of thecompany’s data and produce reports.


2 Import the files ARDA7BTS.CSV, ARDA7TRP.CSV and ARDA7PRC.CSV





ARDA7BTS.FerryNo 1----∞ ARDA7TRP.FerryNoARDA7PRC.Season 1----∞ ARDA7TRP.SeasonARDA7PRC.TimeOfDay 1----∞ ARDA7TRP.TimeOfDay

4 Search for only those trips which took place during the Winter on the ferry Tawara OrchidYou will need to use the Season and FerryName fields to find this information.

For each trip, print the FerryName, Season, TimeofDay and Date in ascending order of TimeOfDay, then ascending order of Date

Print out this list.

Make sure that your name and today’s date are printed.

You are going to prepare a report which shows details of afternoon cruises on Tawara Orchid.

5 From all the records, select only the Afternoon cruises which were on Tawara Orchid

6 Prepare and print a grouped report which:



• has a report header Afternoon Cruises on Tawara Orchid• groups the data by Season• shows the Season as a group header• has detail rows displaying Date, TimeOfDay, Price, Passengers• shows the total income for each season (sum of price multiplied by passengers for each cruise)• displays your name and today’s date at the end


You are now going to prepare a report which summarises some information about the numberof passengers per season on each ferry.

7 From all the records, select details of the trips on each ferry. Include only the fields FerryName, Season, Passengers

8 Using this data, create a cross-tab (pivot table) which shows Ferry as row labels and Season as column headings. Show the total number of passengers per ferry per season.

9 Add the title Summary – Trips per ferry, your name and today’s date to the report and then print it.



task for excel & access

Documents

total cost column

total cost x

data model

total number of destinations

ms excel ms access tasks

number of trees

average cost

following data