Dates..(Week of Month) 2

[dobe1]

What I hope to learn:
I am looking for an SQL way (in Access) to find the numeric week of the month. In other words 1st, 2nd, 3rd, etc week of the month. The criterion is that the week must be a full week, and must extend to a full 7 days. I have data that must be reported by the week. I will be reporting the number of missed pick-ups per week / per month.

The data I have available:
I have a flat file with individual dates of each missed pick-up.

My attempt so far:
I have created a second file with the date range of each week throughout the remaining year. This is really a small amount of inputing. I have a begining date, end date and a field for the week:
beg_date end_date, week_nr.

I had tried to use a subquery for comparing the range to the actual date of the missed pickup, but I could not make this work.

Any ideas?

Dobe

 

[lupins46]

I'm not clear about how or where you are trying to get the week but given your table is ther then one option is a DLookup.

DLookup("week_nr", "Mytablename", "Mydatefield >=beg_date AND mydatefield<=end_date")

 

[Crowley16]

take a look at the weekday function, F1...

you should be able to use this function directly in your sql

so:

SELECT weekday([Date], vbMonday) FROM ....

 

[dobe1]

I'm not clear about how or where you are trying to get the week but given your table is ther then one option is a DLookup."

Thanks all. I am sorry, I was not more clear. Starting with Sunday and of course a calendar, each month will have between 4 and five full weeks. The first full week in July starts on the 4th. That would be week 1. I have a file that has a date of an event, i.e. the date of the missed pick-up.

What I did last night was to use "switch" and test for all possibilites.
UPDATE work_order SET wk_nr = switch([org_date] Between #5/2/2004# And #5/8/2004#,1,[org_date] Between #5/9/2004# And #5/15/2004#,2,[org_date] Between #5/16/2004# And #5/22/2004#,3,[org_date] Between #5/23/2004# And #5/29/2004#,4,[org_date] Between #5/30/2004# And #6/5/2004#,5,[org_date] Between #6/6/2004# And #6/12/2004#,1,[org_date] Between #6/13/2004# And #6/19/2004#,2,[org_date] Between #6/20/2004# And #7/26/2004#,3,[org_date] Between #7/27/2004# And #8/3/2004#,4)
WHERE not bus_line is null;

This is what may be a long way around, but will allow me to group by month and then by week.

 

[dhookom]

Try this expression:

UPDATE work_order
SET wk_nr = Day([Org_Date]-Weekday([Org_Date])+1)\7+1
WHERE not bus_line is null;


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MichaelRed]

I'm not only not clear on what you are attempting, I'm also confused by the overall statement. Specifically, If you 'define' a week as starting on SUNSAY, lasting a full Seven Days, where ALL of the days occur within the same calendar month, I immediatly stu7mble into two closely related issues:

1[tab]Some Days / date will not be reported. e.g. If the last day of a nonth is Wednesday, the week does not fall into the month. Nor does it fall into the following month, at least according to the criteria.

2[tab]Your statemetn that there are four or five full weeks in a month is incorrect. e.g. June, 2004 starts on Tuesday, making the first Sunday the sixth, Subsquent Sunday dates in June are 13, 20 and 27, however the "week of the 27th dribbles over into July, so according to the stated criteria, it is not counted as a "June" week, Thus the only "FULL" weeks start on the 6th, 13th, and 20th. I only see / count three. Further there can NEVER be more than 4 "Full" weeks, and that will occur only about 1/2 the time.

In briefly reviewing your statement above, you obviously did not follow the 'stated' criteria (e.g.

,[org_date] Between #5/30/2004# And #6/5/2004#,5

which obviously 'leaks' over from May into June.

Without some additional info, I cannot say for certain, however it looks like you could generate the dates & week nuumbers (programatically) in a recordset and do aggregate queries based on joins between the week dates.

I generated a small procedure which can be used to populate a table with the start and endates for each week which starts on a Sunday throughout a Year, including what I infer you need for the "week Numbers) for both the Week and the Year. The table consist of JUST these four columns, with the two defined as date and the others as numeric.

dtStrt	        dtEnd	   WkInMnth	WkInYr
1/4/2004	1/10/2004	1	1
1/11/2004	1/17/2004	2	2
1/18/2004	1/24/2004	3	3
1/25/2004	1/31/2004	4	4
2/1/2004	2/7/2004	1	5
2/8/2004	2/14/2004	2	6
2/15/2004	2/21/2004	3	7
2/22/2004	2/28/2004	4	8

... continuing through weekj 52, commencing 12/26

The Procedure:

Public Function basFullWks(dtYr As Date)

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Dim dtFirst As Date
    Dim dtSun As Date
    Dim dtTmp As Date
    Dim Idx As Integer
    Dim Jdx As Integer
    Dim strSQL As String

    'Clear Previous
    DoCmd.SetWarnings False
        strSQL = "Delete * from tblWeeks;"
        DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblWeeks", dbOpenDynaset)
    
    dtFirst = DateSerial(Year(dtYr), 1, 1)

    dtSun = DateAdd("d", 8 - Weekday(dtFirst), dtFirst)

    Idx = 1
    Jdx = 1
    Do

        dtTmp = DateAdd("ww", 1, dtSun)

        With rst
            .AddNew
                !dtStrt = dtSun
                !dtEnd = DateAdd("d", 6, dtSun)
                !WkInMnth = Idx
                !WkInYr = Jdx
            .Update
        End With


        If (Month(dtTmp) <> Month(dtSun)) Then
            Idx = 0
        End If

        If (Year(dtTmp) <> Year(dtYr)) Then
            Exit Do
        End If

        dtSun = dtTmp
        Idx = Idx + 1
        Jdx = Jdx + 1
    Loop
End Function

An Alternative, might be availalable to you with the use of the Partition predicate from (at least Jet) SQL, but since the dates for the start and end periods of the months and year(s) still need to be generated, the posted procedure could be modified to generate the start and end dates appropiatly.




MichaelRed
mlred@verizon.net

 

[dhookom]

I agree with Michael that the requirements and example weren't consistent. However, the simple expression that I provided I think meets the "assumed" requirements.

I appreciate the posting of the big Switch() expression but this contained errors
[Blue]#6/20/2004# And #7/26/2004#,3,[/blue]
since this clearly was meant to be 6/26/2004.

My interpretation of "an SQL way (in Access)" was that you didn't want to use code in your solution.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dobe1]

I appreciate the posting of the big Switch() expression but this contained errors
#6/20/2004# And #7/26/2004#,3,
since this clearly was meant to be 6/26/2004.

My interpretation of "an SQL way (in Access)" was that you didn't want to use code in your solution. "

Duane,
You are correct on both assumptions. First, that is an errro. Second, I was looking for a non-code method.

Thanks all. I am digesting all of the suggestions.

Dobe

 

[dobe1]

Michael:

"1 Some Days / date will not be reported. e.g. If the last day of a nonth is Wednesday, the week does not fall into the month. Nor does it fall into the following month, at least according to the criteria.

2 Your statemetn that there are four or five full weeks in a month is incorrect. e.g. June, 2004 starts on Tuesday, making the first Sunday the sixth, Subsquent Sunday dates in June are 13, 20 and 27, however the "week of the 27th dribbles over into July, so according to the stated criteria, it is not counted as a "June" week, Thus the only "FULL" weeks start on the 6th, 13th, and 20th. I only see / count three. Further there can NEVER be more than 4 "Full" weeks, and that will occur only about 1/2 the time.
"


Michael,
This data prep is for a report which is to be grouped by week. Now the logic I am using is that I am interested in comparing 7 days to other 7 day sets.

Therefore, I will take the first full week of a month, and make it week 1, the second will be two, etc. On those days where the last of the calendar month does not have a full seven days, I will acquire the remainder of the set of 7 from the begining of the next month.

Remember, this is not comparing month to month, but week to week. Again, I am looking to compare a 7 day set with another 7 day set.

The help is appreciated. The switch statement works ( with the exception of the date error I made). The problem is that I will eventually turn this over to someone else to maintain. But for now, I hooked Crystal into my database, and it does give me what I want, just not as eliquently as I had hoped.

 

[dobe1]

dhookem,
I just tested out your script. It runs well, and should not have to be altered at all. Thanks for taking the time.

Dobe

 

[cdavisdccb]

Micheal Red,

I've been looking for this for a while. I think you've provided the closest function that I could use. But it's giving me wrong years. When I inserted "2004" between the parenthesis, it returns data for "1905".

If I could figure this syntax out, then I can take the WeekInYear of the dates from my main table, and query them against the WeekInYear from the table that was created by your function in order to return the WeekInMonth.

I'm currently using the following code but it was only accurate for June 2004!

Switch(Day([Date])<7,1,Day([Date])<14,2,Day([Date])<21,3,Day([Date])<28,4,True,5)

Anyway, if you could help me on the syntax, I'd appriciate it.
Thanks,
Chris

"It doesn't matter how idiot-proof you make it, someone makes a better idiot!"
- Bazooka Joe

 

[MichaelRed]

It is a DATE type argument, thuis requires a 'full' date deffinition. Whilst the Month and Year are not used (in this incantation at least), similar functions might (e'g' someione might want to modify it to generate their 'fiscal year' calendar. If you want to just want it to accept an Intgeger represesntation of the yeay, you need to change the data type of the arg, as well as the DateSerial statement.





MichaelRed
mlred@verizon.net

 

[cdavisdccb]

Cool. Thanks for the code.

Works great using: Date() for (dtYr).

Chris

"It doesn't matter how idiot-proof you make it, someone makes a better idiot!"
- Bazooka Joe