Help with a cafe database

How are you going to present this invoice to the customer?  It should be a printed report or at least a form (don't even think about printing a form) that can calculate the total in the form/report header/footer section or a report group header/footer simply by using a text box with a =Sum(price) kind of expression

I don't see where using a query to do that helps you, but if it is necessary, use a subquery in a calculated field or maybe a DSum expression.

How are you going to present this invoice to the customer?  It should be a printed report or at least a form (don't even think about printing a form) that can calculate the total in the form/report header/footer section or a report group header/footer simply by using a text box with a =Sum(price) kind of expression

I don't see where using a query to do that helps you, but if it is necessary, use a subquery in a calculated field or maybe a DSum expression.

I was going to do both. I would add a button to the order form shown above that when clicked, takes the user to a form that shows the order details, as well as  the item prices and the total cost.There would be a button from there that takes the user to a report that shows the same details, but can be printed.

How would I use a form/report footer and textbox in this case? Would it be like creating the form, making acombo box that selects my order details, with other textboxs to be populated with the information bar the prices details, which use textboxes with the expression mentioned? If so, how would the expression work in case in consideration with the query I have already made for the first calculation?

Create an invoice form (continuous) based on your query without the calculated total field.  In the form's Footer section add a text box with the expression:

    =Sum([item cost] * quantity)

Do the same thing for the invoice report.  You might be able to get away without the invoice form by just opening the invoice report in PrintPreview (or, if it will always fit on one page or you use a continuous sheet kind of printer, maybe(?) using Report view).

First, This is one of the situations where the rule of relations databases against repeating data doesn't work. Price is a time sensitive value and you need to capture the price at the time of the order. So you need a unitcost field in your Order Items table. This can be filled in automatically by including the cost in the RowSource of the Item combo, then use the Column property to assign it to the Cost control. 

From there you can calculate the Extended cost by multiplying the quantity by the unitcost both on your form and in a query that feeds the invoice. 

Just wanted to say thank you to everyone who provided help as it has been very helpful. I have been able to add the order invoice form into my database, which includes a subform used to view item details of the user's selected order.

Invoice form:

Subform:

The total price and total sum calculations have been performed within two of the subform's textboxes rather than in queries and it seems to work fine, although it won't display the values in pounds, but as pure numerical values. So apart from that issue of showing the prices as pounds, I now only need to create a report version of the invoice, which should be relatively easy I take it if I follow guides made by microsoft on this site.

To show the total in pounds, simply change the Format property of the textbox to "Currency". The currency symbol is just for display; it's not stored in the table.

Thank you to John Vinson, that advice solved the issue.

So, I'm getting there. I'm currently working on a bunch of reports to show data in a hard copy format.

However, an issue has arisen with the Invoice form. For whatever reason, when I enter an order with a customer who has already made a previous order, my invoice form is showing items from both of the customer's orders. Below is an example of this occurring:

Selecting the order number 1:

Note in the centre of the drop down table that appears from the combobox, between the collection time and customer forename, this column represents customer ID. As can be seen from the image, the order that I am selecting (layered in black) has customer ID number 3 assigned to it, as does order number 5. Below shows the subform after the user selects order 1 from the above combobox.

As can be seen from the above image, the subform shows two items, items 40 and 44. This is in error compared to the "Order Item" table, which clearly shows that there is only one item in the order:

I am unsure what the error is that is causing this issue, but I believe it has something to do with the query I am using for the invoice's subform. I will post images of the queries used for both the invoice and the subform, as well as for the design views of both forms, showing the properties of the order ID textboxes within each, in case they help provide information that could be helpful.

Invoice form query:

Invoice Subform query:

Invoice form design (with listed "Order No" combobox properties):

Invoice Subform design (with listed invisible "Order No" textbox's properties):

Again, thanks for all the help so far, it has helped a ton and it is getting this project closer to completion.

I'll have to look tomorrow after I've had some coffee (it's near midnight here). But... if your Query is linking on CustomerID you're seeing just what I would expect - all the records for that customer. If instead you want to see all the records for a particular Order you must instead link on the OrderID.

Note that posting screenshots of query designs is not ideal. Instead, if you're trying to get help with a Query, open it in SQL view (one of the options on the View button to the left of the ribbon) and copy and paste the SQL text into a message. It may look like Old Cuneiform Akkadian at this point, but it's the real query (the grid is just a tool to build SQL) and a lot of us are fluent in Old Akkadian :-{)

Similarly, a picture of a combo box is less informative than the SQL text of its RowSource query. This is one case where a few words are worth a thousand pictures!

I'll have to look tomorrow after I've had some coffee (it's near midnight here). But... if your Query is linking on CustomerID you're seeing just what I would expect - all the records for that customer. If instead you want to see all the records for a particular Order you must instead link on the OrderID.

Note that posting screenshots of query designs is not ideal. Instead, if you're trying to get help with a Query, open it in SQL view (one of the options on the View button to the left of the ribbon) and copy and paste the SQL text into a message. It may look like Old Cuneiform Akkadian at this point, but it's the real query (the grid is just a tool to build SQL) and a lot of us are fluent in Old Akkadian :-{)

Similarly, a picture of a combo box is less informative than the SQL text of its RowSource query. This is one case where a few words are worth a thousand pictures!

Ok. I'm still used to working in design and datasheet views, I haven't had much experience in using the SQL view. Here they are for both the Invoice query and subform query.

Invoice query SQL view:

Invoice Subform query SQL view:

If you want to produce an Invoice only for a specific Order, then you need to filter for that Order. what I would recommend is that you have a button on your Orders form to print an Invoice. The code behind that button should look like:

DoCmd.OpenReport "rptInvoice",acViewPreview,,"[OrderID] = " & Me.OrderID

Make sure you use your actual object names, but the idea is to only include Order records that match the currently displayed Order.