Complex Select Query for finding Min & Max of a Group

[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 (Image attached).

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.

[rpeare]

SO you want the result to return (using your example) only records that have a beg week month of 2011/6/25 or an end week month of 2011/9/23?

[trksbc]

Hi,

Thanks 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!

[rpeare]

I think I need a bit more explanation.

How are you are you determining the beginning and ending day of the month, I assume it's some formula based on the week ending date but if you could tell me how those are formulated it would help. It appears as though you want the month start to be the first saturday on or before the first day of the current month and end on the last friday before the end of the month? If that's the case why does your table mark 12/31/2010 as part of 2011 instead of 2010? 12/31/2010 falls exactly on a friday which would seem to put it in CY 2010.

Secondly, it appears that your Year (you are using reserved words for field names which is a problem, that will likely cause you difficulties as you do more programming) is based on not the actual calendar year but the month start and end dates you generate from the month beginning calculation, is that correct?

Lastly, you do not need to store the quarter, month or year in your table (month and year are reserved words, don't use them, try to use compound words or abbreviations with no spaces, or if you need a separator use an underscore _) These can be calculated when a query runs so you are storing data that you do not need to.

[trksbc]

Hi rpeare,

Thanks for checking this post. I have tried to answer your questions below:
-The week starts on Saturday.
-The Beg date is Week end date(Friday) - 6 days, (so it will be Saturday).
-The Month in my calender is different from the actual calender, hence my first week of 2011 starts in 2010.
-The calender will be given to me at the beginning of year specifying when the week or quarter starts and what are the week end dates.(Hence 12/31/2010 is part of 2011).

-I am just starting to learn Access & programming. I will take you advice and avoid using system keywords.

-I will not be able to use actual calender as my calender differs and I am not in a position to decide when the week or the quarter starts.

Thanks!

[rpeare]

Well, I was under the impression you could figure out what month/quarter your items belonged to without needing an outside source, but you can't so this is my suggestion.

Make a table that has all of your week endings for the year, include the beginning of the month in that table and the ending of the month as well. For the purposes of your example your beginning/ending points are arbitrary (they can't be determined by the program and need to be supplied) Then when you run your query just link your week ending to this subsidiary table.