Solved

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

Posted on 2013-06-27
11
384 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
  • 6
  • 4
11 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now