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

Totals by time period

P: 78
Hi again!

I have a question in regards to an approach.

I have a report that servers as a breakdown report. I need to have my report return totals based on different time criterion. For example:

Expand|Select|Wrap|Line Numbers
  1. Total Number of sales (Count)
  2. Present- 3months
  3. 4-6 Months
  4. 7-12 Months
Slightly stumped on my approach. Can anyone offer advice as to how I can achieve this?


My current thought is something along these lines. Maybe I'm off track.
As my ControlSource
Expand|Select|Wrap|Line Numbers
  1. =Iff(DateDiff("m",[SoldDate],Date())<4,Count([SoldDate]))
I'm getting a parameter prompt for IIF.
Dec 21 '11 #1
Share this Question
Share on Google+
7 Replies

Expert Mod 15k+
P: 31,709
Assuming you have a date field somewhere then you would need to convert that into a number of months in the past (Use DateDiff() for this). From that point you can create a field using IIf() that returns one value if the value is less than four, another if less than seven, another if less than thirteen, etc. The result of this field can then be GROUPed by and aggregated across (Count() is an aggregate function) in the usual way.
Dec 21 '11 #2

P: 78
Thank you, NeoPa. I editied my previous post. (not sure if you had the chance to view my sample code) It looks like I'm on the right track but not quite there. Is it possible/wise to do this all with one expression as I'm trying (Still debugging) or seperate it somehow?

Should I create an Expression field in my query builder for the DateDiff and call that number somehow into my ControlSource then use the iff statement there to complete my calculation?
Dec 21 '11 #3

Expert Mod 15k+
P: 31,709
You haven't used IIf(). You've tried to use Iff() instead.

Your follow-on question or suggestion is not a valid answer by itself, but that's already clear from my answer in post #2. Why not try going forwards from that point.
Dec 21 '11 #4

P: 78
Thanks, NeoPa.

I have added 2 expression fields to my query. The first [SoldDateDiff] is to find the DateDiff()of [SoldDate]. The second [SoldDateDiffGrp] takes [SoldDateDiff] and assigns a letter based on the monthly conditions. It is a nested IIf statement.

Expand|Select|Wrap|Line Numbers
  1. SoldDateDiffGrp: IIf([SoldDateDiff]<4,"A",IIf([SoldDateDiff]<7,"B",IIf([SoldDateDiff]<13,"C","D")))
I'm at a loss of how to implement the GROUP BY clause to get the desired results. Can you provide insight?

Also, These calculations are part of a report summary. The details section of the report will contain all possible situations: Current-3, 6-4, 7-12 and 13+.
Dec 21 '11 #5

Expert Mod 15k+
P: 31,709
You would do a GROUP BY on the expression itself, so your GROUP BY clause might look like :
Expand|Select|Wrap|Line Numbers
  1. GROUP BY IIf([SoldDateDiff]<4,'A',IIf([SoldDateDiff]<7,'B',IIf([SoldDateDiff]<13,'C','D')))
Although, I'd use a different expression :
Expand|Select|Wrap|Line Numbers
  1. Switch([SoldDateDiff]<4,'A',[SoldDateDiff]<7,'B',[SoldDateDiff]<13,'C',True,'D')
Of course, if [SoldDateDiff] is just an expression then it will not be available to the GROUP BY clause, so you'll need to make it available by providing the data in a Subquery (otherwise the full expression would be required in place of each reference to [SoldDateDiff] - Messy).
Dec 22 '11 #6

P: 78
As a clairification, a subquery is simply another query that the main query calls data from?
Dec 23 '11 #7

Expert Mod 15k+
P: 31,709
A subquery is a query defined within the current one, that can provide data.

It is not limited to use within the FROM clause, but can be used as criteria and even as data in the SELECT clause itself. See Subqueries in SQL for more ideas.
Dec 24 '11 #8

Post your reply

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