Reply to thread

Message

<blockquote data-quote="Sir Whiskers" data-source="post: 771327" data-attributes="member: 6941">Just off the top of my head, could you set up a parameter query to ask you what the earliest date is you want to pull, then just input the current date minus one year?Example:Group By: SSNSum: Absent HoursSum: OccurencesWhere: WeekEnd Date &gt;= [Enter date]I haven't played with this, so I don't know if you'll have any problems with a paricular date yielding 51 weeks or 53 weeks. As a check, you could add a column to your query to Count WeekEnd Date - if the count was not 52, you'd have to tweak the date you input as a parameter.Another potential problem - how do you plan to handle employees with less than 1 year's service? Their averages would be off (in their favor). A possible solution would be to Count WeekEnd Date as I mentioned above, then use that number to determine the value you divide by.Again, I haven't actually put this together, but hopefully it will point you in the right direction.PS - If someone knows the correct syntax for DateDiff in a query, that might simplify things. Just use DateDiff to pull all records with a WeekEnd Date within 52 weeks of the date entered in the parameter.</blockquote>

[QUOTE="Sir Whiskers, post: 771327, member: 6941"] Just off the top of my head, could you set up a parameter query to ask you what the earliest date is you want to pull, then just input the current date minus one year? Example: Group By: SSN Sum: Absent Hours Sum: Occurences Where: WeekEnd Date >= [Enter date] I haven't played with this, so I don't know if you'll have any problems with a paricular date yielding 51 weeks or 53 weeks. As a check, you could add a column to your query to Count WeekEnd Date - if the count was not 52, you'd have to tweak the date you input as a parameter. Another potential problem - how do you plan to handle employees with less than 1 year's service? Their averages would be off (in their favor). A possible solution would be to Count WeekEnd Date as I mentioned above, then use that number to determine the value you divide by. Again, I haven't actually put this together, but hopefully it will point you in the right direction. PS - If someone knows the correct syntax for DateDiff in a query, that might simplify things. Just use DateDiff to pull all records with a WeekEnd Date within 52 weeks of the date entered in the parameter. [/QUOTE]

Verification