Solved

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

Posted on 2013-06-27
11
407 Views
Last Modified: 2013-07-13
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

In addition, I need sum of calculated field if all 4 cases have field3=Yes".  Please help.
Database-2.accdb
0
Comment
Question by:maximyshka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39283508
0
 

Author Comment

by:maximyshka
ID: 39283896
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39285559
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:maximyshka
ID: 39286210
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39287131
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 Comment

by:maximyshka
ID: 39287768
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39288184
I reviewed your new question.
...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 Comment

by:maximyshka
ID: 39288253
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 Comment

by:maximyshka
ID: 39291924
Jeff, Capricorn. Would you please help with the question #  28171688. It's not going anywhere.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39292193
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 Comment

by:maximyshka
ID: 39295006
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question