Getting a grand total from a sum of a column and one field?

[LELF333]

I have recently started my AS ICT coursework and I am having one problem with one of my queries. I need to create an invoice query to show the total amount spent by customers while they stay in a camping site (I will use this query to make a report). I have 5 tables, these including information about accommodation prices and also meal prices. I have worked out the total for the cost of the accommodation of customers stay, but I am struggling with the next bit. Customers are able to book many meals while they stay. The meals are on a set menu with certain prices. I need to be able to add the accommodation price for the week on to the total price of all the meals the customer has ordered.

Accommodation ID Price per night Number of nights Description Date of meal Number of meals Price per person Total cost of accommodation Total cost of meals Acc002 £75.00 5 Breakfast Light 24/10/2015 2 £3.50 £375.00 £7.00 Acc002 £75.00 5 Breakfast Standard 23/10/2015 2 £5.00 £375.00 £10.00 £17.00 <caption> Invoice </caption><thead> </thead><tbody> </tbody><tfoot></tfoot>

<tbody>
</tbody>

This is one part of my invoice, It shows the [Total cost of accommodation] and the [Total cost of meals]. Basically I need a way to add the TOTAL of the [Total cost of meals] (In this case it is £17.00 but depending on how many meals are ordered by the customer this could be more or less) to the [Total cost of accommodation] (Which in this case is £375.00). I don't like the fact that I have to have the duplicate value of the accommodation due to the fact it is in the same query as the meals booked. Any suggestions on alternate ways that I can setup the query differently will be welcomed.

Thanks.

 

[strive4peace]

it seems to me that the meals are per date while the accomodation is per stay.

perhaps a daily total instead of trying to combine apples and oranges?

This would be easier with a report than a query.

In a report, you could have a subreport for meals that you can show detail for and sum. then add that sum to the price for accomodation

 

[Micron]

I agree. Create a query that counts the #of nights in the stay and totals that cost, and one that lists all items related to the stay. Create a report with the first query, and add a sub report based on the second. The link between the Parent & Child reports is the Accommodation ID. Put the subtotal, taxes (etc.) and grand total in the report footer.