Calculating percent discount

[Peter Simpson]

Hi All

1. I have an order table that has a percent discount field that is populated from the Order form. Am I correct to assume that the calculation should take place in the Orders query?

2. If I am correct in my assumption in the above point, where in the query do I run the calculation? I have already included the Discount field in he query, so do I do the calculation there? If so, what is the best coding to use?

If I have missed the plot entirely will someone please put me on the right track?

[ranman256]

if the discount applies to the entire order then
Q1 to sum all amt in the order
Q2 to apply txtDiscount on the form and Q1.Total to create a SubTotal

[Peter Simpson]

I am assuming tha the "GroupBy" function pops up? hem which option do I use?

[ranman256]

Group by is for the fields NOT to sum. (like clientID)
SUM is for those you do.

[Peter Simpson]

Please can you clarify where I must put the calculation and how you would suggest I should write it?

[Peter Simpson]

I used the following calculation in a calculated field in my query

SalesPrice-(SalesPrice x (Discount/100))

eg 300-(300 x (50/100)) = 150

Do you agree?

[Peter Simpson]

Query in design view

[Bulzie]

If that calculation is correct yes that seems a good place. you can rename it to DiscountPercent: or something instead of Expr1 and then use that as a field on the form. Assume SalesPrice is another field in the query?

[Micron]

why not just enter the discount rate as the decimal representation of the percentage (e.g. 25% is .25)? Makes it easier to write the expression regardless of which way you want to apply a rate. For example DiscAmt: [amt]-[amt]*[discount] No parentheses required either since the arithmetic operations will follow their natural course. The expression becomes 100-100*.25 = 75 To add 25%: 100*1.25 = 125

Most of us would agree - do the calculation in the query or via calculated form controls. The former is usually better, IMHO. Rather than use the default alias of Expr1:, use your own meaningful field name, such as DiscAmt (discounted amount if that's the final figure you're creating).

[orange]

You seem to be calculating the DiscountedPrice. But your post title is Calculating Percent Discount??

[Peter Simpson]

Oh hell...Orange...you are right. Thanks for all the help guys, I have managed to sort out the calculation and where to place it thanks to you guys....just one small issue remains......

When I place it in the report I get the Label but the amount does not show...Why? What do I do?

[Micron]

In the property sheet for the report control, you have a table or query field set as its control source and that field is part of the query if it's based on a query?
Or your report control calculated expression is using fields that are available to the report (i.e. are part of the report record source)?
Or the record you're looking at does contain the required values?
Or is this no longer a calculation of a discount amount but is instead a discount rate field from a table or query?
Hard to know what your issue is now without more information about what you're doing at this point.