Author Topic: Applied ICT June 2009 Problem  (Read 1564 times)

Offline abdjed1

  • Newbie
  • *
  • Posts: 14
  • Reputation: 44
Applied ICT June 2009 Problem
« on: March 19, 2011, 11:29:59 am »
How do I do task 10
"Select from all the records only the customers that have booked more than one
holiday. Calculate for each of these customers the average price of their holidays.
Include within the report only the Forename, Surname and the summary data.
Group this data by the number of holidays booked. Sort the data within each group into
ascending order of Surname, then into ascending order of Forename. For each group
show the number of customers and the average cost of the holidays. Make sure that
the average costs are formatted into the same currency format as the cost field.
Add the title Customers with more than one holiday to the report. Make sure that the
report fits on a single page and that all data and labels are visible."

Offline $tyli$h Executive

  • Honorary Member
  • SF V.I.P
  • *****
  • Posts: 5070
  • Reputation: 65403
  • Gender: Male
Re: Applied ICT June 2009 Problem
« Reply #1 on: March 19, 2011, 02:03:42 pm »
How do I do task 10
"Select from all the records only the customers that have booked more than one
holiday. Calculate for each of these customers the average price of their holidays.
Include within the report only the Forename, Surname and the summary data.
Group this data by the number of holidays booked. Sort the data within each group into
ascending order of Surname, then into ascending order of Forename. For each group
show the number of customers and the average cost of the holidays. Make sure that
the average costs are formatted into the same currency format as the cost field.
Add the title Customers with more than one holiday to the report. Make sure that the
report fits on a single page and that all data and labels are visible."

Select from all the records only the customers that have booked more than one
holiday:

Create a query from the main table. In the No of Holidays field, go to the criteria box and enter ">10". This will do the filtering job

Calculate for each of these customers the average price of their holidays.:

Create a summary query and select average for the "Holiday price" field.

Group this data by the number of holidays booked. Sort the data within each group into
ascending order of Surname, then into ascending order of Forename.:

Select the relvant option when creating the report.

For each group
show the number of customers and the average cost of the holidays.

Easy. Just add a summation Total field and Average field in the report. :)

Offline abdjed1

  • Newbie
  • *
  • Posts: 14
  • Reputation: 44
Re: Applied ICT June 2009 Problem
« Reply #2 on: March 19, 2011, 03:37:53 pm »
There is no field called No of holidays..how do I get that?

Offline $tyli$h Executive

  • Honorary Member
  • SF V.I.P
  • *****
  • Posts: 5070
  • Reputation: 65403
  • Gender: Male
Re: Applied ICT June 2009 Problem
« Reply #3 on: March 19, 2011, 04:35:33 pm »
There is no field called No of holidays..how do I get that?

The whole thing needs to be done using a summary query, in which you can calculate the total no of holidays. Then imported into a report.