expression "% Disount" gives error Overflow message 1

[ps32208]

Hi all,

I have created an expression in an access query which tells me a % discount, calculated from the list price and and net price: Expr1: (

• -[Net])/
  • *100 . The problem is this lists customers many times so I just want to see the customer once and a total price (net and list) together with the overall % discount, so I have grouped the query and changed the total to sum for both the list and net price fields. When the query is run I receive error message Overflow.
    
    With out the expression the query gives me the required result, I just need to add % discount to the grouped query... Does anyone know what I should do?
    
    Regards,
    Pete.

 

[lameid]

Overflow? Sounds like you are trying to divide by 0... If list is 0 then you have a problem.

Anyway for the overall discount I think you want
(Sum(

• )-Sum([Net]))/Sum(
  • )*100

 

[lespaul]

Why don't you post the SQL that you tried?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[ps32208]

Hi Lameid,

Thanks for your quick response. I have tried your suggestion but still no success. I will try and explain my problem a little clearer than initially.

There are 3 fields in my query plus the expression.

1. Customer Name (table a)
2. List (table b)
3. Net (table b)
4. Expression to calcualate percentage discount as above.

I want to group the query by customer showing me a sum of list and net and a % discount for each, so for each customer there is a total list & net & an overall % discount. How best should I achieve this?

 

[lameid]

Post your SQL as Leslie suggested... We can just fix it from there.

You do know about hitting the capital Sigma (Funny looking "E" context help of Totals)on the toolbar to make your query an aggregate and then setting the Total row to Group By to group by a field and Sum to sum it right?

 

[ps32208]

Hi Leslie,

Here is the sql, I shorted the field names previously...:

SELECT [Detail - Product_Sales].[Customer Group ID], Sum([Detail - Product_Sales].[Sell to List CLC $]) AS [SumOfSell to List CLC $], Sum([Detail - Product_Sales].[Sell to Net CLC $]) AS [SumOfSell to Net CLC $], ([Sell to List CLC $]-[Sell to Net CLC $])/[Sell to List CLC $]*100 AS Expr1
FROM [Detail - Company] RIGHT JOIN [Detail - Product_Sales] ON [Detail - Company].[Customer Group ID] = [Detail - Product_Sales].[Customer Group ID]
GROUP BY [Detail - Product_Sales].[Customer Group ID], ([Sell to List CLC $]-[Sell to Net CLC $])/[Sell to List CLC $]*100;


Pete.

 

[ps32208]

Hi,

Yeah, I use this function thanks but must be doing something wrong. Some of the data is "0" in both net and list, as you suggested earlier maybe this is the problem. If so what is the remedy?

 

[ps32208]

I think you have answered it Lameid. I have just tested this on some data without zero's and it works fine. will try and exclude the data with zeros now... many thanks for your help.

 

[lameid]

You are clearly not summing correctly on your fields try..

SELECT [Detail - Product_Sales].[Customer Group ID], Sum([Detail - Product_Sales].[Sell to List CLC $]) AS [SumOfSell to List CLC $], Sum([Detail - Product_Sales].[Sell to Net CLC $]) AS [SumOfSell to Net CLC $], (Sum([Sell to List CLC $]) - Sum([Sell to Net CLC $]))/Sum([Sell to List CLC $])*100 AS Expr1
FROM [Detail - Company] RIGHT JOIN [Detail - Product_Sales] ON [Detail - Company].[Customer Group ID] = [Detail - Product_Sales].[Customer Group ID]
GROUP BY [Detail - Product_Sales].[Customer Group ID];


Notice the removal from group by and the change to sums in the select

 

[ps32208]

that has done the trick, many thanks.