Query doing a calculation across many to many relationship

[Killer Whale]

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.

[Robeen]

Have you started with the query?
If so - could you post the SQL for the query here?

If not, you could:
1. Create a new query.
2. Add all the Tables that have any of the data you will need your query to show.
3. Add the Fields from each Table that you want to see in your query [Customer Name, Amount of Purchase].
If your Relationships are already set up between your Tables - you should not need to join the tables in the query - the joins should sutomatically appear in the query window.
4. Run the query and make sure that all the data you need for your calculation to work is in there.
5. Post the SQL for the query here and tell us what you need to do next with the fields in it.

Basically, what you will do once you've got all the data in your query, is to:
1. 'Group the Customer Name field'.
2. 'Sum the Amount of Purchase field'.
3. 'Add the Filter Criteria': > 150 to the field in which the Sum of Purchases is calculated for each Customer.

Hope this helps!

[Killer Whale]

Just quickly:
SELECT Customer_Tbl.FirstName, Customer_Tbl.LastName, Order_Tbl.SumofTax, Stock_Order_Tbl.Order_StockID, Stock_Order_Tbl.StockID_FK, Stock_Order_Tbl.OrderID_FK, Stock_Order_Tbl.Quantity, Stock_Tbl.Cost
FROM Stock_Tbl INNER JOIN ((Customer_Tbl INNER JOIN Order_Tbl ON Customer_Tbl.CustomerID = Order_Tbl.CustomerID_FK) INNER JOIN Stock_Order_Tbl ON Order_Tbl.OrderID = Stock_Order_Tbl.OrderID_FK) ON Stock_Tbl.StockID = Stock_Order_Tbl.StockID_FK;

Relationships:


I'm currently trying to wrap my head around this: link, dunno if it'll help.

[Robeen]

That link should help. I've found Microsoft Help pages pretty good if you can find the page that addresses your need.

In its simplest form - you will be trying to get to this:
Select these fields:
Name - ProductOrdered - QuantityOrdered - ProductPrice

Add another field like this [I'm not doing the Tax part]:
CustomerPrice: QuantityOrdered * ProductPrice

Now you should have all the fields you need to get to your final query.
Save the Query [I'll call it Query1].

At this point I often create a new Query and use the first Query [Query1] as the data source.
Pull in Name and CustomerPrice from Query1 into the field list.

To the top - right of your Tool Bar you should see a large yellowish Orange button named 'Totals'. Click that.
It will put 'Group By' in the 'Total: row of your query field list.
Leave the Name 'Group By' alone.
Click in the CustomerPrice 'Group By' row & drop the list down and select 'Sum'.
Run the query.

If everything is done right you should see each Customer name once and the total amount of each Customer's purchases.

I hope this helps!

[Killer Whale]

Thanks bud, I just popped "group by" in there and it virtually did itself; I suppose SQL is used to pulling data from relational databases!

SELECT Customer_Tbl.FirstName, Customer_Tbl.LastName, (Sum([Stock_Tbl].[Cost]*[Stock_Order_Tbl].[Quantity])) AS CustomerSpendings, Customer_Tbl.CustomerID
FROM Stock_Tbl INNER JOIN ((Customer_Tbl INNER JOIN Order_Tbl ON Customer_Tbl.CustomerID = Order_Tbl.CustomerID_FK) INNER JOIN Stock_Order_Tbl ON Order_Tbl.OrderID = Stock_Order_Tbl.OrderID_FK) ON Stock_Tbl.StockID = Stock_Order_Tbl.StockID_FK
GROUP BY Customer_Tbl.FirstName, Customer_Tbl.LastName, Customer_Tbl.CustomerID;

SELECT Customer_Tbl.FirstName, Customer_Tbl.LastName, FormatCurrency([Customer Spendings Before Tax].[CustomerSpendings]*(1+[Order_Tbl].[SumofTax]),2) AS TotalSpent
FROM [Customer Spendings Before Tax] INNER JOIN (Customer_Tbl INNER JOIN Order_Tbl ON Customer_Tbl.CustomerID = Order_Tbl.CustomerID_FK) ON [Customer Spendings Before Tax].CustomerID = Customer_Tbl.CustomerID
WHERE (((FormatCurrency([Customer Spendings Before Tax].[CustomerSpendings]*(1+[Order_Tbl].[SumofTax]),2))>150));

I don't really know much of this SQL, but due to the link with QBE in access I can just switch between the two and understand what's going on.

[Robeen]

I'm glad it worked for you. I agree with the 'it virtually did itself' part. Access DOES take care of a lot of the small details.
All the best!