maximyshka
asked on
How do i do calculation by using multiple fields and loop. Please see attached database
Would you please help me to see the attached database
Data presented in the following format
ID	Section No	Field No	Field Value	Form Value	Calc Number	Answer
1	 1	 1	 0.5	 No		 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
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
Data presented in the following format
ID	Section No	Field No	Field Value	Form Value	Calc Number	Answer
1	 1	 1	 0.5	 No		 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++
IIF me.txtanswer=True, me.txtCalcNo=z, Null
End Do
Loop
x++
End Do
LoopWould 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
als315
Can you show expected result also?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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?
ASKER
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.
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.
ASKER
Thanks, JARmod101
ASKER
Thanks for your answers. Here is my suggestions
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("s
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
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("s
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
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- ?
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- ?
ASKER
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.
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.
ASKER
I was thinking of loop only because it is the easiest way to do calculations and put data to the table....
" 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
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
ASKER
<<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.
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.
ASKER
"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.
Everything else is calculated via query using grouping, it was clear in my comments.
This was the answer I was looking for.