Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question

    Hello

    I used Access' Help Topics to find out how to show only the current week in a Query. This worked OK, and the notes stated that Access considers a Work Week as beginning on Sunday. I created 4 Test Records: 3 within this week's range (ie, 6 Sept 09 to 12 Sept 09) and 1 outside of this range. When I ran the Query, Access returned the 3 results I was expecting.

    I next wanted to create a Report that shows ALL Dates, grouped by Week. But when the Report ran, Access has grouped 1 result (the Sunday test data) as being Week 36 and has then listed the other 2 results under Week 37.

    Why does an Access Query begin on a Sunday yet an Access Report groups with a week beginning on a Monday?

    What's the easiest way to make these both start on the same day?

    Thanks for any advice.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='GreenLantern' post='792342' date='06-Sep-2009 13:05']...
    ...
    What's the easiest way to make these both start on the same day?
    Thanks for any advice.[/quote]
    The simplest way to make it sync up correctly is to calculate the week in the query, and then use that as your value to group by in the Reports Sorting and Grouping capability. This could be a variance based on what version of Access you are using, so that information would be helpful. And if you want to post the specific expressions you use in both the query and the Grouping areas, we can help you debug it.
    Wendell

  3. #3
    New Lounger
    Join Date
    Sep 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='792343' date='06-Sep-2009 20:16']The simplest way to make it sync up correctly is to calculate the week in the query, and then use that as your value to group by in the Reports Sorting and Grouping capability. This could be a variance based on what version of Access you are using, so that information would be helpful. And if you want to post the specific expressions you use in both the query and the Grouping areas, we can help you debug it.[/quote]

    The Where part of the Query in SQL view is as follows:

    WHERE (((DatePart("ww",[DateWorked]))=DatePart("ww",Date())) AND ((Year([DateWorked]))=Year(Date())));

    This is what the Access Help recommended.

    The Report just uses Access 2007's Group & Sort pane at the bottom of the screen.

    BTW, will that translate if a user with Access 2003 opens the Report?

    Thanks for your reply and help.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    What field are you using to group on at the bottom of the report? It should be the same statement as your Where clause, but without the WHERE statement if you do not have that as a calculated expression in the SELECT statement. It would help to have the whole SQL statement, not just the WHERE part. As to working with 2003, if that is a concern, do your development in 2003 - things are generally upward compatible (with a few major exceptions in 2007) but the other way is not nearly as compatible. For example the .ACCDB format is not supported in 2003 at all.
    Wendell

  5. #5
    New Lounger
    Join Date
    Sep 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='792370' date='07-Sep-2009 00:29']What field are you using to group on at the bottom of the report? It should be the same statement as your Where clause, but without the WHERE statement if you do not have that as a calculated expression in the SELECT statement. It would help to have the whole SQL statement, not just the WHERE part. As to working with 2003, if that is a concern, do your development in 2003 - things are generally upward compatible (with a few major exceptions in 2007) but the other way is not nearly as compatible. For example the .ACCDB format is not supported in 2003 at all.[/quote]

    SELECT tblStaff.StaffName, tblProject.ProjectID, tblProperties.PropertyName, tblProperties.PropertyAddress1, tblProperties.Town, tblWork.DateWorked, tblWork.HoursWorked
    FROM ((tblWork INNER JOIN tblProject ON tblWork.ProjectID = tblProject.ProjectID) INNER JOIN tblProperties ON tblProject.PropertyID = tblProperties.PropertyID) INNER JOIN tblStaff ON tblWork.StaffID = tblStaff.StaffID
    WHERE (((DatePart("ww",[DateWorked]))=DatePart("ww",Date())) AND ((Year([DateWorked]))=Year(Date())));

    The Report doesn't have any expressions or Criteria in the underlying Query (it's the most basic of queries). The Report is grouped firstly by StaffName and then Grouped by DateWorked (by Week) then Sorted by DateWorked.

    Thanks for your ongoing help.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='GreenLantern' post='792408' date='07-Sep-2009 02:45']SELECT tblStaff.StaffName, tblProject.ProjectID, tblProperties.PropertyName, tblProperties.PropertyAddress1, tblProperties.Town, tblWork.DateWorked, tblWork.HoursWorked
    FROM ((tblWork INNER JOIN tblProject ON tblWork.ProjectID = tblProject.ProjectID) INNER JOIN tblProperties ON tblProject.PropertyID = tblProperties.PropertyID) INNER JOIN tblStaff ON tblWork.StaffID = tblStaff.StaffID
    WHERE (((DatePart("ww",[DateWorked]))=DatePart("ww",Date())) AND ((Year([DateWorked]))=Year(Date())));

    The Report doesn't have any expressions or Criteria in the underlying Query (it's the most basic of queries). The Report is grouped firstly by StaffName and then Grouped by DateWorked (by Week) then Sorted by DateWorked.

    Thanks for your ongoing help.[/quote]
    Try changing your query to include explicity the WeekWorked as an expression like this:
    Code:
    SELECT tblStaff.StaffName, tblProject.ProjectID, tblProperties.PropertyName, tblProperties.PropertyAddress1, tblProperties.Town, tblWork.DateWorked, tblWork.HoursWorked, DatePart("ww",[tblWork.DateWorked]) AS WeekWorked
    FROM ((tblWork INNER JOIN tblProject ON tblWork.ProjectID = tblProject.ProjectID) INNER JOIN tblProperties ON tblProject.PropertyID = tblProperties.PropertyID) INNER JOIN tblStaff ON tblWork.StaffID = tblStaff.StaffID
    WHERE (((DatePart("ww",[DateWorked]))=DatePart("ww",Date())) AND ((Year([DateWorked]))=Year(Date())));
    Then do your Group on the report by WeekWorked.

    I am also suspicious that your criteria may not give you exactly what you want. I understand the Year criteria - you only want to show reports from this year. However the part looking at the week should actually only give you work done in the current week - so if it's Monday there won't be much if any. I would take that out until you are satisified you have something that works the way you want it to. Then what you may want is work done during the last week and the current week.
    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
  •