Formula Problem / Query

[ProcalX]

First off can someone link me to a helpful page if there is one so that i can learn all these formulas, i know excel v.well but not access..

My query is:

i have the fields:
Product Quantity Unit Price Discount Discount Total Price
prod1 2 222.50 10.0% 44.50 400.50


now i have a whole number of products being listed, and 1 price for each row.. then below at the bottom of the page i have a Sub-Total / Shipping Charge / Total + VAT.

What is the formula i must use to calculate all the cells listed in the Price column (so i can work out the sub-total automatically)?

See Attached image.

 

[Ron_dK]

ProcalX

I would suggest that you create a query ( based on your table) , which would look something like :

SELECT TbPrice.product, TbPrice.quantity, TbPrice.unitprice, TbPrice.discount, Nz([unitprice])*Nz([discount]) AS [Discount-Total], Nz([unitprice])-Nz([Discount-Total]) AS Price
FROM TbPrice;

If you run the query you'll get :


Price Query
product quantity unitprice discount Discount-Total Price
Test 20 €340.00 10.00% 34 306
Garbage 2 €12.10 5.00% 0.605 11.495

Ensure that the above fields are bound to those on your form.
Create a textbox called : Order subtotal with a control source :
=Sum([Price])

Hth

 

[ProcalX]

See Attached (again)

I have included a new screenshot of the control source i am using in relation to the "order sub form" (calculates the total) and it isn't working, what have i done wrong?

 

[Ron_dK]

Did you follow this :

Create a textbox called : 'Order subtotal' with a control source :
=Sum([Price])

The 'Order Subtotal' should sum all the totals from the field Price.
This field should be the last one as defined in the query.

 

[ProcalX]

See Attached

I have created the text box: Order Sub-Total and put the control source as "=Sum([Price])" saved it and run in view mode only to recieve a "#Error" in italics.

If i am doing something really obviously wrong, could you layout some very simple instructions as this is getting aggrevating!

Thanks for the help

ProcX

 

[Ron_dK]

OK, some notes.

In your table , make sure that the fields quantity = number, unitprice = currency, and discount = currency (percent format).

The property of the textbox format can be selected as currency or general number.

Hope this helps.

 

[ProcalX]

Well i've double checked and i've already set these format options.

Still getting "#Error"

don't understand why..

i don't know if this will help but here is a screenshot of the mainboard in design view for you: (See Attached)

 

[Ron_dK]

Procal,

What record source do you use for the subform.
I reckoned that the records displayed in the subform were based on the query
that I gave you.

SELECT TbPrice.product, TbPrice.quantity, TbPrice.unitprice, TbPrice.discount, Nz([unitprice])*Nz([discount]) AS [Discount-Total], Nz([unitprice])-Nz([Discount-Total]) AS Price
FROM TbPrice;

 

[ProcalX]

So where exactly do i put this "code"?

am i putting that as the control source? or do i have to do something else with it?

 

[ProcalX]

Ok well i've got it sort of working, however it is not calculating the quantity or the discount in the total price, so it's not fully working.

My current SQL code for this is:

SELECT [Total Price of Order Details].OrderID, [Total Price of Order Details].CompanyName, Sum([Total Price of Order Details].[Total Price]) AS [SumOfTotal Price], [Total Price of Order Details].CustomerID
FROM [Total Price of Order Details]
GROUP BY [Total Price of Order Details].OrderID, [Total Price of Order Details].CompanyName, [Total Price of Order Details].CustomerID
ORDER BY [Total Price of Order Details].CompanyName;

 

[ProcalX]

SELECT [Total Price of Order Details].OrderID, [Total Price of Order Details].CompanyName, Sum([Total Price of Order Details].[Total Price]) AS [SumOfTotal Price], [Total Price of Order Details].CustomerID
FROM [Total Price of Order Details]
GROUP BY [Total Price of Order Details].OrderID, [Total Price of Order Details].CompanyName, [Total Price of Order Details].CustomerID
ORDER BY [Total Price of Order Details].CompanyName;

This is the query that is working at the moment to create the "total price"

however it is not working out the discount, nor does it take into account the quantity .. (it's only assuming 1 unit is brought each time).

(i really don't understand this code... hence i've struggling..)

 

[Ron_dK]

Procal,

For your convenience, I have added a sample Dbase. ( Access 2000 version).
Look at the form FormPrice in design view and hope you'll see what I initially ment.

Hope this will get you on track.

 

[ProcalX]

Rak,

Thanks very much for the help, have now sorted the database out and it is working out the subtotal

1 Problem, it still doesnt work out the quantity, it takes into account the discount & subtracts that from the total price, but if the unit price is $330 and you have 22 of them quanitity wise, the total price isn't $330.. if you understand

if u can help me sort this problem, i will then leave you alone & go and take an access course in designing databases

 

[Ron_dK]

OK, see what you mean.
See attachement. Modified to include the total.