[Webinar] Streamline your web hosting managementRegister Today

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 456

# How do i do calculation by using multiple fields and array. Please see attached database

how can I do calculations using multiple fields and array?
For example 4 questions

1. If field1=Mike and Field3=Yes, Field4+Field4
2. If field1=Nik and Field 3=Yes, Field4+Field5
3. If field 1=Peter and Field3=Yes, Field4+Field5
4. If field 1=Sam and Field3=Yes, Field4+Field5

Database-2.accdb
0
maximyshka
• 6
• 4
1 Solution

Commented:
0

Author Commented:
Thanks a lot.  But can you make some adjustments:

1) Please avoid hard coding of the customer name. Actual data will be changed based on the download on monthly basis.  So, instead of Nik and Peter will be somebody esle.  In addition, what if we have 10 cases instead of 4. I added 4 more customers in the attached database (all together should be 8 records)

Although, I previously asked to consider speed of the query.  Should we use database connection code?

Function getTotal()
Select Case Me.txtCustomer
Case "Mike", "Nik", "Peter", "Sam"
getTotal = Nz(Me.txtNum) + Nz(Me.txtNum2)

Case Else
getTotal = Null

End Select

Note: the idea with selecting cases is great but we cannot use hard coding if we have multiple values

2) Value of in the calculated field only changes if you make appropriate selection in the form and click in the form window which correspond to Calculated field.  However value of the total field should be changed no matter if I click on the calculated field window.  See information for Fred (ID #5)

3) Not sure if we can answer crucial question which I previously mentioned:

If Field3="Yes", summarize calculated field value of all records which have value of "Field3" as yes and put it to the table

For example for following record

ID=1, Field1=Mike, Field3="Yes" --Sum of (Field4 + Field5) for Mike and Nik for both records
ID=2, Field1=Nik, Field3="Yes"

If field3 value is "Yes" for 4 records, then we need Sum of (Field4+Field5) for 4 records and put it to [Calc Field]

I think they need such calculations for weighted value
Database-2-rev12.accdb
0

MIS LiasonCommented:
You are now asking for something that is much more detailed than your original question...

Can you just post a clear, graphical example of the "exact" output you need for each contingency?
0

Author Commented:
Hi Jeff.

Based on copy of the database which I provided in comments ID # 39283896

Calc field should be for all fields  see below (after the data)

ID       Field1      Field2      Field3      Field4      Field5      Calc Number
8      Tom        1/4/2013      Yes              3             15
7      David      1/3/2013      Yes              5             25
6      Peter      1/2/2013      Yes              2              20           22
5      Fred              1/1/2013      Yes         4             10
4      Sam         12/10/2012      Yes      3            20          23
3      Peter      12/11/2012      Yes      2              15        17
2      Nik              12/13/2012      Yes      2              20          22
1      Mike            12/12/2012      Yes      1              2           3

Since Field3 for all records was changes to "Yes" that Calc Number should be based on the Sum of Calc Field for all records 149;
In records where Calc Number is missing it should be fixed (see part #2) from previous comments
0

MIS LiasonCommented:
The reason why this is not easy is because of the design you have here...
The records here (what you are calling "Cases" I believe), ...should be in the columns, this way hardcoding can be avoided...
The way you have it now, you would have to manually edit the code every time you edit, add or delete a person's name...
This would also probably solve the need for NZ()

But perhaps I am missing something, ...let's see if capricorn1 comes back...

JeffCoachman
0

Author Commented:
Jeff, would you please review question #  28171688.  I put new question based on some provided comments and made some suggestions to the code. Also, I made some adjustments to database. Thanks
0

MIS LiasonCommented:
...I still think the design you are working with is the sticking point.

But as you can see, you have a great expert there willing to assist you...
So you may be close to a solution.
;-)

JeffCoachman
0

Author Commented:
Jeff, I would appreciate if you highlight any issues with design.

Regarding the answer: Provided answer should work on the form level.  I would not ask this question if I need it on query and table level.

Not sure about update statement during form run-time. This can be an issue
0

Author Commented:
0

MIS LiasonCommented:
maximyshka,

<Jeff, I would appreciate if you highlight any issues with design. >
I posted my thoughts in: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28170251.html#a39287131

To be honest, ...again, this question is quite odd and complex.
capricorn1 is the Top Access expert and gave it his best shot, and it was not what you wanted.
I am afraid, I can do no better.

again, when questions like this come up (that have no direct answer, either:
1. The question is too complex and should be broken up into separate posts.
2. The design is not optimal (again, having to do something like this is typically a sign of an un-normalized design:
...Nz(Me.txtNum) + Nz(Me.txtNum2)
and again, setting harcoded values: ( Case "Mike", "Nik", "Peter", "Sam", ...etc), is a nightmare to maintain.

Sorry I could not help more, ...but let's see if capricorn1 returns...

JeffCoachman
0

Author Commented:
Jeff, Capricorn, the Question # 28171688 reflects updated information and changes in the database design as suggested.  I simply ask you to look at the code listed in the question # 28171688. The design which was previously provided in this question was fixed.

This question will be closed later together with the question # 28171688.

However, Question # 28171688 is the most critical for the answer
0

## Featured Post

• 6
• 4
Tackle projects and never again get stuck behind a technical roadblock.