Running Average excluding 0's

[Nick X]

Hi all,
I have a subform that has several calculated values, the last of which is
the difference.

[txtOldNum]=cboBMNUM.column(3)
[NewNum1] is bound
[NewNum2] is bound
[txtNumAvg]=IIf([NewNum2]=0,[NewNum1],([NewNum1]+[NewNum2])/2)
[txtNumDiff]=IIf([txtOldNum]=0,"N/A",[txtOldNum]-[txtNumAvg])

I would like to get the running average of [txtNumDiff]
[txtRunningAvg]=Avg([txtNumDiff]) where [txtNumDiff] rows that equal 0 are
excluded from the calculation.

Thanks,
NickX

 

[Jeff Boyce]

Consider using a query, doing the comparison between 1 & 2 there, and
excluding the record if they are equal (i.e., difference = 0).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Nick X]

Jeff,
Thanks for your response.

[txtNumAvg]=IIf([NewNum2]=0,[NewNum1],([NewNum1]+[NewNum2])/2)

I moved this to the query as an expression.

what I want is the difference between [txtOldNum] and [txtAvgNum]
([txtNumDiff]) to be averaged across the whole subform dataset (ignoring
zeros) and resulting in [txtRunningAvg]


cboBMNUM|txtbox|txtOldNum|NewNum1|NewNum2|txtNumAvg|txtNumDiff
cbox column(1)|(2) |(3)
1328 |` |.000 |735.91 |0.00 |735.91 |N/A
219 |` |745.210 |744.18 |0.00 |744.18 |1.0270
1329 |` |.000 |738.71 |0.00 |738.71 |N/A
240 |` |753.790 |753.01 |0.00 |753.01 |0.7760
287 |` |830.180 |829.41 |0.00 |829.41 |0.7690
980 |` |.000 |758.46 |0.00 |758.46 |N/A
679 |` |749.520 |748.67 |0.00 |748.67 |0.8460

the last column [txtNumDiff] averages out to be 0.8545 or 3.418 divided by 4,
how would I display this in the subform footer?

Thanks,
NickX

PS: (cbox column(1)|(2)|(3) is an illustration of [cboBMNUM] to show where
txtOldNum is drawn from)

Consider using a query, doing the comparison between 1 & 2 there, and
excluding the record if they are equal (i.e., difference = 0).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,
I have a subform that has several calculated values, the last of which is
the difference.

[txtOldNum]=cboBMNUM.column(3)
[NewNum1] is bound
[NewNum2] is bound
[txtNumAvg]=IIf([NewNum2]=0,[NewNum1],([NewNum1]+[NewNum2])/2)
[txtNumDiff]=IIf([txtOldNum]=0,"N/A",[txtOldNum]-[txtNumAvg])

I would like to get the running average of [txtNumDiff]
[txtRunningAvg]=Avg([txtNumDiff]) where [txtNumDiff] rows that equal 0 are
excluded from the calculation.

Thanks,
NickX

 

[Jeff Boyce]

Nick

A query works with data from a table (or another query). It doesn't work
with data from a form (mainly because the data isn't really "in" the form,
it's in the underlying table(s)).

Try creating a query against the original data tables instead of the form.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
Thanks for your response.

[txtNumAvg]=IIf([NewNum2]=0,[NewNum1],([NewNum1]+[NewNum2])/2)

I moved this to the query as an expression.

what I want is the difference between [txtOldNum] and [txtAvgNum]
([txtNumDiff]) to be averaged across the whole subform dataset (ignoring
zeros) and resulting in [txtRunningAvg]


cboBMNUM|txtbox|txtOldNum|NewNum1|NewNum2|txtNumAvg|txtNumDiff
cbox column(1)|(2) |(3)
1328 |` |.000 |735.91 |0.00 |735.91 |N/A
219 |` |745.210 |744.18 |0.00 |744.18 |1.0270
1329 |` |.000 |738.71 |0.00 |738.71 |N/A
240 |` |753.790 |753.01 |0.00 |753.01 |0.7760
287 |` |830.180 |829.41 |0.00 |829.41 |0.7690
980 |` |.000 |758.46 |0.00 |758.46 |N/A
679 |` |749.520 |748.67 |0.00 |748.67 |0.8460

the last column [txtNumDiff] averages out to be 0.8545 or 3.418 divided by
4,
how would I display this in the subform footer?

Thanks,
NickX

PS: (cbox column(1)|(2)|(3) is an illustration of [cboBMNUM] to show where
txtOldNum is drawn from)

Consider using a query, doing the comparison between 1 & 2 there, and
excluding the record if they are equal (i.e., difference = 0).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,
I have a subform that has several calculated values, the last of which
is
the difference.

[txtOldNum]=cboBMNUM.column(3)
[NewNum1] is bound
[NewNum2] is bound
[txtNumAvg]=IIf([NewNum2]=0,[NewNum1],([NewNum1]+[NewNum2])/2)
[txtNumDiff]=IIf([txtOldNum]=0,"N/A",[txtOldNum]-[txtNumAvg])

I would like to get the running average of [txtNumDiff]
[txtRunningAvg]=Avg([txtNumDiff]) where [txtNumDiff] rows that equal 0
are
excluded from the calculation.

Thanks,
NickX

 

[Nick X]

Jeff,
The problem I am having with creating a query is that txtOldNum is a product
of making a selection from the combo box [cboBMNUM]:
SELECT PtblBenchmark.OBJECTID, PtblBenchmark.BMNum, PtblBenchmark.Name,
PtblBenchmark.Elevation, PtblBenchmark.st_name_1, PtblBenchmark.st_name_2
FROM PtblBenchmark;

[txtOldNum]=cboBMNUM.column(3) {PtblBenchmark.Elevation}

From my experience .column() does not work in a query. Is there a way to
fake this? Because, technically, [txtOldNum] does not exist until a
selection is made from the combo box in the form (as a calculated value).
All the other values are easily done with an expression in the query. I
understand that Access does not like to calculate values in a form based on
calculated values from the form. I guess I could add a field to store the
value of txtOldNum, I was just trying to follow the rules of normalization by
not storing values that didn't need stored.

Which brings us back to the original issue:
As I enter records into the subform in question, how do I get a running
average over [txtNumDiff] (in the subform footer) excluding the rows where
[txtNumDiff] equals zero.

Thanks,
NickX

Nick

A query works with data from a table (or another query). It doesn't work
with data from a form (mainly because the data isn't really "in" the form,
it's in the underlying table(s)).

Try creating a query against the original data tables instead of the form.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
Thanks for your response.

[txtNumAvg]=IIf([NewNum2]=0,[NewNum1],([NewNum1]+[NewNum2])/2)

I moved this to the query as an expression.

what I want is the difference between [txtOldNum] and [txtAvgNum]
([txtNumDiff]) to be averaged across the whole subform dataset (ignoring
zeros) and resulting in [txtRunningAvg]


cboBMNUM|txtbox|txtOldNum|NewNum1|NewNum2|txtNumAvg|txtNumDiff
cbox column(1)|(2) |(3)
1328 |` |.000 |735.91 |0.00 |735.91 |N/A
219 |` |745.210 |744.18 |0.00 |744.18 |1.0270
1329 |` |.000 |738.71 |0.00 |738.71 |N/A
240 |` |753.790 |753.01 |0.00 |753.01 |0.7760
287 |` |830.180 |829.41 |0.00 |829.41 |0.7690
980 |` |.000 |758.46 |0.00 |758.46 |N/A
679 |` |749.520 |748.67 |0.00 |748.67 |0.8460

the last column [txtNumDiff] averages out to be 0.8545 or 3.418 divided by
4,
how would I display this in the subform footer?

Thanks,
NickX

PS: (cbox column(1)|(2)|(3) is an illustration of [cboBMNUM] to show where
txtOldNum is drawn from)

Consider using a query, doing the comparison between 1 & 2 there, and
excluding the record if they are equal (i.e., difference = 0).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,
I have a subform that has several calculated values, the last of which
is
the difference.

[txtOldNum]=cboBMNUM.column(3)
[NewNum1] is bound
[NewNum2] is bound
[txtNumAvg]=IIf([NewNum2]=0,[NewNum1],([NewNum1]+[NewNum2])/2)
[txtNumDiff]=IIf([txtOldNum]=0,"N/A",[txtOldNum]-[txtNumAvg])

I would like to get the running average of [txtNumDiff]
[txtRunningAvg]=Avg([txtNumDiff]) where [txtNumDiff] rows that equal 0
are
excluded from the calculation.

Thanks,
NickX

 

[Jeff Boyce]

Instead of relying on the form's combobox's columns, can you join the
table(s) involved in the combobox to the table(s) with the information you
want? That way, you could use the ID field from the form's combobox as a
selection criterion to get the data you want from its table into your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
The problem I am having with creating a query is that txtOldNum is a
product
of making a selection from the combo box [cboBMNUM]:
SELECT PtblBenchmark.OBJECTID, PtblBenchmark.BMNum, PtblBenchmark.Name,
PtblBenchmark.Elevation, PtblBenchmark.st_name_1, PtblBenchmark.st_name_2
FROM PtblBenchmark;

[txtOldNum]=cboBMNUM.column(3) {PtblBenchmark.Elevation}

From my experience .column() does not work in a query. Is there a way to
fake this? Because, technically, [txtOldNum] does not exist until a
selection is made from the combo box in the form (as a calculated value).
All the other values are easily done with an expression in the query. I
understand that Access does not like to calculate values in a form based
on
calculated values from the form. I guess I could add a field to store the
value of txtOldNum, I was just trying to follow the rules of normalization
by
not storing values that didn't need stored.

Which brings us back to the original issue:
As I enter records into the subform in question, how do I get a running
average over [txtNumDiff] (in the subform footer) excluding the rows where
[txtNumDiff] equals zero.

Thanks,
NickX

Nick

A query works with data from a table (or another query). It doesn't work
with data from a form (mainly because the data isn't really "in" the
form,
it's in the underlying table(s)).

Try creating a query against the original data tables instead of the
form.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
Thanks for your response.

[txtNumAvg]=IIf([NewNum2]=0,[NewNum1],([NewNum1]+[NewNum2])/2)
I moved this to the query as an expression.

what I want is the difference between [txtOldNum] and [txtAvgNum]
([txtNumDiff]) to be averaged across the whole subform dataset
(ignoring
zeros) and resulting in [txtRunningAvg]


cboBMNUM|txtbox|txtOldNum|NewNum1|NewNum2|txtNumAvg|txtNumDiff
cbox column(1)|(2) |(3)
1328 |` |.000 |735.91 |0.00 |735.91 |N/A
219 |` |745.210 |744.18 |0.00 |744.18 |1.0270
1329 |` |.000 |738.71 |0.00 |738.71 |N/A
240 |` |753.790 |753.01 |0.00 |753.01 |0.7760
287 |` |830.180 |829.41 |0.00 |829.41 |0.7690
980 |` |.000 |758.46 |0.00 |758.46 |N/A
679 |` |749.520 |748.67 |0.00 |748.67 |0.8460

the last column [txtNumDiff] averages out to be 0.8545 or 3.418 divided
by
4,
how would I display this in the subform footer?

Thanks,
NickX

PS: (cbox column(1)|(2)|(3) is an illustration of [cboBMNUM] to show
where
txtOldNum is drawn from)

:

Consider using a query, doing the comparison between 1 & 2 there, and
excluding the record if they are equal (i.e., difference = 0).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,
I have a subform that has several calculated values, the last of
which
is
the difference.

[txtOldNum]=cboBMNUM.column(3)
[NewNum1] is bound
[NewNum2] is bound
[txtNumAvg]=IIf([NewNum2]=0,[NewNum1],([NewNum1]+[NewNum2])/2)
[txtNumDiff]=IIf([txtOldNum]=0,"N/A",[txtOldNum]-[txtNumAvg])

I would like to get the running average of [txtNumDiff]
[txtRunningAvg]=Avg([txtNumDiff]) where [txtNumDiff] rows that equal
0
are
excluded from the calculation.

Thanks,
NickX

 

[Nick X]

Jeff,
I was able to get this to work in a query. Actually, I don't want a running
average, I want an average over the whole subform as I enter new records:

txtAvgAll=Avg([exElDiff]) produces the results I desire, as long as there
are only numbers. When you throw in a field with the "N/A" it causes an
error. I need to exclude the "N/A" values while still displaying them...

It feels like I need a where clause but I am sure that won't work in a text
box expression. Is there a way I can do a subquery? Or, am I totally
missing the point and sitting here with the solution under my nose.

Thank you for your patience,
NickX

Sample Data:
OldNum |NewNum1|NewNum2|NumAvg (NewNum)|NumDiff (NewNum-OldNum)
..000 |758.455 |0 |758.455 |N/A
749.520 |748.674 |0 |748.674 |0.846
737.470 |736.756 |0 |736.756 |0.714
740.430 |739.597 |0 |739.597 |0.833
750.750 |749.919 |749.928 |749.9235 |0.8265
..000 |758.422 |0 |758.422 |N/A
..000 |757.013 |0 |757.013 |N/A

Instead of relying on the form's combobox's columns, can you join the
table(s) involved in the combobox to the table(s) with the information you
want? That way, you could use the ID field from the form's combobox as a
selection criterion to get the data you want from its table into your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
The problem I am having with creating a query is that txtOldNum is a
product
of making a selection from the combo box [cboBMNUM]:
SELECT PtblBenchmark.OBJECTID, PtblBenchmark.BMNum, PtblBenchmark.Name,
PtblBenchmark.Elevation, PtblBenchmark.st_name_1, PtblBenchmark.st_name_2
FROM PtblBenchmark;

[txtOldNum]=cboBMNUM.column(3) {PtblBenchmark.Elevation}

From my experience .column() does not work in a query. Is there a way to
fake this? Because, technically, [txtOldNum] does not exist until a
selection is made from the combo box in the form (as a calculated value).
All the other values are easily done with an expression in the query. I
understand that Access does not like to calculate values in a form based
on
calculated values from the form. I guess I could add a field to store the
value of txtOldNum, I was just trying to follow the rules of normalization
by
not storing values that didn't need stored.

Which brings us back to the original issue:
As I enter records into the subform in question, how do I get a running
average over [txtNumDiff] (in the subform footer) excluding the rows where
[txtNumDiff] equals zero.

Thanks,
NickX

Nick

A query works with data from a table (or another query). It doesn't work
with data from a form (mainly because the data isn't really "in" the
form,
it's in the underlying table(s)).

Try creating a query against the original data tables instead of the
form.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
Thanks for your response.

[txtNumAvg]=IIf([NewNum2]=0,[NewNum1],([NewNum1]+[NewNum2])/2)
I moved this to the query as an expression.

what I want is the difference between [txtOldNum] and [txtAvgNum]
([txtNumDiff]) to be averaged across the whole subform dataset
(ignoring
zeros) and resulting in [txtRunningAvg]


cboBMNUM|txtbox|txtOldNum|NewNum1|NewNum2|txtNumAvg|txtNumDiff
cbox column(1)|(2) |(3)
1328 |` |.000 |735.91 |0.00 |735.91 |N/A
219 |` |745.210 |744.18 |0.00 |744.18 |1.0270
1329 |` |.000 |738.71 |0.00 |738.71 |N/A
240 |` |753.790 |753.01 |0.00 |753.01 |0.7760
287 |` |830.180 |829.41 |0.00 |829.41 |0.7690
980 |` |.000 |758.46 |0.00 |758.46 |N/A
679 |` |749.520 |748.67 |0.00 |748.67 |0.8460

the last column [txtNumDiff] averages out to be 0.8545 or 3.418 divided
by
4,
how would I display this in the subform footer?

Thanks,
NickX

PS: (cbox column(1)|(2)|(3) is an illustration of [cboBMNUM] to show
where
txtOldNum is drawn from)

:

Consider using a query, doing the comparison between 1 & 2 there, and
excluding the record if they are equal (i.e., difference = 0).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,
I have a subform that has several calculated values, the last of
which
is
the difference.

[txtOldNum]=cboBMNUM.column(3)
[NewNum1] is bound
[NewNum2] is bound
[txtNumAvg]=IIf([NewNum2]=0,[NewNum1],([NewNum1]+[NewNum2])/2)
[txtNumDiff]=IIf([txtOldNum]=0,"N/A",[txtOldNum]-[txtNumAvg])

I would like to get the running average of [txtNumDiff]
[txtRunningAvg]=Avg([txtNumDiff]) where [txtNumDiff] rows that equal
0
are
excluded from the calculation.

Thanks,
NickX

 

[Jeff Boyce]

Nick

I'd head for the query, first. Can you build a query that excludes the
"N/A"-valued records? Can you join your already-working query to THAT one
instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
I was able to get this to work in a query. Actually, I don't want a
running
average, I want an average over the whole subform as I enter new records:

txtAvgAll=Avg([exElDiff]) produces the results I desire, as long as there
are only numbers. When you throw in a field with the "N/A" it causes an
error. I need to exclude the "N/A" values while still displaying them...

It feels like I need a where clause but I am sure that won't work in a
text
box expression. Is there a way I can do a subquery? Or, am I totally
missing the point and sitting here with the solution under my nose.

Thank you for your patience,
NickX

Sample Data:
OldNum |NewNum1|NewNum2|NumAvg (NewNum)|NumDiff (NewNum-OldNum)
.000 |758.455 |0 |758.455 |N/A
749.520 |748.674 |0 |748.674 |0.846
737.470 |736.756 |0 |736.756 |0.714
740.430 |739.597 |0 |739.597 |0.833
750.750 |749.919 |749.928 |749.9235 |0.8265
.000 |758.422 |0 |758.422 |N/A
.000 |757.013 |0 |757.013 |N/A

Instead of relying on the form's combobox's columns, can you join the
table(s) involved in the combobox to the table(s) with the information
you
want? That way, you could use the ID field from the form's combobox as a
selection criterion to get the data you want from its table into your
query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
The problem I am having with creating a query is that txtOldNum is a
product
of making a selection from the combo box [cboBMNUM]:
SELECT PtblBenchmark.OBJECTID, PtblBenchmark.BMNum, PtblBenchmark.Name,
PtblBenchmark.Elevation, PtblBenchmark.st_name_1,
PtblBenchmark.st_name_2
FROM PtblBenchmark;

[txtOldNum]=cboBMNUM.column(3) {PtblBenchmark.Elevation}

From my experience .column() does not work in a query. Is there a way
to
fake this? Because, technically, [txtOldNum] does not exist until a
selection is made from the combo box in the form (as a calculated
value).
All the other values are easily done with an expression in the query.
I
understand that Access does not like to calculate values in a form
based
on
calculated values from the form. I guess I could add a field to store
the
value of txtOldNum, I was just trying to follow the rules of
normalization
by
not storing values that didn't need stored.

Which brings us back to the original issue:
As I enter records into the subform in question, how do I get a running
average over [txtNumDiff] (in the subform footer) excluding the rows
where
[txtNumDiff] equals zero.

Thanks,
NickX

:

Nick

A query works with data from a table (or another query). It doesn't
work
with data from a form (mainly because the data isn't really "in" the
form,
it's in the underlying table(s)).

Try creating a query against the original data tables instead of the
form.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
Thanks for your response.

[txtNumAvg]=IIf([NewNum2]=0,[NewNum1],([NewNum1]+[NewNum2])/2)
I moved this to the query as an expression.

what I want is the difference between [txtOldNum] and [txtAvgNum]
([txtNumDiff]) to be averaged across the whole subform dataset
(ignoring
zeros) and resulting in [txtRunningAvg]


cboBMNUM|txtbox|txtOldNum|NewNum1|NewNum2|txtNumAvg|txtNumDiff
cbox column(1)|(2) |(3)
1328 |` |.000 |735.91 |0.00 |735.91 |N/A
219 |` |745.210 |744.18 |0.00 |744.18 |1.0270
1329 |` |.000 |738.71 |0.00 |738.71 |N/A
240 |` |753.790 |753.01 |0.00 |753.01 |0.7760
287 |` |830.180 |829.41 |0.00 |829.41 |0.7690
980 |` |.000 |758.46 |0.00 |758.46 |N/A
679 |` |749.520 |748.67 |0.00 |748.67 |0.8460

the last column [txtNumDiff] averages out to be 0.8545 or 3.418
divided
by
4,
how would I display this in the subform footer?

Thanks,
NickX

PS: (cbox column(1)|(2)|(3) is an illustration of [cboBMNUM] to show
where
txtOldNum is drawn from)

:

Consider using a query, doing the comparison between 1 & 2 there,
and
excluding the record if they are equal (i.e., difference = 0).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,
I have a subform that has several calculated values, the last of
which
is
the difference.

[txtOldNum]=cboBMNUM.column(3)
[NewNum1] is bound
[NewNum2] is bound
[txtNumAvg]=IIf([NewNum2]=0,[NewNum1],([NewNum1]+[NewNum2])/2)
[txtNumDiff]=IIf([txtOldNum]=0,"N/A",[txtOldNum]-[txtNumAvg])

I would like to get the running average of [txtNumDiff]
[txtRunningAvg]=Avg([txtNumDiff]) where [txtNumDiff] rows that
equal
0
are
excluded from the calculation.

Thanks,
NickX

 

[Nick X]

Jeff,
I believe I have come up with the answer
[txtAvgAll]=Sum(IIf([exNumDiff]>0.1,[exNumDiff],Null))/Sum(IIf([exNumDiff]>0.1,1,0))

I changed the value of "N/A" to Null, I take into account the Null and the
fact that none of my residuals should be less than 0.1, then I count the
number of records using that same criteria.

Everything else I stuck into a query:
SELECT PtblBenchmark.Agency, tblBM_Loop_Avg.BM_PKID, tblBM_Loop_Avg.LOOP_ID,
PtblBenchmark.OBJECTID, tblBM_Loop_Avg.BM_OID, PtblBenchmark.Elevation,
tblBM_Loop_Avg.Elevation1, tblBM_Loop_Avg.Elevation2,
tblBM_Loop_Avg.Comments,
IIf([Elevation2]=0,[Elevation1],([Elevation1]+[Elevation2])/2) AS exAvgEl,
Abs(IIf([Elevation]>".000",[Elevation]-[exAvgEl],Null)) AS exElDiff
FROM tblBM_Loop_Avg INNER JOIN PtblBenchmark ON tblBM_Loop_Avg.BM_OID =
PtblBenchmark.OBJECTID;

I think this will work, thankyou for all the encouragement.
NickX