Need to Convert Excel Formula to Access (1 Viewer)

[Jakboi]

Hello,

=IF(AND(I6>1,SUM(SUM(I$7>1,I$8>1,I$9>1,1),SUM(I$7=1,I$8=1,I$9=1))=4),F$6/(SUM(I$7>1,I$8>1,I$9>1,1)),"0")

I have the following for Excel and was wondering if I could use it for an Access form. What this does is the following.

There is a text box for a dollar amount. There are 4 dropdown boxes for employee names. There are 4 text boxes for what credit they receive for the sale.

Example.

$100,000 Sale
4 Employees are involved.
They each receive the total divided by 4.

So there is $100,000 in a text field. When I select 1 employee from the combo box it will place 100,000 into employee credit box 1.

If I select an employee from combo box 2, then each employee will have 50,000 in the credit text box by there name.

If I select an employee from combo box 3, then each of the 3 will have $33,000 in a text box by their name and so on.

Basically it takes the whole amount in box one and will divide the amount if an employee is selected in a dropdown, then place that amount into another text box. I am going to try to convert the above but if anyone has a better idea that would be great.

Thanks.

 

[pbaldy]

I think the solution would depend on your data structure. In Excel it would be proper to have the 4 columns for employee. In Access, you generally wouldn't have a table with repeating fields like that. So in a normalized db, I'd say your solution would be to divide your amount by the count of how many employees are associated with that sale. That count would be based on a separate table that stored each employee/sale as a record (a many-to-many junction table).

 

[Jakboi]

pBaldy thanks for the response.

Ok. I have this so far but I know its far from correct but hoping I can get it down.

=IF(AND(Officer1 Not IsNull,SUM(SUM(Officer2 Not IsNull,Officer3 Not IsNull,Officer4 Not IsNull,1),SUM(Officer2 IsNull,Officer3 IsNull,Officer4 IsNull)) IsNull4),Amount/(SUM(Officer2 Not IsNull,Officer3 Not IsNull,Officer4 Not IsNull,1))"0")

Now it says this error:

You may have entered a preceeding comma without a value or identifier.

Now I understand that the syntax for this is like this from other post I have read or close to that. So it looks like I need an extra value, however when I add them I get same error.

(IsNull(Field),0,0)

This is the screen shot of some help. There will be an amount in the the "Amount" area. When an employee is selected from the dropdown it will place his portion of credit into the "unbound" text area to the right of the field.

 

[pbaldy]

So we're going with the spreadsheet masquerading as a database, eh? Try this:

=Amount/(IIf([Officer1],1,0)+IIf([Officer2],1,0)+IIf([Officer3],1,0)+IIf([Officer4],1,0))

 

[Jakboi]

Hello,

Well I was trying to make this as easy as possible for the person inputting this data and for the credits between the officers be exact everytime. I did try what you suggested but did not work.

I didnt understand your response at first but now reading it again thoroughly see what you mean more now I look at it. I ony tried it the other way becasue I thought it would be easy. I would rather do it the right way.

So I would have a table called maybe sales with the following:

Product
Employee
Employee Credit

Then would the calculation be done in a query?

I have to have a total of each employee at the end of each month.