I have the data and algorithm, how do I implement it

[complexx]

The database displays/prints service reports which include numerical data and comments about the quality of my customers water. Right now, the comments are input by the secretary based on a predefined list (she just enters the numerical values that correspond to a specific comment and the comment is displayed on the report when it is generated).

The comments can be calculated by running several conditional statements on the data that she enters. I'm trying to automate the calculation of these comments with an algorithm that I wrote.

After doing some searching for information on vba functions, I assume thats what I need to create to accomplish this.

I am unsure as to where to write this vba function that contains the algorithm I want to implement and how to get the data from the tables to be available to this function (the data is contained in several different tables).

The end result I'm looking for is for the function to write the comments to the report based on calculation of the data rather than having the secretary input the comments manually (and storing them in a table when the data from the service report is stored).

 

[Pat Hartman]

There are a number of ways to perform calculations. You can put them into the query itself or you can create a function that returns a value to the query. Another way is to write a VBA procedure that opens a recordset and loops through it. I would need to know more about what the algorithm calculates and what you need to do with the result to offer a definitive approach.

 

[complexx]

Here's some more detail about the algorithm.

Table 1 contains and various MIN and a MAX values.

Table 2 contains various DATA values.

Table 3 contains a list of comments.

The algorithm basically manipulates DATA and runs some comparisons on it with MIN and MAX and would return a numerical value based on the results of those comparisons. The numerical value corresponds to one of the comments in Table 3.

I would like the function to be able to output one or more numerical values so that I could display the comments associated with the numerical values in a report. (I assume to a temporary table to capture the output, unless I could perform a "write" to the report mid algorithm)

If I have been unclear anywhere, please let me know.

 

[Pat Hartman]

If you can perform the calculation in the query, you can then create a second query which joins the first query to the comments table. You would then use this second query as the RecordSource for the report. Otherwise, you can create a temporary table and then join that table to table 3.