Query a table for Maximun weekly/monthly value

[rmiller]

Greetings

I have a table structured as follows:

ID Date WeekNo Month Day Hour Demand
1 2/12/2005 2 2 12 1 286.3
2 2/12/2005 2 2 12 2 315.1
..............................................................
.............................................................
100 12/02/2006 54 12 02 6 130.0


I need to two separate queries that gives the maximum demand and the date it occurs. eg


Date WeekNo MaxDemand
2/12/2005 1 315.1
2/19/2005 2 200.4
2/26/2005 3 538.2
(One record each week)
etc

and another with one record per month containing the maximum monthly demand and the date and time of the occurrence.

Date Month Hour MaxDemand
1/15/2005 1 14 320.4
2/12/2005 2 17 590.1
(One record for each month)

I am new to access and have been searching this site but cant find exactly what I need. Can someone direct me in the right direction?

Thanks for you help!!


Rmiller

 

[rmiller]

Maximum demand query

NO replies?? At least point me in the right direction. Is there a good book, a web site or a previous post that you think will help me?

 

[zdog]

Hi RMiller,

I can't think of a way to acheive the desired output using a single query, but you could use two queries.

The first query would select the max value from the demand field and is grouped by the week field.
You then use this query to reference the table to determine the date when this occured by linking the week field and demand field from the table and query 1.

You can repeat the same procedure except group by month to determine max monthly demand.

I'm relatively new to access so maybe one of you pros can figure out a way to combine what I've suggested in 2 queries into a single query.

Hope that gets you moving in the right direction
ZDog

 

[rmiller]

Query for Maximum demand

Thank you very much. I will continue to struggle with the problem until I see the light or until someone lights a candle in my area.

Also I don't necessarily need a single query. multiple queries would be great also.

 

[IanH]

One option for this is to use something called a correlated sub-query: bascially you use the results of one query (the sub-query) to filter out the results for your main query.

For the weekly query:
In essence what you do is select the date where the demand was highest for each week and then ling this back to the original table to pull off more information. This works fine unless you have multiple examples of the same high demand (ie if for example you had exactly the same maximum demand on multiple days within the week) at which point you might need to consider which one of the results you wanted to include as this will give both results.

SELECT T.Date_field, T.WeekNo, T.Demand
FROM UserTable AS T INNER JOIN [SELECT Max(Demand) AS MaxOfDemand, WeekNo
FROM UserTable
GROUP BY WeekNo]. AS T1 ON (T.WeekNo = T1.WeekNo) AND (T.Demand = T1.MaxOfDemand)
GROUP BY T.Date_field, T.WeekNo, T.Demand;

There is a similar query for the monthly output:

SELECT T.Date_field, T.MonthNo, T.Hour, T.Demand
FROM Table3 AS T INNER JOIN [SELECT Max(Demand) AS MaxOfDemand, MonthNo
FROM Table3
GROUP BY MonthNo]. AS T1 ON (T.MonthNo = T1.MonthNo) AND (T.Demand = T1.MaxOfDemand)
GROUP BY T.Date_field, T.MonthNo, T.Hour, T.Demand;


You should also consider giving different names to your date, week and month fields and these names are used as date functions.

One option would be to call them:
Date_field; WeekNo, MonthNo, DayNo or similar.

Hope this helps

Regards

Ian

 

[rmiller]

Query a table for Maximum weekly/Monthly Demand

Thanks a MILLION!!! ....There is one other thing. The Database contains several years of data and this query extracts just 12 values for monthly and 53 values for the Weekly query. Is there an easy way (or not ) to report the same thing for each year in the DB.

If I don't have a solution I could split the file into yearly files.


Rmiller

 

[IanH]

That's quite simple. If you add the Year function into both the query and sub-query and use this when joining the two (just like we did with the month value) you get the following for the weekly report:

SELECT Year(T.Date_field) AS Year_Value, T.Date_field, T.WeekNo, T.Demand
FROM UserTable AS T INNER JOIN [SELECT Max(Demand) AS MaxOfDemand, WeekNo, Year(Date_field) AS Year_Value
FROM UserTable
GROUP BY WeekNo, Year(Date_field)]. AS T1 ON (T.WeekNo = T1.WeekNo) AND (T.Demand = T1.MaxOfDemand) AND (Year(T.Date_field) = T1.Year_Value)
GROUP BY T.Date_field, T.WeekNo, T.Demand;

And this for the monthly:

SELECT Year(T.Date_field) AS Year_Value, T.Date_field, T.MonthNo, T.Hour, T.Demand
FROM UserTable AS T INNER JOIN [SELECT Max(Demand) AS MaxOfDemand, MonthNo, Year(Date_field) AS Year_Value
FROM UserTable
GROUP BY MonthNo, Year(Date_field)]. AS T1 ON (T.MonthNo = T1.MonthNo) AND (T.Demand = T1.MaxOfDemand) AND (Year(T.Date_field) = T1.Year_Value)
GROUP BY Year(T.Date_field), T.Date_field, T.MonthNo, T.Hour, T.Demand;

And this for the monthly:

Regards

Ian

 

[rmiller]

Query for Maximum Demand

Ian

THANK YOU!!

You are a scholar and a gentleman!

Rmiller