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
|
|||
|
|||
Cross Tab Report
I have data in an amortization table that list amounts to be recognized each
period for a given invoice. I would like a report that would show how much revenue by invoice per month for next twelve months based on when its run and any amounts over 12 months summed in a “Long Term” column. This report would be similar to an AR Aging that puts an invoice in to 30, 60, 90, +120 buckets, but I need the “buckets” to be dynamic. Invoice 4/1/07 5/1/07 6/1/07 7/1/07 8/1/07 9/1/07 10/1/07 11/01/07 12/01/01 1/1/08 2/01/08 3/1/08 LT The fields I have are Field Name Field Type Sample Data InvoiceNumber Text I23037 RevenueMethod Text Ratable Amortization Period Date/Time 4/1/07 Amortization Invoice Amount Number 7,258.58 Amortized Cost Amount Number 1,458.58 Can this be done? I am not a programmer, so I can only do very simple things with SQL or VB. Thanks in advance for your help. |
#2
|
|||
|
|||
Cross Tab Report
There is a sample solution for dynamic monthly crosstab reports at
http://www.tek-tips.com/faqs.cfm?fid=5466. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "mikeycan" wrote: I have data in an amortization table that list amounts to be recognized each period for a given invoice. I would like a report that would show how much revenue by invoice per month for next twelve months based on when its run and any amounts over 12 months summed in a “Long Term” column. This report would be similar to an AR Aging that puts an invoice in to 30, 60, 90, +120 buckets, but I need the “buckets” to be dynamic. Invoice 4/1/07 5/1/07 6/1/07 7/1/07 8/1/07 9/1/07 10/1/07 11/01/07 12/01/01 1/1/08 2/01/08 3/1/08 LT The fields I have are Field Name Field Type Sample Data InvoiceNumber Text I23037 RevenueMethod Text Ratable Amortization Period Date/Time 4/1/07 Amortization Invoice Amount Number 7,258.58 Amortized Cost Amount Number 1,458.58 Can this be done? I am not a programmer, so I can only do very simple things with SQL or VB. Thanks in advance for your help. |
#3
|
|||
|
|||
Cross Tab Report
Hello Duane,
Thanks for the Tip/Link. I understand how it works, but it does not provide the information I desire. The process offered looks at a date and goes backwards. I am looking for a method that looks forward. Again I would like to see starting from the current month what Amortization would be for the next 12 months and a sum any amortization over 12 months until it runs out. For example, I have an invoice for $150K and it is amortized for 15 months ($10k a month), starting in November 2007. When I run my report in December it should show me the following: “Mth1” to “Mth12” = $10K each “LongTerm” = $20K (months 14 & 15) Since the amortization started in November and I am running the report in December the November amount would be excluded. Is this possible? Thanks in advance for you help. "Duane Hookom" wrote: There is a sample solution for dynamic monthly crosstab reports at http://www.tek-tips.com/faqs.cfm?fid=5466. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "mikeycan" wrote: I have data in an amortization table that list amounts to be recognized each period for a given invoice. I would like a report that would show how much revenue by invoice per month for next twelve months based on when its run and any amounts over 12 months summed in a “Long Term” column. This report would be similar to an AR Aging that puts an invoice in to 30, 60, 90, +120 buckets, but I need the “buckets” to be dynamic. Invoice 4/1/07 5/1/07 6/1/07 7/1/07 8/1/07 9/1/07 10/1/07 11/01/07 12/01/01 1/1/08 2/01/08 3/1/08 LT The fields I have are Field Name Field Type Sample Data InvoiceNumber Text I23037 RevenueMethod Text Ratable Amortization Period Date/Time 4/1/07 Amortization Invoice Amount Number 7,258.58 Amortized Cost Amount Number 1,458.58 Can this be done? I am not a programmer, so I can only do very simple things with SQL or VB. Thanks in advance for your help. |
#4
|
|||
|
|||
Cross Tab Report
Do you actually have the values in a table that you would like to return in
your query or are these all calculated on the fly? You should be able to apply either future or past dates by simply changing the minus to plus or vice-versa. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "mikeycan" wrote: Hello Duane, Thanks for the Tip/Link. I understand how it works, but it does not provide the information I desire. The process offered looks at a date and goes backwards. I am looking for a method that looks forward. Again I would like to see starting from the current month what Amortization would be for the next 12 months and a sum any amortization over 12 months until it runs out. For example, I have an invoice for $150K and it is amortized for 15 months ($10k a month), starting in November 2007. When I run my report in December it should show me the following: “Mth1” to “Mth12” = $10K each “LongTerm” = $20K (months 14 & 15) Since the amortization started in November and I am running the report in December the November amount would be excluded. Is this possible? Thanks in advance for you help. "Duane Hookom" wrote: There is a sample solution for dynamic monthly crosstab reports at http://www.tek-tips.com/faqs.cfm?fid=5466. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "mikeycan" wrote: I have data in an amortization table that list amounts to be recognized each period for a given invoice. I would like a report that would show how much revenue by invoice per month for next twelve months based on when its run and any amounts over 12 months summed in a “Long Term” column. This report would be similar to an AR Aging that puts an invoice in to 30, 60, 90, +120 buckets, but I need the “buckets” to be dynamic. Invoice 4/1/07 5/1/07 6/1/07 7/1/07 8/1/07 9/1/07 10/1/07 11/01/07 12/01/01 1/1/08 2/01/08 3/1/08 LT The fields I have are Field Name Field Type Sample Data InvoiceNumber Text I23037 RevenueMethod Text Ratable Amortization Period Date/Time 4/1/07 Amortization Invoice Amount Number 7,258.58 Amortized Cost Amount Number 1,458.58 Can this be done? I am not a programmer, so I can only do very simple things with SQL or VB. Thanks in advance for your help. |
Thread Tools | |
Display Modes | |
|
|