How do I calculate 7 day averages in a query?

  • Thread starter Thread starter Craig Cerff
  • Start date Start date
C

Craig Cerff

I have a table with a list of data. How do I use a query
(and perhaps and expression builder) to give me the
averages of a number of points of data. So say I have 100
points but I want to be able to generate a query that
gives me the average for every 7 points and thus end up
with around 14 averages.

Question two, I use [Forms]![MyFormName]![ControlName] to
specify the criteria for a query. The control is a combo
box from which I can select my Criteria. I would like to
be able to leave the combo box empty and then allow all
the data to be displayed. i.e. on my form I select the
dates and say the grade being produced and the sample
point. The query then generates the info according to
this selection. How do I set the query that say if I
select dates and the sample point but leave grade empty,
information for all grades for the particular sample point
is displayed or all leave sample point empty and all
information is displayed for the selected date regardless
of grade or sample point and various options of this?

Thanks
Craig
 
Q1. What is the structure of your table(s)?

Q2. This is best handled with some VBA programming, but in the query, you
can also use the LIKE operator.

WHERE field LIKE "*" & [Forms]![MyFormName]![ControlName] & "*"
 
Thanks for the reply,
I tried your suggestion but nothing seems to work. I have
tried many different options in the query but none work.
I thought IIf ( Forms![FormName]![ControlName] = IsEmpty,
[TableName]![FieldName] , Forms![FormName]![ControlName] )
would work in the criteria of my query. The false part
works,i.e. when I select something in the combo box on the
form the query runs properly. However, if I leave the
control in the form blank no results are displayed. I
thought it would use the true part and get the information
from the original table. Everything I try does one of two
things, it returns no recordsor all the records even if I
have selected soemthing in the Combo Box. I don't know
code but there must be an expression or something inserted
into the OR part of the criteria. "*" in the or part then
gives all the records even if the combo box is not blank.

On Q1 the Table has 5 fields Date, Shift, Grade, Sample
Point and Consistency. This last field is the actual
result which is entered by the user. All the others are
entered automatically, i.e. Date() and the other fields
are selected via combo boxes on the form. My goal is to
have a graph that works on a weekly moving average.
However, there is a snag, you may be running grade A one
day and the next dat Grade B. So what I want is to be
able to extract daily data and average it over 7 days for
a particular grade even if the dates do not run
consecutively. So the query pulls out the data for a
grade and then gives and average for every 7 values and
this is the basis for a graph.
-----Original Message-----
Q1. What is the structure of your table(s)?

Q2. This is best handled with some VBA programming, but in the query, you
can also use the LIKE operator.

WHERE field LIKE "*" & [Forms]![MyFormName]![ControlName] & "*"

--
Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.fmsinc.com/consulting/


I have a table with a list of data. How do I use a query
(and perhaps and expression builder) to give me the
averages of a number of points of data. So say I have 100
points but I want to be able to generate a query that
gives me the average for every 7 points and thus end up
with around 14 averages.

Question two, I use [Forms]![MyFormName]![ControlName] to
specify the criteria for a query. The control is a combo
box from which I can select my Criteria. I would like to
be able to leave the combo box empty and then allow all
the data to be displayed. i.e. on my form I select the
dates and say the grade being produced and the sample
point. The query then generates the info according to
this selection. How do I set the query that say if I
select dates and the sample point but leave grade empty,
information for all grades for the particular sample point
is displayed or all leave sample point empty and all
information is displayed for the selected date regardless
of grade or sample point and various options of this?

Thanks
Craig
Click to expand...


.
Click to expand...
 
IsEmpty - Returns a Boolean value indicating whether a variable has been
initialized.
IsNull - Returns a Boolean value that indicates whether an expression
contains no valid data.

IIf ( IsNull(Forms![FormName]![ControlName]), [TableName]![FieldName] ,
Forms![FormName]![ControlName] )


Craig Cerff said:
Thanks for the reply,
I tried your suggestion but nothing seems to work. I have
tried many different options in the query but none work.
I thought IIf ( Forms![FormName]![ControlName] = IsEmpty,
[TableName]![FieldName] , Forms![FormName]![ControlName] )
would work in the criteria of my query. The false part
works,i.e. when I select something in the combo box on the
form the query runs properly. However, if I leave the
control in the form blank no results are displayed. I
thought it would use the true part and get the information
from the original table. Everything I try does one of two
things, it returns no recordsor all the records even if I
have selected soemthing in the Combo Box. I don't know
code but there must be an expression or something inserted
into the OR part of the criteria. "*" in the or part then
gives all the records even if the combo box is not blank.

On Q1 the Table has 5 fields Date, Shift, Grade, Sample
Point and Consistency. This last field is the actual
result which is entered by the user. All the others are
entered automatically, i.e. Date() and the other fields
are selected via combo boxes on the form. My goal is to
have a graph that works on a weekly moving average.
However, there is a snag, you may be running grade A one
day and the next dat Grade B. So what I want is to be
able to extract daily data and average it over 7 days for
a particular grade even if the dates do not run
consecutively. So the query pulls out the data for a
grade and then gives and average for every 7 values and
this is the basis for a graph.
-----Original Message-----
Q1. What is the structure of your table(s)?

Q2. This is best handled with some VBA programming, but in the query, you
can also use the LIKE operator.

WHERE field LIKE "*" & [Forms]![MyFormName]![ControlName] & "*"

--
Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.fmsinc.com/consulting/


I have a table with a list of data. How do I use a query
(and perhaps and expression builder) to give me the
averages of a number of points of data. So say I have 100
points but I want to be able to generate a query that
gives me the average for every 7 points and thus end up
with around 14 averages.

Question two, I use [Forms]![MyFormName]![ControlName] to
specify the criteria for a query. The control is a combo
box from which I can select my Criteria. I would like to
be able to leave the combo box empty and then allow all
the data to be displayed. i.e. on my form I select the
dates and say the grade being produced and the sample
point. The query then generates the info according to
this selection. How do I set the query that say if I
select dates and the sample point but leave grade empty,
information for all grades for the particular sample point
is displayed or all leave sample point empty and all
information is displayed for the selected date regardless
of grade or sample point and various options of this?

Thanks
Craig
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks a million
-----Original Message-----
IsEmpty - Returns a Boolean value indicating whether a variable has been
initialized.
IsNull - Returns a Boolean value that indicates whether an expression
contains no valid data.

IIf ( IsNull(Forms![FormName]![ControlName]), [TableName]! [FieldName] ,
Forms![FormName]![ControlName] )


Thanks for the reply,
I tried your suggestion but nothing seems to work. I have
tried many different options in the query but none work.
I thought IIf ( Forms![FormName]![ControlName] = IsEmpty,
[TableName]![FieldName] , Forms![FormName]! [ControlName] )
would work in the criteria of my query. The false part
works,i.e. when I select something in the combo box on the
form the query runs properly. However, if I leave the
control in the form blank no results are displayed. I
thought it would use the true part and get the information
from the original table. Everything I try does one of two
things, it returns no recordsor all the records even if I
have selected soemthing in the Combo Box. I don't know
code but there must be an expression or something inserted
into the OR part of the criteria. "*" in the or part then
gives all the records even if the combo box is not blank.

On Q1 the Table has 5 fields Date, Shift, Grade, Sample
Point and Consistency. This last field is the actual
result which is entered by the user. All the others are
entered automatically, i.e. Date() and the other fields
are selected via combo boxes on the form. My goal is to
have a graph that works on a weekly moving average.
However, there is a snag, you may be running grade A one
day and the next dat Grade B. So what I want is to be
able to extract daily data and average it over 7 days for
a particular grade even if the dates do not run
consecutively. So the query pulls out the data for a
grade and then gives and average for every 7 values and
this is the basis for a graph.
-----Original Message-----
Q1. What is the structure of your table(s)?

Q2. This is best handled with some VBA programming, but in the query, you
can also use the LIKE operator.

WHERE field LIKE "*" & [Forms]![MyFormName]!
Click to expand...
[ControlName]
& "*"
--
Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.fmsinc.com/consulting/


"Craig Cerff" <[email protected]>
Click to expand...
wrote
in message
I have a table with a list of data. How do I use a query
(and perhaps and expression builder) to give me the
averages of a number of points of data. So say I
Click to expand...
have
100
points but I want to be able to generate a query that
gives me the average for every 7 points and thus end up
with around 14 averages.

Question two, I use [Forms]![MyFormName]!
Click to expand...
[ControlName]
to
specify the criteria for a query. The control is a combo
box from which I can select my Criteria. I would
Click to expand...
like
to
be able to leave the combo box empty and then allow all
the data to be displayed. i.e. on my form I select the
dates and say the grade being produced and the sample
point. The query then generates the info according to
this selection. How do I set the query that say if I
select dates and the sample point but leave grade empty,
information for all grades for the particular sample point
is displayed or all leave sample point empty and all
information is displayed for the selected date regardless
of grade or sample point and various options of this?

Thanks
Craig


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top