Three Fields - will NOT be equal -

[pj33558]

I am trying to figure out the best way to accomplish this task/calculation.

I have five fields. Comm, Comm2, Comm3, %, Amount (Row # is for reference in my example)

Most often, Comm2 and Comm3 are blank, and never are Comm, Comm2, and Comm3 filled for same row, and Comm will ALWAYS have something in the field.

What I need is:

Row 1: easy: amount X % which gives $30 for JP
Row 2: amount x 1% for Comm2 AND amount x (3% - 1%) which gives $15 for BD and $30 for JP
Row 3: amount X 1% for Comm3 AND amount x (3% - 1%) which gives $12.50 for GP and $25.00 for JP
Row 4: same as row 2
Row 5; FP gets nothing, all % go to JP

I have tried: IIf(IsNull[Comm2] AND IsNull[Comm3], [Amount]*[%], [Amount]*([%]-.01) AND [Amount]*(.01))

and I failed miserably

(have I mentioned I've not done this sort of thing in years?!)

Thank you for guidance, tips, hints, suggestions!

Row #	Comm	Comm2	Comm3	%	Amount
1	JP			3	100
2	JP	BD		3	150
3	JP		GP	3	125
4	JP	SP		3	1000
5	JP		FP	3	200

[June7]

If Comm2 and Comm3 are mutually exclusive, why not one field? Could the values shown in each field ever be in the other field? Regardless, they both get 1%.

Why does FP get nothing?

Calc results shown wrong - 3% of 100 is only 3, not 30, but I think I get the idea.

Amount * (0.03 - IIf(Comm2 & Nz(Comm3, "FP")="FP", 0, 0.01)) + IIf(Comm2 & Nz(Comm3,"FP")="FP", 0, Amount * 0.01)

[pj33558]

I am linked by ODBC to the GL. Those two fields were set up that way long before my time and unfortunately, can't be changed at this time. I was originally brought into develop a quick and dirty Access tool and was told it was mostly complete.

HA! Not even close. The original design had to be thrown out, nothing was salvageable.

FP gets nothing because it is a generic account for when the CEO sells something (he doesn't get commission on sales).

So the table above with the Comm, Comm2 and Comm3 are linked to an employee table that has the % for each type of sale. The table contains a bunch of other data I use downstream as does payroll.

The table linked by ODBC pulls the amounts, customers, names, sales types, where sold, quantity, amounts, regions, etc etc etc... I have linked the ODBC table to the Employee table by EMP column.

When the Emp name shows up in the Comm2 or Comm3 column, all the % are ignored; they are paid only 1%.

If their name shows up in the Comm column, they are paid by the % listed below.

For instance the employee table looks like this:

Emp	ID	FNLN	PCS	PDS	SSD	DDI	SSI	PSI
Jane	JP	JanePage	3%	2.5%	5%	6%	6.2%	10%
Mitch	MP	MitchPrior	2%	3%	4.5%	10%	8%	12.5%

[June7]

I did some edits on my previous post. Make sure you review it again.