Solved

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

Posted on 2013-06-29
397 Views

Data presented in the following format

ID&#9;Section No&#9;Field No&#9;Field Value&#9;Form Value&#9;Calc Number&#9;Answer
1&#9;       1&#9;           1&#9;            0.5&#9;            No&#9;&#9;                         False

Field "Answer" was provided in the format "Yes/No".

The database reflects various records  for various sections.  The goal is to calculate Combined Field Value for the entire sections and put it based on the provided field value for all records which have form value "Yes". The results should be inputted to the field "Calc Number" only to the record of the section which reflect Answer value as "True".  Please see attached database for the details.

Suggestions to do

1) Determine section value from the form by using me.txtSectionNo.
2) Input provided code into variable
3) Create 2 loops:
3.1 First loop is outside loop which go through sections. Loop should increment section number
3.2. Inside loop should go through all records within the section of the form. Get all values for records which have form value (txtformvalue) as "Yes" withing the section, summarize field value (txtfieldvalue) and input it to the Calculated field txtcalcNo only for record which have hidden field txtanswer as "true". This can be done by using either form or database connection
4. Validate that all required records have Combined Calculated Field for the section.  Form should show value with current record, before or after update

I have some suggestions of the code. Please review and fix especially loop parts and test the form in the provided database

``````

Dim x as integer
x= Me.txtSectionNo.value
Do while X>0
Dim Y as double
Do while y>0
Dim z as double
IIF me.txtformvalue="yes", z=me.txtFieldValue, Null

y++
End Do
Loop

x++
End Do
Loop
``````

Would you please adjust the above listed code, test it and provide alternative codes by using form fields and database connection for future references
Database-2.accdb
0
Question by:maximyshka
• 9
• 3
• 2

LVL 39

Expert Comment

ID: 39287818
Can you show expected result also?
0

Author Comment

ID: 39288101
For the section 1 calc number s/b 1
For the section 2 calc number s/b 8
For the section 3 calc number s/b 11.5 (since no value assigned for id#16)

Please no hard coding of values since data will be constantly changed.
0

LVL 39

Accepted Solution

als315 earned 500 total points
ID: 39288214
You can get expected result with this query (save it as Query1):
``````SELECT Table1.[Section No], Sum(Table1.[Field Value]) AS [SumOfField Value]
FROM Table1
WHERE (((Table1.[Form Value])="Yes"))
GROUP BY Table1.[Section No]
HAVING (((Sum(Table1.[Field Value])) Is Not Null));
``````
It is not good idea to store calculated fields in table, but you can do it with this update query:
``````UPDATE Table1 SET Table1.[Calc Number] = DLookUp("[SumOfField Value]","Query1","[Section No] = " & [Table1]![Section No])
``````
0

Author Comment

ID: 39288248
Thanks "als315"

1) This is working on Query level.  However, I need to integrate this to form vba code (probably onCurrent Event).  The calc field should be automatically calculated and value should be displayed in the form during run time. Answer based on Queries is partial.

1.1) Storing calc field in table is a critical part

2) Please provide answer based on provided vba code.  This was a main question.  Reasons for that: the [Form Value])="Yes" condition taken either from the table or from the form (before and after update).   Due to slow network, I cannot be sure that updated value of (txtformvalue) will be immediately stored in the table.

Update statement should work during run-time and display value in the calc field. Don't forget the CalcField is a calculated field in the form.
0

LVL 39

Expert Comment

ID: 39290781
Your form is continuous form and you can't simply calculate sum of fields in different records. You can do it with form's recordset, but it may be too slowly if you will have many records. May be you can explain why you like to store calculated values to table, especially if you have "slow network"? If you can give more information about your data we can try to find more convenient way. May be form with subform, where you can sum records) could be used?
0

Author Comment

ID: 39291085
Look, provided answer based on tables is incomplete. I cannot use it. The purpose of the question is to use form field value inside the loop.

You can use any approaches you like with the attached database. The sum can be determined by the value of fields for current record. I need to store value of calculated field for later access and reporting purposes.

I was thinking of subform as well.  First decided to try calculations within one form.
0

Author Comment

ID: 39296325
Thanks, JARmod101
0

Author Comment

ID: 39300118

1. Based on provided answers on this and other questions I cannot put vba code for continuous form due to issue with continuous form type.

2.On VBA we should Create function getCalc(). Purpose of the function is to retrieve values for the current record of continuous form in order to do calculation
Function getcalc(p_field1, p_field2, pfield3, pfield4 As String)
getcalc = ""
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select [ID], [Field No], [Field Value], [Form Value], [Calc Field] Where [ID]=" & txtFormID & "and [Form Value]" & [txtformvalue]  & "'")
While Not rs.EOF

**** Not sure if anything is needed (since I only want to retrieve info based on current record of the form (ID) and put to recordset for further calculations.

rs.MoveNext
Wend
rs.Close
End Function

3. On Query Level Create another Query
Select Section, getCalc([Field Value), Sum(getCalc(Nz([Field Value]))
Group By Section
Where [Form Value]="yes"

Questions:
1. would you please validate function listed in part #2 (not sure if it is correct),
2. Can we combine function listed in part #2 and query listed in part # 3.

Database is attached

*** Unfortunately, I have to stick with continuous form type.
Database-2-1.accdb
0

LVL 30

Expert Comment

ID: 39302632
maximyshka,

Just give in clear steps what to look for and what output:

1- Open form Form1, you see 2 records of a total 15.
2- Look at field x, the value is y and expected to be z
3- ?
0

Author Comment

ID: 39303202
First, I'm not clear which database you are following.  I don't see any total  of 15 which were listed in the field Calculated Field.

Based on the database provided in comments ID # 39300118 output should be the following: For records which have field value as yes, the calculated value which we have to put in the field "Calc Number" (form field txtcalcno) where field "Answer" ="True" (Checkmarked)

1) For Section 1 the Calc Number should be 3
2) For Section 2 the Calc Number should be 8
3) For Section 3 the Calc Number should be 11.5

4. All these value have to be inputted to the table and displayed in the form where the field "Answer" is checked (please see the table). It is hidden in the form (txtanswer).

5. As I indicated in previous comments  there is an issue if we put vba code in continuous form. Therefore, I was thinking of other possible suggestions.

6. No hard coding of values should be used since data will be changed every month.

7. Please look at query select and other my suggestions.  I have difficulties to retrieve values from database and update table from the form during run time.
0

Author Comment

ID: 39303208
I was thinking of loop only because it is the easiest way to do calculations and put data to the table....
0

LVL 30

Expert Comment

ID: 39304417
" I don't see any total  of 15 "
This is an example, when you open the form and look at record navigation, it says 1 of 15.

So the instructions I asked for will help me understand the problem. Please note that we contribute in many questions so it is not possible to understand the whole project of each asker.

So to concentrate on the problem, I expect to tell me to open a form named form1 and navigate to a certain record. In that record see a certain field and so on.

http:#a39302632
0

Author Comment

ID: 39304633
<<This is an example, when you open the form and look at record navigation, it says 1 of 15.>>

I don't understand the provided example.

I believe we both know the difference between record count and calculating sum over group.

I simply asked you question to open database connection, do calculation based on certain criteria including current form value and write data to the table via form field.

Furthermore, I provided my suggestions and asked you to validate them.

If this question is so complicated please let me know if I can close question without points.
0

Author Closing Comment

ID: 39324143
"Update" statement is not needed since we can write select statement into table via make table query. As I put in my comments, I have to write for reporting purposes. FORM value is automatically updated once we going to different record.
Everything else is calculated via query using grouping, it was clear in my comments.
This was the answer I was looking for.
0

## Featured Post

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the Pâ€¦
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â€¦
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, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which â€¦