Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Week Ending Date (AC97 SR-1)

    I have a table which collects data on a daily basis. The factory supervisor wants to see data summarised weekly.

    So far I have created a Totals Query that groups the data by week using the folowing:

    Date By Week: Format$([tblRewindData].[Date],"ww").

    There are a number of problems. Firstly, the data sorts so that Week 9 comes after week 24 (current week no of 2003). I assume that this is because the week no is alphanumeric. In addition, I have been trying (unsuccessfully), to create another calculated field on the query to show the Saturday Week ending date for a week.

    I might add that I am not an SQL guru, but prefer to solve problems with formulas or functions.

    There has gotta be a way, and I thank in advance any who might assist.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Week Ending Date (AC97 SR-1)

    I wonder if you could do the following:
    Format(Format$([tblRewindData].[Date],"ww"),"00")

    >>I have been trying (unsuccessfully), to create another calculated field on the query to show the Saturday Week ending date for a week. <<
    What exactly do you mean by this? Would you give an example please?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Week Ending Date (AC97 SR-1)

    The expression [tblRewindData].[Date] + 7 - WeekDay([tblRewindData].[Date]) returns the Saturday in the week of [tblRewindData].[Date].

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Week Ending Date (AC97 SR-1)

    Thanks Pat,

    For Week No 10, The week ending Saturday is 8th March 2003. I need to calculate that date just from the week number.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Week Ending Date (AC97 SR-1)

    If you change your week number to a number, the sort should be OK - I'm curious why you are using the format command?

    As to the Saturday date for a week, as long as you are running a more or less standard date setup on your PC (so weeks begin on Sunday and end on Saturday), you should be able to compute the Saturday date for each record as a part of your query using the WeekDay function. The syntax should look something like:
    <font face="Georgia"><font color=blue>SaturdayDate=RecordDate + (7-WeekDay(RecordDate))</font color=blue></font face=georgia>
    That expression should work just fine as a calculated field in a query, and if you do a GroupBy on the field, you should get a single record with the Saturday date (unless you have the date as a time stamp - then use the int function to truncate it).
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Week Ending Date (AC97 SR-1)

    Just tried the "00" trick. Beudy Mate!

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Week Ending Date (AC97 SR-1)

    Does this mean you want to calculate a Saturday date for a given week number?

  8. #8
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Week Ending Date (AC97 SR-1)

    Yes thanks Pat. Because it's a Totals Query, I cant include the date field, using group by, as it 'unsumarises' my weekly data. So for week no 'nn ' I need to calculate Saturdays (or in other words, 'Week Ending...") Date.

  9. #9
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Week Ending Date (AC97 SR-1)

    Thanks Wendell.
    Don't quite understand 'Time Stamp', but date field is part of multiple key to table.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Week Ending Date (AC97 SR-1)

    What year do you want to base the calculation on?

    The calculation that follows is based upon a date field from a table of mine that could quite easily be changed to yours:
    (WeekNumber - 1) * 7 + 7 - Weekday(DateSerial(Year(RecordDate ),1,1)) + DateSerial(Year(RecordDate ),1,1)

    But, if the week number is calculated from a date in your query, then that date can be used as in Wendell's suggestion to calculate the Saturday Week End date as follows:
    SaturdayDate=RecordDate + (7-WeekDay(RecordDate))

  11. #11
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Week Ending Date (AC97 SR-1)

    OK. I admit to being somewhat confused. I think you guys are all saying the same thing, but I'm not getting it.

    Have attached a sample database with my table and query.
    Attached Files Attached Files

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Week Ending Date (AC97 SR-1)

    I have included 2 additional queries that were based upon your query and both calculate the Saturday Week End date in different ways. As you can see Wendell's is the most concise.
    Attached Files Attached Files

  13. #13
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Week Ending Date (AC97 SR-1)

    Ahhhh! (Please here see light going on in Devious's brain)


    Many thanks to both Wendell and Pat. I now march forward

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Week Ending Date (AC97 SR-1)

    Time stamps are obtained when you use the Now() function instead of Date(). Date/Time fields store dates as the integer part of a numeric field, and the time is stored as the decimal part of the field. And, yes, I think we all tried to say pretty much the same thing, all at the same time. When I started my response, nobody else had responded, I got interupted for a couple of minutes, and when I did post you already had several responses.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •