If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Range
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 |
#2
|
|||
|
|||
Date Range
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 "Tsidjpt" wrote: 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 |
#3
|
|||
|
|||
Date Range
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. "Tsidjpt" wrote: 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 |
#4
|
|||
|
|||
Date Range
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. |
Thread Tools | |
Display Modes | |
|
|