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

Posted on 2013-06-29
Medium Priority
Last Modified: 2013-07-13
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


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

IIF me.txtanswer=True, me.txtCalcNo=z, Null
End Do

End Do

Open in new window

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

Expert Comment

ID: 39287818
Can you show expected result also?

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.
LVL 41

Accepted Solution

als315 earned 1000 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));

Open in new window

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])
WHERE (((Table1.Answer)=True));

Open in new window

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


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.
LVL 41

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?

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.

Author Comment

ID: 39296325
Thanks, JARmod101

Author Comment

ID: 39300118
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("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.

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"

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.
LVL 31

Expert Comment

ID: 39302632

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- ?

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.

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....
LVL 31

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.


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.

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.

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

592 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