How do I make my query sum values over the past two weeks, starting today?

[cowcarx]

This is a two part question.

My Query looks something like this:

Date//Operator//Total Parts//Total Earned Hours//Total Time

1. I want the query to sum Parts/Hours/Time over the past two weeks, starting from the current date. I'm trying different variations of the Date() but I can't find any helpful material online.


2. I also want to divide the Earned Hours column by Total Time column. I tried to create a report and add a field that divides the two values. This DOES work, however it won't let me sort the new custom field from largest to smallest or vice versa using the Add Group/Sort function. What am I doing wrong here?

[Rabbit]

1) Date() - 14 will give you 2 weeks ago. Or you could use the DateAdd() function and give it a negative number.

2) You will have to sort by the formula if that's possible, I don't know if it is off the top of my head. If not, calculate it in the query and sort by the new field.

[zmbd]

Do you need running totals or over all type calc.

You don't mention which version you're using... 2003, I don't think you can sort/group at report time on a calculated field as the value isn't actually stored anywhere; however, in 2007 and 2010 you should be able to (This article mentions doing so, but not how to: http://office.microsoft.com/en-us/ac...010108354.aspx)

Please be aware that normally the site rules allow only one question per thread. This makes it much easier for the "Experts" (Rabbit, NeoPa, and so many others) to help by focusing on the one issue, and for people to search the site for answers in that they don't have to un-wind a multi-level question.