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.

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]

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>
[TH="bgcolor: #c0c0c0"] Accommodation ID [/TH]
[TH="bgcolor: #c0c0c0"] Price per night [/TH]
[TH="bgcolor: #c0c0c0"] Number of nights [/TH]
[TH="bgcolor: #c0c0c0"] Description [/TH]
[TH="bgcolor: #c0c0c0"] Date of meal [/TH]
[TH="bgcolor: #c0c0c0"] Number of meals [/TH]
[TH="bgcolor: #c0c0c0"] Price per person [/TH]
[TH="bgcolor: #c0c0c0"] Total cost of accommodation [/TH]
[TH="bgcolor: #c0c0c0"] Total cost of meals [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

[/TD]
[/TR]
</tbody>[/TABLE]

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.