455,078 Members | 1,280 Online
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 Total Number of sales (Count) Present- 3months 4-6 Months 7-12 Months Slightly stumped on my approach. Can anyone offer advice as to how I can achieve this? Thanks. My current thought is something along these lines. Maybe I'm off track. As my ControlSource Expand|Select|Wrap|Line Numbers =Iff(DateDiff("m",[SoldDate],Date())<4,Count([SoldDate])) I'm getting a parameter prompt for IIF. Dec 21 '11 #1
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 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 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 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