|
|
|
date: Sun, 6 Jul 2008 10:19:01 -0700,
group: microsoft.public.access
back
Re: calculating averages
Since you can do this in a form or query (or even a report) there is no need
to store the result. In a query use an alias column:
Grade: AVG([Grade1]+[Grade2])
in a form or report, set the controlsource of the Grade textbox to:
=AVG([Grade1]+[Grade2])
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
"Kit" wrote in message
news:72D70EBB-EBC4-4B89-859F-9081AAD3D52F@microsoft.com...
> Hi,
>
> I received a reply that my question on calculating averages had been
> answered, when I click on the link for the answer, it was blank. Please
> respond by to my question.
>
> Can this calculation of averages be done from a form?
>
> Form Name is Benton
> I have three fields
> field one is "grade"
> field two is "grade1"
> field three is "grade2"
>
> I want the average of "grade1" and "grade2" to show in the "grade" field.
>
> Can this be done with a form only, if not is there a way I can create a
> macro, each time I try to create a macro, I only get the pre-defined
> actions.
>
> Thanks for any help you can give me.
date: Sun, 6 Jul 2008 13:31:02 -0400
author: Arvin Meyer [MVP]
Re: calculating averages
Hi Arvin, and thanks so very much -- I owe you one!!!
I have my controlsource set with the parameters for the avg, but it's giving
me both field values: i.e. say my "grade1" is 50 and my "grade2" is 60, the
value in "grade" comes out as 2530. How can I merge grade1 and grade2. I
have them as separate fields, but if you can suggest a combined field that
will give me one result, I sure would appreciate it.
"Arvin Meyer [MVP]" wrote:
> Since you can do this in a form or query (or even a report) there is no need
> to store the result. In a query use an alias column:
>
> Grade: AVG([Grade1]+[Grade2])
>
> in a form or report, set the controlsource of the Grade textbox to:
>
> =AVG([Grade1]+[Grade2])
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
>
> "Kit" wrote in message
> news:72D70EBB-EBC4-4B89-859F-9081AAD3D52F@microsoft.com...
> > Hi,
> >
> > I received a reply that my question on calculating averages had been
> > answered, when I click on the link for the answer, it was blank. Please
> > respond by to my question.
> >
> > Can this calculation of averages be done from a form?
> >
> > Form Name is Benton
> > I have three fields
> > field one is "grade"
> > field two is "grade1"
> > field three is "grade2"
> >
> > I want the average of "grade1" and "grade2" to show in the "grade" field.
> >
> > Can this be done with a form only, if not is there a way I can create a
> > macro, each time I try to create a macro, I only get the pre-defined
> > actions.
> >
> > Thanks for any help you can give me.
>
>
>
date: Sun, 6 Jul 2008 11:25:00 -0700
author: Kit
Re: calculating averages
I'm wondering whether what you actually want is the average of the two values
in the controls on the form rather than the average of a set of values in the
form's underlying recordset. If so don't use the Avg function but a
ControlSource of:
=(Val([Grade1])+Val([Grade2]))/2
However, if Grade1 and Grade2 are in fact columns in a table then Mark (aka
Steve) is, despite Arno's strictures, actually right about the flawed design
of the table. It should be decomposed as he suggests, but as Grade will be a
set of unique values in the tblGrade table you can just have the one column
Grade as the primary key of that table, and a corresponding Grade foreign key
in the tblItemGrade table, with referential integrity and cascade updates
enforced in the relationship between the two tables. The ControlSource for
an unbound text box to show the average grade per 'item' in a form based on
the tblItem table would then be:
=DAvg("GradeValue", "tblItemGrade", "ItemID = " & [ItemID)
though a more likely scenario would be to have a form based on tblItem with
a continuous subform based on tblItemGrade embedded within it, linked to the
parent form on ItemID, in which case you'd have a text box in the subform's
footer with a ControlSource property of:
=Avg([GradeValue])
We don't really have enough information from you about what your database
represents in terms of the real world to say which way you should be going.
Ken Sheridan
Stafford, England
"Kit" wrote:
> Hi Arvin, and thanks so very much -- I owe you one!!!
>
> I have my controlsource set with the parameters for the avg, but it's giving
> me both field values: i.e. say my "grade1" is 50 and my "grade2" is 60, the
> value in "grade" comes out as 2530. How can I merge grade1 and grade2. I
> have them as separate fields, but if you can suggest a combined field that
> will give me one result, I sure would appreciate it.
>
> "Arvin Meyer [MVP]" wrote:
>
> > Since you can do this in a form or query (or even a report) there is no need
> > to store the result. In a query use an alias column:
> >
> > Grade: AVG([Grade1]+[Grade2])
> >
> > in a form or report, set the controlsource of the Grade textbox to:
> >
> > =AVG([Grade1]+[Grade2])
> > --
> > Arvin Meyer, MCP, MVP
> > http://www.datastrat.com
> > http://www.mvps.org/access
> > http://www.accessmvp.com
> >
> >
> > "Kit" wrote in message
> > news:72D70EBB-EBC4-4B89-859F-9081AAD3D52F@microsoft.com...
> > > Hi,
> > >
> > > I received a reply that my question on calculating averages had been
> > > answered, when I click on the link for the answer, it was blank. Please
> > > respond by to my question.
> > >
> > > Can this calculation of averages be done from a form?
> > >
> > > Form Name is Benton
> > > I have three fields
> > > field one is "grade"
> > > field two is "grade1"
> > > field three is "grade2"
> > >
> > > I want the average of "grade1" and "grade2" to show in the "grade" field.
> > >
> > > Can this be done with a form only, if not is there a way I can create a
> > > macro, each time I try to create a macro, I only get the pre-defined
> > > actions.
> > >
> > > Thanks for any help you can give me.
> >
> >
> >
date: Sun, 6 Jul 2008 17:09:00 -0700
author: Ken Sheridan
Re: calculating averages
It look as though you are getting the values from the underlying recordset.
In the text box try naming the text box slightly different than the field:
=AVG([txtGrade1]+[txtGrade2])
If you are still having the problem, my gues is that you are using a subform
and it is averaging those values.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
"Kit" wrote in message
news:A420C025-98D0-4344-904C-703AE6B4998F@microsoft.com...
> Hi Arvin, and thanks so very much -- I owe you one!!!
>
> I have my controlsource set with the parameters for the avg, but it's
> giving
> me both field values: i.e. say my "grade1" is 50 and my "grade2" is 60,
> the
> value in "grade" comes out as 2530. How can I merge grade1 and grade2. I
> have them as separate fields, but if you can suggest a combined field that
> will give me one result, I sure would appreciate it.
>
> "Arvin Meyer [MVP]" wrote:
>
>> Since you can do this in a form or query (or even a report) there is no
>> need
>> to store the result. In a query use an alias column:
>>
>> Grade: AVG([Grade1]+[Grade2])
>>
>> in a form or report, set the controlsource of the Grade textbox to:
>>
>> =AVG([Grade1]+[Grade2])
>> --
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.mvps.org/access
>> http://www.accessmvp.com
>>
>>
>> "Kit" wrote in message
>> news:72D70EBB-EBC4-4B89-859F-9081AAD3D52F@microsoft.com...
>> > Hi,
>> >
>> > I received a reply that my question on calculating averages had been
>> > answered, when I click on the link for the answer, it was blank.
>> > Please
>> > respond by to my question.
>> >
>> > Can this calculation of averages be done from a form?
>> >
>> > Form Name is Benton
>> > I have three fields
>> > field one is "grade"
>> > field two is "grade1"
>> > field three is "grade2"
>> >
>> > I want the average of "grade1" and "grade2" to show in the "grade"
>> > field.
>> >
>> > Can this be done with a form only, if not is there a way I can create a
>> > macro, each time I try to create a macro, I only get the pre-defined
>> > actions.
>> >
>> > Thanks for any help you can give me.
>>
>>
>>
date: Sun, 6 Jul 2008 23:14:51 -0400
author: Arvin Meyer [MVP]
|
|
