Solved

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

Posted on 2013-06-27
11
378 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

746 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

12 Experts available now in Live!

Get 1:1 Help Now