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

LELF333

New Member
Joined
Nov 14, 2015
Messages
1
:eek: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 IDPrice per nightNumber of nightsDescriptionDate of mealNumber of mealsPrice per personTotal cost of accommodationTotal cost of meals
Acc002£75.005Breakfast Light24/10/20152£3.50£375.00£7.00
Acc002£75.005Breakfast Standard23/10/20152£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.:biggrin:
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

strive4peace

Well-known Member
Joined
Jul 6, 2009
Messages
678
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,518
Messages
5,602,138
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top