Min, Max & Average Query

Question

Post reply

Min, Max & Average Query

cstg85

Ten Centuries

Points: 1264
More actions
October 29, 2014 at 8:07 pm

#75701

I am trying to convert a query that I know works in management studio to an Access query. Every day a test is run against a product, each minute that the test is running access records a row in the table, each row is date/time stamped. Since the table is in SQL Server I was able to query it from management studio. However, I would like the end user to be able to call that same query in from the access 2010 "front end", but I am having an issue getting it to work in Access 2010.
Here is my T-SQL Code:
SELECT CAST(measure_time AS DATE) AS 'Date'
, MIN(fAtmPM) AS 'Min'
, MAX(fAtmPM) AS 'Max'
, AVG(fAtmPM) AS 'Average'
FROM My_Table
GROUP BY CAST(measure_time AS DATE)
ORDER BY DATE DESC;
This query pulls back the min, max and average of each day. Everything I have tried in access so far does not pull back a single row for each date, it selects all rows for each day.
Any suggestions? Thank you!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WendellB SSCrazy Eights Points: 8678 More actions · Answer 1

Try the following:

SELECT Min(My_Table.fAtmPM) AS Min, Max(My_Table.fAtmPM) AS Max, CDate(Avg([fAtmPM])) AS Average

FROM My_Table;

Note that DATE is a reserved word in Access because there is a Date() function that returns the current system date.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

cstg85 Ten Centuries Points: 1264 More actions · Answer 2

WendellB,

Thank you for the quick response, I attempted to use the query you provided and in the average column it puts the date. So my goal is to have a distinct date value pulled then the min, max and average number for that day. I tried to use a distinct but it calls a distinct value for each column not just the date. Is there anything else I can try?

Thank you!

WendellB SSCrazy Eights Points: 8678 More actions · Answer 3

I think I missed something here - are you saying that you want to pull all records from a specific date, and then get the min, max and avg for some other column value. I assumed from your T-SQL that the column 'fAtmPM' was a date column. If that is incorrect, then remove the CDate() function from the SQL, and put a WHERE clause at the end specifying what day you want records from.

Otherwise, post more details or some sample data and I'm sure this can be worked out.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

cstg85 Ten Centuries Points: 1264 More actions · Answer 4

Wendell, thank you for your patience with me!

Here is some sample data, right now I have data in two columns in a table. The first column is a date time column, the second is a recorded value. Each day a test is run and data is collected every minute while the test runs, a value is recorded with a date / time stamp.

measure_time || fAtmPM

-----------------------------------------------------

2014/11/01 12:01:00 || 1034.72229003906

2014/11/01 12:02:00 || 1028.10180664063

2014/11/01 12:03:00 || 1023.73840332031

2014/11/02 15:00:00 || 1034.79748535156

2014/11/02 15:01:00 || 1025.76965332031

Here is the output I am looking for:

measure_time || min || max || Avg ||

----------------------------------------------------------------------------------------------------------------

2014/11/01 || 1023.73840332031 || 1034.72229003906 || 1028.854166666667 ||

2014/11/02 || 1025.76965332031 || 1034.79748535156 || 1030.283569335935 ||

So what is happening is that there is a single line for each day and is gathers all of the columns for that day and provides the min, max and average of the second column.

Does this help clarify?

Thanks!

WendellB SSCrazy Eights Points: 8678 More actions · Answer 5

OK, that clarifies things. Here is the SQL for doing what you want:SELECT CDate(Int([measure_time])) AS [Date Measured], Min(My_Table.fAtmPM) AS [Min], Max(My_Table.fAtmPM) AS [Max], Avg(My_Table.fAtmPM) AS [Avg]

FROM My_Table

GROUP BY CDate(Int([measure_time]));

Note that Access does not have a field type that is strictly a date, so the expression CDate(Int([measure_time])) takes the integer portion of the DateTime field and then formats it as a date only - equivalent to CAST as Date in SQL Server.

Here are the results I get:

Date Measured Min Max Avg

11/1/2014 1023.738403320311034.722290039061028.85416666667

11/2/2014 1025.769653320311034.797485351561030.28356933594

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

cstg85 Ten Centuries Points: 1264 More actions · Answer 6

WendellB,

The code snippet in your previous email worked, thanks again for all your help, time and patience!