Hi Matt,
That both the date and time are in the same field is a very, very good
thing. There is a "gotcha" however. I'll describe it below.
The SQL statement I sent is one way to manually create a query. As you know,
Access has the QBE tool that creates the SQL statements. I'll describe how to
create a similar query using the QBE tool. I'm assuming that you are using
Access 2000 to 2003 and not 2007.
First open up a new query based on the table(s) that you need. Then drag
down the fields needed. So far nothing new yet!
Next put in the following in the criteria below the time field. The stuff
between the brackets are parameters. Access will prompt you to input the
start and end times.
Between [Enter Start Time] And [Enter End Time]
Here's where things can get strange. If you put in an end time of 6/4/2007
and have a record that's 6/4/2007 1PM, it won't show up. 6/4/2007 is at
midnight and anything else after that won't show. If you remember to use the
next date (6/5/2007) that will include all of the 4th but could sneak in
records from the 5th.
What to do? Remembering to put in both the date and time is one option, but
a little clunky.
Here's one of my favorites. Adding the .99999 puts the time right about
11:59:59 PM so that the entire day is covered.
Between [Enter Start Time] And [Enter End Time] +.99999
Another option that gives you better control and allows you to pick down to
the minute:
= [Enter Start Time] And < [Enter End Time]
Now to the Parameters statement. When prompted for a parameter, Access
guesses if you are putting in text, numbers, or dates. Sometimes it gets it
wrong. In fact it will cause errors with crosstab queries unless you
precisely define the data type of the parameter.
To define the parameter, go up to Query on the menu and pick Parameters in
the drop down. In the first box you need to put in the parameter just like
it's in the criteria of the query. Then you need to pick Date/Time in the
Data Type. Since you have two parameters, you need to enter both of them. The
result should look like so:
Parameter Data Type
[Enter Start Time] Date/Time
[Enter End Time] Date/Time
Note that even a slight misspelling such as an extra space will cause
errors. Also the Parameter window is real tight and difficult if you have
long parameters. After entering the parameters, click OK.
Now would be a very good time to save the query and run it to see if it's
grabbing the correct records. If so, we can move on to the average.
To get to things like average, counts, and sums, go up to View, Totals.
Notice that it's added a line with Group By to each column in the grid. What
you want to do here is change the Group By under Time to Avg. Also change the
Group By under Time to Where. (You don't want to group by time or average up
the time).
Run the query and see if that's what you want. Modify as necessary.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
Matt said:
PARAMETERS [Enter Start Time] DateTime, [Enter End Time] DateTime;
SELECT SENSOR, Avg(TEMP) AS AvgTemp
FROM YourTable
GROUP BY SENSOR
HAVING [Time] Between [Enter Start Time] And [Enter End Time];
For this to work, the TIME field must be a Date datatype. Also what about
the Date portion?
The date and time are actually in the same field (5/29/07 9:15PM for
example). Will that work?
Also, I'm fairly unfamiliar with Access. I don't know where
parameters are, for example, or what you mean by "Avg(TEMP) as AvgTemp
from your table". Little help?
