Hi,
I'm using a query which shows the total spend DDP/EUR per month per profit centre (given by the profit centre). I have also added what the total should be (COGS 5999 or 1300).
I would like to add (in the query) the percentage of reported total spend
As -> total spend (given by company) / COGS 5999 = ..%
How can I put that in my query? As I do not know where to place it (i.e. under...
Tischtennisbal
Last Activity: Mar 27 '07, 09:22 AM
Joined: Nov 1 '06
Location:
-
Entering formula in field
-
SELECT DISTINCTROW Rebates.Contrac tSupplier, Rebates.[Rebate level 1], Rebates.[Rebate level 2], Rebates.[Rebate level 3]
FROM Rebates
WHERE (((Rebates.[Rebate level 3])=IIf([Rebate level 1]>=[Rebate level 2],[Rebate level 1],IIf([Rebate level 2]>=[Rebate level 3],[Rebate level 2],[Rebate level 3]))));
Comma's don't work with my english/american version of Access. Therefore you should substitute the dot-comma's... -
This is my query:
CODE]IIf([Rebate level 1]>=[Rebate level 2];[Rebate level 1];IIf([Rebate level 2]>=[Rebate level 3];[Rebate level 2];[Rebate level 3]))[/CODE]Code:SELECT DISTINCTROW Rebates.ContractSupplier, Rebates.Product, Rebates.[Rebate level 1], Rebates.[Rebate level 2], Rebates.[Rebate level 3]
- When I put the formula in rebate level 1, 13 or 56 records are found;
- When I put the formula in...Leave a comment:
-
Unfortunately still the same. I don't get all records.
Although I find it peculiar that the query returns more records if I put the formula @ the criteria for round 2 or 3 compared to round 1.
Also adding "MaxRebate: " to the formula gives me an error, but the formula works without... Any idea why?Leave a comment:
-
I did almost the same as you.
But for some weird reason, it does not give all of the highest rebates...Code:IIf([Rebate level 1]>=[Rebate level 2];[Rebate level 1];IIf([Rebate level 2]>=[Rebate level 3];[Rebate level 2];[Rebate level 3]))
It only gives around 40% of all records... Now I have no clue whyLeave a comment:
-
It doesn't matter if mutiple rebates are the same. As long the highest rebate is taken. And 3 is the maximum rounds.
I used your formula, but it gives an error:
"The expression your entered has an invalid (.) dot or ! operator or invalid parentheses."
I have the english version, but even changing comma's for dot comma (;) does not work.
Also should I put the formula is a new field...Leave a comment:
-
Max function
Dear all,
I have to calculate the highest rebate percentage during 3 meeting round. The rebates differ almost every round (can be higher, can be lower)
I want to know how I can calculate the highest rebate without showing the other lower values.
E.g.
Round 1 Round 2 Round 3
Product 1: 0,05 0,03 0,07
Product 2: 0,02 ... -
Unfortnunately it gives an error. "Syntax error in FROM clause"Leave a comment:
-
I hope that is useful to you. I use the output to calculate savings in excel, but the information comes from this query.
The only thing needed is access to convert the percentages to Y or N.
I have no clue, I aint brilliant with Access. My hopes are on you!
Thanks for helping! :-)Leave a comment:
-
SQL CODE:
...Code:SELECT Spend.[3Po product code], Products.ProductName, Products.Category, Profit_centre.[Profit centre code], Profit_centre.Region, Spend.[Local Supplier name], Supplier.[3PO Supplier name], Mothercompany.Mothercompanyname, Spend.[3PO Agreement number], Spend.[Delivery date], Spend.[Volume delivered KG], Spend.[Invoiced amount EUR], Spend.[Total spend DDP/EUR], Products.[Indexed product] FROM (Mothercompany INNER JOIN
Leave a comment:
-
Nothing.
I've putin the criteria range of "Indexed product" (in the query).Code:IIf(IsNull([Indexed product]);"N";"Y")
No other info or formulas are added to the criteria. (I hope this is what you mean.)Leave a comment:
-
The data type of "Indexed products" is indeed Number, but the error (data mismatch...) remains. If there another solution to my problem?Leave a comment:
-
Iif Formula
I'm looking for a formula to convert data.
The table Products contains a column called "Indexed products" in which every product have (or don't have) a percentage.
Using the iif formula, I want to "convert" the data into yes (Y) or no (N).
E.g. Product 1 = 5%, then it should be Yand if no percentage is stated than N.
So far i have this written in the criteria in the query:...
No activity results to display
Leave a comment: