Creating calculations

[panjap]

Hello,
Please may someone help with a problem as i am struggling on using Microsoft Acess to manipulate data and crete a system which creates orders for a shop as part of my project. i really would be grateful for any help what so ever.
i have the tables

Products which include Prod_ID , Prod_Name and then Price

Customer Order Details which include in order, Order_ID, Prod_ID, Qty_on_Order, Discount and then Line_Total

i would like to know how to apply calculations so that I can times the price of a product by the quantity and discount and then get the line total

i then want to know what calcualtions are needed to insert a final overall cost as the table Customer Order has
Order_Total( which adds up the sum of all Line_Total in Order details)
and then adds Delivery Cost, and times byOverall_Discount and then to produce Final Costs

i would really be appreciative if any one could help me. i would also like to know where to place these calculations. do i create a new query or use the existing tables

[nico5038]

Normally we use an algoritm iin a query for this.
So place both the Product table and Orderline in a query and join them by the ProductID.
Next add a field in the graphical editor like:
TotalPrice: [Qty] * [Price]

For the overall total re-use this query in a groupby query to get the totals per OrderID and/or ProductID.

Getting the idea ?

Nic;o)

[panjap]

Normally we use an algoritm iin a query for this.
So place both the Product table and Orderline in a query and join them by the ProductID.
Next add a field in the graphical editor like:
TotalPrice: [Qty] * [Price]

For the overall total re-use this query in a groupby query to get the totals per OrderID and/or ProductID.

Getting the idea ?

Nic;o)

i am grateful for your reply. Though unfortunatley do not under stand what you mane,as i do not know what you mean by "algoritm" in a query, and a what a graphical editor is.
i did however make a query but did not knkw how to insert the calculation
Sorry for any trouble caused, and i wouild really be grteful for any assisstance.

[nico5038]

An algoritm is the same as a calculation like:
a = b + c
When you start to build a new query Access will show a window with in the upper half the selected table (from the pop-up form that's displayed first) and below that a serie of cells.
The first row will hold the fieldnames and in that first row you can enter a calculation/algoritm like:
Total:[Qty]*[Price]
Both [Qty] and [Price] need to be fields in the table in the upper half.

Just give it a try and let me know the result.

Nic;o)

[panjap]

An algoritm is the same as a calculation like:
a = b + c
When you start to build a new query Access will show a window with in the upper half the selected table (from the pop-up form that's displayed first) and below that a serie of cells.
The first row will hold the fieldnames and in that first row you can enter a calculation/algoritm like:
Total:[Qty]*[Price]
Both [Qty] and [Price] need to be fields in the table in the upper half.

Just give it a try and let me know the result.

Nic;o)

Hello thank you that is really helpful. the line total worked a treat as a inserted
Line_Total: (Products.Price*[Qty_on_Order]*(1-[Discount])/100)*100
thank you, however i then had another table called customer order.
and here i have the fields

customer ID
Order ID
Order total (this is when all the line totals for one order are added up)
Delivery cost
Overall Discount
Final Costs
i am struggling here to add up the total line order for each order and then add the delivery cost and then any extra discounts to produce final costs.
Please may you help me with this formula as i am really stuck
any help would be much appreciated,
thank you

[nico5038]

The delivery cost and then any extra discounts need to be in the Order table and for the Orderdetails you'll now need to create first a groupby query.
Use the created query with the total and place that in the query editor.
Now place the OrderID and the total field.
Next press the "E" looking GroupBy button to get an additional line in the criteria section.
There change the GroupBy under the total field in "Sum".
Save and run this query to see that you get the Order total.

Finally place the Order table and the above GroupBy query in the editor and connect them by the OrderID. Now you can add a calculation like:
TotalOrder:[tblOrder].[DeliveryCost]+[tblOrder].[Discounts]+[qryGroupBy].[SumOfTotal]

Clear ?

Nic;o)

[panjap]

The delivery cost and then any extra discounts need to be in the Order table and for the Orderdetails you'll now need to create first a groupby query.
Use the created query with the total and place that in the query editor.
Now place the OrderID and the total field.
Next press the "E" looking GroupBy button to get an additional line in the criteria section.
There change the GroupBy under the total field in "Sum".
Save and run this query to see that you get the Order total.

Finally place the Order table and the above GroupBy query in the editor and connect them by the OrderID. Now you can add a calculation like:
TotalOrder:[tblOrder].[DeliveryCost]+[tblOrder].[Discounts]+[qryGroupBy].[SumOfTotal]

Clear ?

Nic;o)

thank you for the information and i sucessfully worked out how to total up the line costs but unfortunatley i am struggling with the final costs. In the same query i typed in
Final_Costs: (Customer Order].[Order_Total]+[Delivery Cost]*(1-[Overall_ Discount])/100)*100
but this did not work. dod you mean i now need to create a new query woth the query i just created with the line totals now added up and with the table customer order. i am vey saoory for your incinvience , and would be very grateful for some help.

[nico5038]

Doing fine I see.
Yes, the "tblOrder" and the "GroupBy query" created for the tblOrderDetails need to be combined (placed) into one new query. There you JOIN them by a drag and drop of the OrderID from the table to the query and you should be able to get the final calculation working.

Nic;o)

[panjap]

Doing fine I see.
Yes, the "tblOrder" and the "GroupBy query" created for the tblOrderDetails need to be combined (placed) into one new query. There you JOIN them by a drag and drop of the OrderID from the table to the query and you should be able to get the final calculation working.

Nic;o)

i am very sorry for troubling you, bu ti have tried again the formula

Final Costs: Sum(([Customer Query making order].Order_Total+[Delivery Cost]*(1-[Overall_Discount])/100)*100)
when creating the query you said, and have inserted relevant fields, however since both are in both tables the error message says the field (Delivery Cost )and other fields coem form both the table and query what suggestion can you pleae offer
i am very soory for troubling you again and i am very thankful for your help

[nico5038]

Like the order_total you need to qualify the fields by adding the tablename infront of them like:
Final Costs: Sum(([Customer Query making order].Order_Total+[yoru delivery table].[Delivery Cost]*(1-[yoru delivery table].[Overall_Discount])/100)*100)

Getting the idea ?

Nic;o)

[panjap]

Like the order_total you need to qualify the fields by adding the tablename infront of them like:
Final Costs: Sum(([Customer Query making order].Order_Total+[yoru delivery table].[Delivery Cost]*(1-[yoru delivery table].[Overall_Discount])/100)*100)

Getting the idea ?

Nic;o)

Hello its me again! thankyou for your help
i entered the formula
Final Costs:Sum(([Customer Query making order].[Order_Total]+[Customer Order].[Delivery Cost]*(1-[Customer Order].[Overall_Discount])/100)*100)

but the result came with a total(also bigger then when i added them all up indvidualy). how do i only get this for one order only.
Also when I tried to enter the customer ID and Order ID
It says “you tried to execute a query that dos not include the specified expression ‘Order_ID’ as part of an aggregate function
please will help resove this problem

[nico5038]

Can you use the button top left to switch to SQL mode ?
Copy/paste that here so I can have a look.

The error indecates that the Ordernumber isn't part of a groupby, did you press the "E" looking button ?

Nic;o)

[panjap]

Can you use the button top left to switch to SQL mode ?
Copy/paste that here so I can have a look.

The error indecates that the Ordernumber isn't part of a groupby, did you press the "E" looking button ?

Nic;o)

here it is form sql

SELECT Sum(([Customer Query making order].[Order_Total]+[Customer Order].[Delivery Cost]*(1-[Customer Order].[Overall_Discount])/100)*100) AS [Final Costs]
FROM [Customer Order] INNER JOIN [Customer Query making order] ON [Customer Order].Order_ID = [Customer Query making order].Order_ID;

also i did not type the "E" looking button for this query but did so only for the query C"ustomer Query making order" to add up all the line totals
thank you for your help!

[nico5038]

Try:

SELECT [Customer Order].Order_ID, Sum(([Customer Query making order].[Order_Total]+[Customer Order].[Delivery Cost]*(1-[Customer Order].[Overall_Discount])/100)*100) AS [Final Costs]
FROM [Customer Order] INNER JOIN [Customer Query making order] ON [Customer Order].Order_ID = [Customer Query making order].Order_ID
Group By [Customer Order].Order_ID;

And can you also post the SQL for the [Customer Query making order]

Nic;o)

[panjap]

Try:

SELECT [Customer Order].Order_ID, Sum(([Customer Query making order].[Order_Total]+[Customer Order].[Delivery Cost]*(1-[Customer Order].[Overall_Discount])/100)*100) AS [Final Costs]
FROM [Customer Order] INNER JOIN [Customer Query making order] ON [Customer Order].Order_ID = [Customer Query making order].Order_ID
Group By [Customer Order].Order_ID;

And can you also post the SQL for the [Customer Query making order]

Nic;o)

thanks fot that
it worked but for some reason the oringinal total order is timsed by 100 and therefore with a total cost of £64 and a delivery of £3 should come to £67 but instead comes £6,403

also the overall discount does not seem to becalculated

What did you mean by "you can also post the SQL for the [Customer Query making order]"
do i need to do this?
thank you for all your help

[nico5038]

That will be a bracketing problem, try:

SELECT [Customer Order].Order_ID, Sum([Customer Query making order].[Order_Total]+([Customer Order].[Delivery Cost]*(1-[Customer Order].[Overall_Discount])/100)*100)) AS [Final Costs]
FROM [Customer Order] INNER JOIN [Customer Query making order] ON [Customer Order].Order_ID = [Customer Query making order].Order_ID
Group By [Customer Order].Order_ID;

Nic;o)

[panjap]

That will be a bracketing problem, try:

SELECT [Customer Order].Order_ID, Sum([Customer Query making order].[Order_Total]+([Customer Order].[Delivery Cost]*(1-[Customer Order].[Overall_Discount])/100)*100)) AS [Final Costs]
FROM [Customer Order] INNER JOIN [Customer Query making order] ON [Customer Order].Order_ID = [Customer Query making order].Order_ID
Group By [Customer Order].Order_ID;

Nic;o)

thankyou this worked
however there was one small problem for the final cost the overall discount applied only to the delivery cost and not the delivery cost and order total added together
here is sql

SELECT [Customer Order].Order_ID, Sum([Customer Query making order].[Order_Total]+([Customer Order].[Delivery Cost]*(1-[Customer Order].[Overall_Discount])/100)*100) AS [Final Costs]
FROM [Customer Order] INNER JOIN [Customer Query making order] ON [Customer Order].Order_ID = [Customer Query making order].Order_ID
GROUP BY [Customer Order].Order_ID;




rcheers for your help

[nico5038]

Glad I could help, success with your application !

Nic;o)