should I use DSum in an update query for this problem? If so, how?

[davefwelch]

I have a table called Survey with many, many fields that each represent a question on a survey to which businesses have responded. I need to crunch the data from the surveys to produce some tables based on different characteristics of the businesses.

The first two quetions on the surveys simply ask the business to enter the number of full-time and part-time employees they have. This is the number I want to DSum into a field in a new table, based on the response in any other given Yes/No question.

For example, one of the questions on the survey is "Do you offer paid holidays?" I need to come up with the percentage of full-time employees (not businesses) that were reported as being offered paid hoidays. So, the numerator would by the DSum of FT employees at businesses that answered yes to that question, which would be a simple test on one of the fields in the Survey table. The denominator would be the DSum of FT employees at businesses that HAVE FT employees, or where the FT field is >0.

I have tried using the function:
DSum("[Q-1]", "Survey", "[Q-3] = '-1'")
in an update query to update a field in a table called Results. The query runs without error, but the field is not updated (remains 0). Any idea why?

 

[davefwelch]

I know that the best way to use calculated values is not to store them, but at this point, the data collection and entry process is over, so the calculated values will not be changing. The reason I was storing them is so I have the values (there are over 100 calculations) all in one place to use for my report. I know I could do this with hundreds of queries, but I'm trying to develop a way to somewhat automate the process, or at least avoid having to run hundreds of queries, each time writing down the result for later use.

Is it possible to do my calculations in VB and have them displayed on a report? Could you give me an example snipet of code to do that, and an idea of how to display it on a report? I have used VB previously, so I think I could get the calculations done in VB. I'm just not sure how to get the results displayed.