By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,949 Members | 2,038 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,949 IT Pros & Developers. It's quick & easy.

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

P: 1
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?
Sep 28 '12 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 10K+
P: 12,366
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.
Sep 28 '12 #2

Expert Mod 5K+
P: 5,397
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:

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.
Sep 28 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.