Solved

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

Posted on 2013-06-27
11
389 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

776 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