help me get the average please! :(

[joebox8]

Hi... I'm new to access! Hopefully someone can help.

My DB has one table, Its not very big however I have roughly 50 fields in it.
32 of the fields are named x1, x2, x3, x4, ....x32(corresponding to 32 text boxes on my form)
I have Tagged each text box with "T". How would i work it so I can only get the average(or std_dev) of only the tagged text boxes that have a numeric value greater than 0.00???

I realize there are a few ways of doing this however im struggling with it. Here's what i think...


Loop through each text box, If greater than 0.00 then increment a count(say C) then at the end divide my total by (32 - C)?

Or should I do this from the Table instead of the form? through a query? or how could i phrase DAvg so the criteria would pick out the nonNull Tagged textbox values??
Please help!!!

[jzwp11]

x1, x2, x3, x4, ....x32

Having fields x1, x2... is an example of repeating groups which indicates that your table structure is not normalized. I recommend that you check out this site for some background on normalization.

Once you have normalized your structure, obtaining the Average or standard deviation is handled easily with a query.

With what type of data are you working?
Can you please provide more details of what your application is doing and what business process you are trying to model?

[joebox8]

hey... x1 x2...x32 are all required test pieces(for the one record) whose values need to be recorded along with all the relevant calculations for future reference. I realise this structure is not ideal however at the moment it seems the easiest.. any suggestions?

[jzwp11]

Having a normalized structure would make this a lot easier. Doing it with your current structure will be difficult (if not impossible).

I have Tagged each text box with "T". How would i work it so I can only get the average(or std_dev) of only the tagged text boxes that have a numeric value greater than 0.00???

Tagging something in a form but not in the underlying table will not help you in getting you to a solution.

If you want to include records where the value is greater than zero, you would need to include that in the criteria section of the query. So you could have:

SELECT fieldnames
FROM tableName
WHERE x1>0.00 AND X2>0.00 etc.

BUT, if any field is not greater than zero, then the RECORD (and all of the data in the respective fields) will not be included in the query results. Hence the need for normalization.