For school I have this task and I'm really, really stumped beyond any hope of fixing this myself on this one requirement for my task.
The task asks to create and demonstrate a query that "list[s] all customers whose orders total is greater than $150.00 excluding postage"
There's Customer_Tbl, Order_Tbl, Stock_Tbl and Supplier_Tbl tables. Stock_Order_Tbl resolves the many to many between the Stock and Order tables, one customer can make many orders, supplier is irrelevant for this question.
Relevant Fields:
Customer_Tbl: CustomerID (Autonumber PK), Firstname (Text), Lastname (Text)
Order_Tbl: OrderID (Autonumber PK), CustomerID_FK (Number), SumofTax (Number) (I've done this one kind of weird, tax is presented as a multiplier to the final, eg. GST alone would give a tax of 0.1; tax is 0.1 the before tax quantity), ShippingCost (currency)
Order_Stock_Tbl: Order_StockID (Text, PK), StockID_FK (number), OrderID (Number), Quantity (Number, note that this refers to the quantity of each item of stock present in the order)
Stock_Tbl: StockID (Autonumber, PK), Cost (Currency)
So it's like I have to construct a query which can go through the tangle of relationships, read which items are ordered through the Order_Stock_Tbl, pull out the cost field from the Stock table, multiply those figures by the quantity in Order_Stock_Tbl, Sum them all together, *(SumofTax + 1), and at the very end the criteria: >150.
I have no clue how to go about doing that.
An alternative may be to just create a field in Order_Tbl and call it OrderTotal, the problem is that this can very easily lead to data inconsistencies and isn't exactly good normalising.
I'm just a year 12 student doing a computer science course which is half way through 8 weeks (32 in-class hours) of Databasing this year plus carry on from yr 11, so I'm not that fluent with access (note that my postcount = 1); we've only just started SQL and are really just expected just to use Query By Example.