M
mikeycan
Hello!
First of all, thank you in advance for taking the time to help me! I am
trying to create queries that will show future revenues, for invoices being
amortized over defined periods, to be grouped by months for the first twelve
months (from a date selected), and the remainder in a “+12†column. This
report would be similar to an AR Aging that puts an invoice in to 30, 60, 90,
+120 buckets. The amounts are stored in my table “tblRA – Amortizationâ€, the
Report date (“the date selectedâ€) will be user defined and entered on a form.
As if this is not complicated enough, depending on the date selected certain
records will have to be ignored. For example, I have 5 invoices (#1, #2, #3,
etc) and invoice #3 is deferred (means no amounts in the “tblRA –
Amortization†table) which would just be the basic query results. However,
in February this invoice #3 has been released and amounts are now in “tblRA –
Amortization†table. If I re-run the query(s) as of January again, the
monthly amounts will be higher than when I previously generated it because
Invoice #3 has now has been released and incorporated into the “tblRA –
Amortization†table. In a separate table I maintain when invoices are
released, so I think the release date could be used to ignore invoices’
amortization that has been incorporated in the “tblRA – Amortization†table
(however, the invoice itself needs to be included in the Total Invoice
column). Also if I am running the queries for past dates, it needs to not
include future transactions that have not yet been invoiced as of the past
date. I have included some Excel exerpts to give an idea of the results I am
looking for and below that are the table and query names that have the
information I think I would need to create these queries.
As of Jan 1/31/07
Invoice No. Period to be Amortized (months) Total
Invoice Jan Feb Mar Apr May - Oct Nov Dec +12
1 36 12,000.00 333.33 333.33 333.33 333.33
333.33 333.33 8,000.00
2 12 12,000.00 1000 1000 1000 1000 1000 1,000.00
3 Deferred 12,000.00 12,000.00
4 12 12,000.00 1000 1000 1000 3,000.00
5 12 12,000.00 1000 1000 6,000.00
60,000 333.33 1,333.33 1,333.33 2,333.33 3,333.33 3,333.33
30,000.00
As of Feb 2/28/07
Invoice No. Period to be Amortized (months) Total
Invoice Feb Mar Apr May June – Nov Dec Jan +12
1 36 12,000.00 333.33 333.33 333.33 333.33
333.33 7,666.67
2 12 12,000.00 1000 1000 1000 1000 1000 1000 0
3 Released in Feb (12) 12,000.00 2000 1000 1000 1000 1000 0
4 12 12,000.00 1000 1000 1000 1000 2000
5 12 12,000.00 1000 1000 5000
60,000 3,333.33 2,333.33 3,333.33 3,333.33 4,333.33 3,000.00
14,666.67
tblRA - Amortization
FIELDS:
InvoiceNumber Text 50
RevenueMethod Text 50
AmortizationPeriod Date/Time 8
AmortInvoiceAmount Decimal 16
AmortCostAmount Decimal 16
tblRA - Order Deferrals –Releases
FIELDS:
ReleaseID Long Integer 4
InvoiceNumber Text 11
RevenueStream Text 50
RevenueMethod Text 50
ReleaseAmount Currency 8
ReleaseCarveOut Decimal 16
ReleaseCost Currency 8
ReleaseDate Date/Time 8
Comments Memo -
RevenueStartDate Date/Time 8
RevenueEndDate Date/Time 8
qryRA – Invoices
FIELDS:
InvoiceNum Text 50
Invoice Date Date/Time 8
First of all, thank you in advance for taking the time to help me! I am
trying to create queries that will show future revenues, for invoices being
amortized over defined periods, to be grouped by months for the first twelve
months (from a date selected), and the remainder in a “+12†column. This
report would be similar to an AR Aging that puts an invoice in to 30, 60, 90,
+120 buckets. The amounts are stored in my table “tblRA – Amortizationâ€, the
Report date (“the date selectedâ€) will be user defined and entered on a form.
As if this is not complicated enough, depending on the date selected certain
records will have to be ignored. For example, I have 5 invoices (#1, #2, #3,
etc) and invoice #3 is deferred (means no amounts in the “tblRA –
Amortization†table) which would just be the basic query results. However,
in February this invoice #3 has been released and amounts are now in “tblRA –
Amortization†table. If I re-run the query(s) as of January again, the
monthly amounts will be higher than when I previously generated it because
Invoice #3 has now has been released and incorporated into the “tblRA –
Amortization†table. In a separate table I maintain when invoices are
released, so I think the release date could be used to ignore invoices’
amortization that has been incorporated in the “tblRA – Amortization†table
(however, the invoice itself needs to be included in the Total Invoice
column). Also if I am running the queries for past dates, it needs to not
include future transactions that have not yet been invoiced as of the past
date. I have included some Excel exerpts to give an idea of the results I am
looking for and below that are the table and query names that have the
information I think I would need to create these queries.
As of Jan 1/31/07
Invoice No. Period to be Amortized (months) Total
Invoice Jan Feb Mar Apr May - Oct Nov Dec +12
1 36 12,000.00 333.33 333.33 333.33 333.33
333.33 333.33 8,000.00
2 12 12,000.00 1000 1000 1000 1000 1000 1,000.00
3 Deferred 12,000.00 12,000.00
4 12 12,000.00 1000 1000 1000 3,000.00
5 12 12,000.00 1000 1000 6,000.00
60,000 333.33 1,333.33 1,333.33 2,333.33 3,333.33 3,333.33
30,000.00
As of Feb 2/28/07
Invoice No. Period to be Amortized (months) Total
Invoice Feb Mar Apr May June – Nov Dec Jan +12
1 36 12,000.00 333.33 333.33 333.33 333.33
333.33 7,666.67
2 12 12,000.00 1000 1000 1000 1000 1000 1000 0
3 Released in Feb (12) 12,000.00 2000 1000 1000 1000 1000 0
4 12 12,000.00 1000 1000 1000 1000 2000
5 12 12,000.00 1000 1000 5000
60,000 3,333.33 2,333.33 3,333.33 3,333.33 4,333.33 3,000.00
14,666.67
tblRA - Amortization
FIELDS:
InvoiceNumber Text 50
RevenueMethod Text 50
AmortizationPeriod Date/Time 8
AmortInvoiceAmount Decimal 16
AmortCostAmount Decimal 16
tblRA - Order Deferrals –Releases
FIELDS:
ReleaseID Long Integer 4
InvoiceNumber Text 11
RevenueStream Text 50
RevenueMethod Text 50
ReleaseAmount Currency 8
ReleaseCarveOut Decimal 16
ReleaseCost Currency 8
ReleaseDate Date/Time 8
Comments Memo -
RevenueStartDate Date/Time 8
RevenueEndDate Date/Time 8
qryRA – Invoices
FIELDS:
InvoiceNum Text 50
Invoice Date Date/Time 8