average for newbie

[robmc001]

Hi, am trying to calculate an average and cannot get it to work after spending a couple of hours. Have searched these message boards without finding the answer.

Have set up basic database with three tables, linked by serial numbers.
Main_Details table
Expt_Details table
Expt_Data table

Each main table has several experiments and each experiment has several data series.

What I want to do is average the experimental data for each experiment on the form. Know alpha5 is set up to do this on reports easily, but need it on the form. Closest have got is an average of ALL the experimental data. But cannot get it to work so far using dbavg or tableavg or average. Think need to use GRP but keep getting invalid expression.

Could get a simple numerical expression by adding all the data and then dividing by the number of data points, but the number will change and also want to use the standard deviation function later. Thanks, Rob

[Tom Cone Jr]

Re: average for newbie

TABLEAVG() takes a filter expression. If you're getting the average over the entire table perhaps your filter expression is wrong. Suggest you study the examples in the discussioin of the related function TABLECOUNT(), to see if you can figure it out. Posting the complete TABLEAVG() call here, with an explanation of the arguments you use, is another possibiliity, too.

ps - Welcome to the message board!

[robmc001]

Re: average for newbie

Thanks very much Tom, I can get the dbavg to work, sort of, with a filter. Did this by creating a new summary table to hold the mean and standard deviation fields. But as the required filter will change as the user enters new data, this is limited. Everytime new data is entered it also gives a new average value in the next record of the average table, which I suppose is not so bad so long as you remember to use the last average value.

Here is what I am trying to do

expt_details.dbf contains expt_serial
progr_data.dbf contains Expt_Serial_Progr and Progr
expt_summary.dbf contains Expt_serial_summary and Progr_mean (added this table since last post)

Both progr_data.dbf and expt_summary.dbf are child tables of expt_details.dbf, linked one to many, cascade changes, through the expt_serial field to the respective expt_serial_Progr and expt_serial_summary fields.

(The expt_details.dbf is child of the main_table but linked through a different field,, and so appears as an embedded browse as well, that is probably not relevant though)

Used the expression builder to make this for Progr_mean
DBAVG("progr_data.dbf","Expt_Serial_Progr", "Expt_Serial_Progr", "Progr")

this is the function help for your reference
DBAVG( Lookup_Table as C, Index_Tagname as C, Key_Value as A, Lookup_Expression as C )

What I hope to do is when you click on the relevant serial number field, it will show the Prog data in one embedded browse (it does) and the mean in another (it does not). The expression builder gives zero value which means the data cannot be found.

This is based on this example in the help files
Assume that the CUST_ID field in the current record contains "C102". The following expression returns the average of the values in the AMOUNT field for all the records in the ORDER table with a customer ID number of "C102":
? dbavg("ORDER.DBF", "CUSTOMER_ID", CUSTOMER_ID, "AMOUNT")
= 549.18

Have also looked this up in the Liberty Manuals Functions book but the example it gives is a filter as well.
-Rob

[Stan Mathews]

Re: average for newbie

Your construction uses one more set of quotes than the example.

DBAVG("progr_data.dbf","Expt_Serial_Progr", [COLOR="Red"]"Expt_Serial_Progr", [/COLOR]"Progr")

would be

DBAVG("progr_data.dbf","Expt_Serial_Progr", [COLOR="RoyalBlue"]Expt_Serial_Progr[/COLOR], "Progr")

to reference the current value in Expt_Serial_Progr instead of the text "Expt_Serial_Progr".

[robmc001]

Re: average for newbie

Thanks Stan, should have mentioned if take the quotation marks out from the third parameter then the Exp_Serial_Prog goes black and when you click OK you get
Expression does not evaluate to a Numeric value.

Thought this might be because the serial number fields were character and not numeric, so changed those to numeric and still the same.
This means I must have the syntax wrong, have tried many variants but cannot seem to get it right.-Rob

[robmc001]

Re: average for newbie

think I got it to work, changed expression to

DBAVG("progr_data","Expt_Serial_Progr", EXPT_SERIAL_SUMMARY, "Progr")

so the key, or 3rd, field is in the table you are working in, not the table you are calculating the average for. At first this did not work, then I played around including deleting the link and then re-establishing it. Then opened the summary table in a browse and all the values were there!

It still won't work properly as an embedded browse (the correct values show up only after being opened in a stand alone table), only in a separate table and you have to type in the serial number first but this should be fixable, hopefully. It does update "live" as the values change. Thanks for all the advice it helped a lot. -Rob

[Stan Mathews]

Re: average for newbie

Glad you got it working.

so the key, or 3rd, field is in the table you are working in, not the table you are calculating the average for.

Yes, the third parameter is the value you are supplying to the function. It doesn't have to be a field, it just has to be an expression that resolves to a value that will found in the table named in the function.

My most common use is likely a form_field_object_name.value for a key. Supplying the function with a value currently visible on a form.