Date Range

  • Thread starter Thread starter Tsidjpt
  • Start date Start date
T

Tsidjpt

I'm trying to do the following for a quarterly report. I can get each month's
total indivitually, but don't know how to make it show up in the format
listed below. Any help will be greatly appreciated, also, I don't know
anything about visual basic and thank you for your time and help.

Month 1 Month 1 Total Previous 11 Months Total
Month 2 Month 2 Total Previous 11 Months Total
Month 3 Month 3 Total Previous 11 Months Total
 
Hi Tsidjpt,

Could you do something with grouping on month, and on the month group, show
the footer where you have these totals. You could hide the detail section.

You'd have 3 columns coming from your query; Current Month (Jan, Feb Mar
....), Month Total, and Prev 11 Month Total. Sum the detail up in the Month
group footer and hide the detail (detail height = 0)

I think that should work.

Brad
 
Create a table name CountNumber with field CountNUM containing numbers from 0
(zero) through your maximum spread of months. Use your table and field
names.

SELECT Format([YourDate],"mmm yyyy") AS Accounting_Month,
Sum(IIf(Format([YourDate],"yyyymm")=Format(DateAdd("m",-[CountNUM],Date()),"yyyymm"),[Balance],0)) AS Month_Total
FROM AcctDetails, CountNumber
GROUP BY Format([YourDate],"yyyymm"), Format([YourDate],"mmm yyyy")
ORDER BY Format([YourDate],"yyyymm") DESC;

You can add an overall criteria if you desire.
 
I've not been able to work on this for a month and I appreciate the responses
I've reveived but am still a bit confused. So, I'll try again with some
examles and hopefully a little better explanation. Listed below is the format
and the headings that I need to use.

MONTH USAGE Previous 11 MONTHS USEAGE
April 2009 10 132.619 (= to
5/1/2008 to 3/31/2009)
May 2009 12.417 127.504 (= to 6/1/2008 to
4/31/2009)
June 2009 9.872 131.72 (= to 7/1/2008
to 5/31/2009).

Even though this says April through June it could be Jan to March, etc. of
any given year based upon a date range. My query will list the "Usage" totals
by month, but I don't know how to add the previous 11 to each month listed.
Also, if basic is used I can type that but not sure how to incorporate it.
Please feel free to ask for clarification on any part of this example. I
appreciate your time and patience in answering this.
 
Back
Top