Hello, I'm new here but if possible, I'd like to ask for some help regarding a database I'm working on.
You see, I'm currently creating a mock cafe database that must be able to accept information regarding customers, their orders, and items sold by the cafe. From this, I have identified the three main data values needed for the database's tables, which are customer, Item, Order and Order Item, respectively. Below is an images showing my database's relationship diagram, with the tables and their internal information.
Relationships:
Currently, I have finished the tables and the forms used to input the customer, items and orders, but I am currently having issues involving the calculation of the price of orders. You see, when a user wants to make an order, they use the form shown on the form shown below:
On this form, the user selects a customer ID number, followed by entering the order date and collection time, while an order number is automatically generated. The whitebox is a subform,where the user selects an item ID (which also shows the item's name and cost when browsing through its combobox), enters the quantity of the item being ordered, and adds any additional information or price supplement if necessary. As can also be seen, once the user selects an item ID, another box automatically appears in the subform where the user can enter another item and its details if necessary. Once the user is done, they then select the "Save Record" button, which saves the order into the "Order" and "Order Item" tables.
Now, the issue I have is that I need to create a form that acts as an invoice for the order, which shows two calculations, the total price of multiple items of the same type within an order added any price supplements they have (eg, the cost of two coffees within an order, + a supplement of 20p) and total cost of an entire order (eg, the total price of the two coffees and supplement, + the price two sandwiches, to give the order's total cost). I have already figured out the first calculation through the query in the image below:
Item Cost Query (Design View):
So the issue I have is creating a query that calculates the total cost of an order, which is the prices of all items within an order added together. I have tried several times but without success. I am also unsure as to how to display the results of these queries into my currently unmade invoice form and to format said form and ensuring reports so that they only show the calculations for the saved order. If anyone could help with this I would be very thankful.