Query for finding Min & Max of a Month (1 Viewer)

[trksbc]

Hi,

I have to create a select query (which I think is a bit complex). I have to retrieve Week, Year and month from a Master table. I find this simple. Apart from this I need to find the starting date of a particular month and last date of the particular month for the respective month. The problem is the month is not a calender month and dates differ. It will be easy to understand if you have look at the table(attached Zipped Image file).

The columns "Week", "Year" & "Month" are directly selected from the master table. The Column "Beg Week Month" & "End Week Month" have to be created in the query. I am able to find Min of "Beg Week Month" & Max of "End Week Month", but I need for that particular month as displayed in the table.

Let me know if anybody can help me to write this query.

Thanks a lot in advance.

 

[Guus2005]

You should have a table which show the relation between the year the month and the start and enddate of that period.

Say the name of that table is Period and the fields are Year, Month, BegWeekMonth and EndWeekMonth
Fill the table accordingly. You should have twelve records for each year.

Your query will look like this:

select M.Week, M.Year, M.Month, P.BegWeekMonth, P.EndWeekMonth 
from Master M inner join Period P on M.Year = P.Year, M.Month = P.Month

HTH

 

[Galaxiom]

Tables like that are rarely necessary and become a maintenance burden. It is much better to take the time to understand the logic that is behind the ranges and create the expressions that generate them.

 

[trksbc]

Hi,

Thanks everyone for looking in to this. I think, I have not been able to express my requirement completely in words. Hence I have attached the MDB with required result & current query which I developed.

"Required_Table_Current_Qtr" is the required result

"Master_Week_Ending" is the Master table

"q_Current_Qtr" is my query to select "Beg Week Date" & "End Week date" of the month.

Right now, I'm able to select dates for the week, but i want dates for the month.
eg:

Current query result:
Month Beg Week Date End Wk Date
7 2011/06/25 2011/07/01
7 2011/07/02 2011/07/08
7 2011/07/09 2011/07/15
7 2011/07/16 2011/07/22
8 2011/07/23 2011/07/29
8 2011/07/30 2011/08/05
8 2011/08/06 2011/08/12
8 2011/08/13 2011/08/19
8 2011/08/20 2011/08/26

Required query result:
Month Beg Week Date of Month End Week Date of Month
7 2011/06/25 2011/07/22
7 2011/06/25 2011/07/22
7 2011/06/25 2011/07/22
7 2011/06/25 2011/07/22
8 2011/07/23 2011/08/26
8 2011/07/23 2011/08/26
8 2011/07/23 2011/08/26
8 2011/07/23 2011/08/26
8 2011/07/23 2011/08/26

Thanks a lot!

 

[Guus2005]

First create the table i mentioned. In this case a dynamic table (query)

Call it: qryDynamicMonthRange

SELECT Master_Week_Ending.Year, Master_Week_Ending.Quarter, Master_Week_Ending.Month, Min(DateAdd("d",-6,[Week_Ending])) AS MonthStart, Max(Master_Week_Ending.Week_Ending) AS MonthEnd
FROM Master_Week_Ending
GROUP BY Master_Week_Ending.Year, Master_Week_Ending.Quarter, Master_Week_Ending.Month;

Now to create the result you want:

SELECT Master_Week_Ending.Week_Ending, Master_Week_Ending.Year, Master_Week_Ending.Month, qryDynamicMonthRange.MonthStart, qryDynamicMonthRange.MonthEnd
FROM Master_Week_Ending INNER JOIN qryDynamicMonthRange ON (Master_Week_Ending.Month = qryDynamicMonthRange.Month) AND (Master_Week_Ending.Quarter = qryDynamicMonthRange.Quarter) AND (Master_Week_Ending.Year = qryDynamicMonthRange.Year)
WHERE (((Master_Week_Ending.Quarter)=[Select the Quarter:]));

I am not sure why you want to select a quarter?

HTH

 

[trksbc]

Hi Guus2005,

Thanks a lot for the solution. This is a small part of Complex MDB which gets data from external servers. The dates in this created query/table are read during macro execution & data is fecthed accordingly. At present I was populating this manually, but now with your help it is automated.

Thanks a lot !

 

[Galaxiom]

I'm guessing from the small sample of data, that your "months" periods start on the fourth Saturday of the previous calender month.

As I said earlier, whatever the logic, it would be a far better solution to create the expressions or functions that generate the start and end dates of those periods. Otherwise your database is dependent on continuing to manually enter those periods.

Moreover the recording the date of the both the end of one period and the start of the next perid (ie the next day) is a breach of normalization. Recording anything about the weeks is also breaching normalization since these dates can definitely be derived from the "month" periods.

If you let us know the rational behind the periods we could easily provide a function that will generate the dates on demand and completely eliminate the need for the clumsy tables.

 

[Guus2005]

Hi Guus2005,

Thanks a lot for the solution. This is a small part of Complex MDB which gets data from external servers. The dates in this created query/table are read during macro execution & data is fecthed accordingly. At present I was populating this manually, but now with your help it is automated.

Thanks a lot !

Your welcome!