Record Explosion

M

mikeycan

Hello. I am a moderate user of Access and I am trying to build a database to
track our “amortized†revenues. I have a table that contains invoice header
information and other table with invoice detail information including fields
that indicate the begin and end dates of the period in which revenue should
be amortized. I would like to create a set of ???? (queries and/or marcos)
that would create records in a third table. For example, I have an invoice
for $12,000 and RevenueStartDate is 12/16/07 and the RevenueEndDate is
12/15/08. The process should create 12 records in the new table that
allocates the amount based on the number of days in each month. See chart
below. Is this possible for a moderate user?
Number of Days Month Amount
15 Dec-07 493.15
31 Jan-08 1,019.18
28 Feb-08 920.55
31 Mar-08 1,019.18
30 Apr-08 986.30
31 May-08 1,019.18
30 Jun-08 986.30
31 Jul-08 1,019.18
31 Aug-08 1,019.18
30 Sep-08 986.30
31 Oct-08 1,019.18
30 Nov-08 986.30
16 Dec-08 526.03
365 12,000.00
 
K

KARL DEWEY

December 2008 would only have 15 days.
Use these two queries substituting your table and field names.
MikeyCan_1 ---
SELECT Invoice_Detail.Invoice, Invoice_Detail.RevenueStartDate,
Invoice_Detail.RevenueEndDate,
DateAdd("m",[CountNUM],([RevenueStartDate])-Day([RevenueStartDate])+1) AS
Pay_Month, DateDiff("d",[RevenueStartDate],[RevenueEndDate]) AS Total_Days,
[Invoice]/DateDiff("d",[RevenueStartDate],[RevenueEndDate]) AS Per_Day
FROM Invoice_Detail, CountNumber
WHERE
(((DateAdd("m",[CountNUM],([RevenueStartDate])-Day([RevenueStartDate])+1))<=[RevenueEndDate]));


SELECT
DateDiff("d",IIf([RevenueStartDate]>[Pay_Month],[RevenueStartDate]+1,[Pay_Month]),IIf([RevenueEndDate]<DateAdd("m",1,[Pay_Month]),[RevenueEndDate]+1,DateAdd("m",1,[Pay_Month])))
AS Number_Of_Days, Format([Pay_Month],"mmm-yy") AS Payment_Month,
DateDiff("d",IIf([RevenueStartDate]>[Pay_Month],[RevenueStartDate]+1,[Pay_Month]),IIf([RevenueEndDate]<DateAdd("m",1,[Pay_Month]),[RevenueEndDate]+1,DateAdd("m",1,[Pay_Month])))*[Per_Day] AS Payment
FROM MikeyCan_1;
 
K

KARL DEWEY

P.S.
Table CountNumber has field CountNUM containing 0 (zero) through your
maximum schedule.
 
M

mikeycan

Thank you very much for the response! I am not very familiar with SQL coding,
so I attempted to make sense of your suggestion, but I ran into some trouble.
Somewhere in the suggestion, I did not make the correct transition to my
table/field names.

My tables are as follows:
1. [tblRA - Invoices by Revenue Category]
a. fields are [Invoice Num], [RevenueStartDate], and [RevenueEndDate]
2. [tblSYS – CountNumber]
a. Field is [CountNum] (with values from 0 to 48)

Is this how I should substitute them?

qryTest – Advice 1
SELECT tblRA - Invoices by Revenue Category.Invoice, tblRA - Invoices by
Revenue Category.RevenueStartDate,
tblRA - Invoices by Revenue Category.RevenueEndDate,
DateAdd("m",[CountNUM],([RevenueStartDate])-Day([RevenueStartDate])+1) AS
Pay_Month, DateDiff("d",[RevenueStartDate],[RevenueEndDate]) AS Total_Days,
[Invoice]/DateDiff("d",[RevenueStartDate],[RevenueEndDate]) AS Per_Day
FROM tblRA - Invoices by Revenue Category, CountNumber
WHERE
(((DateAdd("m",[CountNUM],([RevenueStartDate])-Day([RevenueStartDate])+1))<=[RevenueEndDate]));

qryTest – Advice 2
SELECT
DateDiff("d",IIf([RevenueStartDate]>[Pay_Month],[RevenueStartDate]+1,[Pay_Month]),IIf([RevenueEndDate]<DateAdd("m",1,[Pay_Month]),[RevenueEndDate]+1,DateAdd("m",1,[Pay_Month])))
AS Number_Of_Days, Format([Pay_Month],"mmm-yy") AS Payment_Month,
DateDiff("d",IIf([RevenueStartDate]>[Pay_Month],[RevenueStartDate]+1,[Pay_Month]),IIf([RevenueEndDate]<DateAdd("m",1,[Pay_Month]),[RevenueEndDate]+1,DateAdd("m",1,[Pay_Month])))*[Per_Day] AS Payment
FROM tblSYS – CountNumber;


KARL DEWEY said:
December 2008 would only have 15 days.
Use these two queries substituting your table and field names.
MikeyCan_1 ---
SELECT Invoice_Detail.Invoice, Invoice_Detail.RevenueStartDate,
Invoice_Detail.RevenueEndDate,
DateAdd("m",[CountNUM],([RevenueStartDate])-Day([RevenueStartDate])+1) AS
Pay_Month, DateDiff("d",[RevenueStartDate],[RevenueEndDate]) AS Total_Days,
[Invoice]/DateDiff("d",[RevenueStartDate],[RevenueEndDate]) AS Per_Day
FROM Invoice_Detail, CountNumber
WHERE
(((DateAdd("m",[CountNUM],([RevenueStartDate])-Day([RevenueStartDate])+1))<=[RevenueEndDate]));


SELECT
DateDiff("d",IIf([RevenueStartDate]>[Pay_Month],[RevenueStartDate]+1,[Pay_Month]),IIf([RevenueEndDate]<DateAdd("m",1,[Pay_Month]),[RevenueEndDate]+1,DateAdd("m",1,[Pay_Month])))
AS Number_Of_Days, Format([Pay_Month],"mmm-yy") AS Payment_Month,
DateDiff("d",IIf([RevenueStartDate]>[Pay_Month],[RevenueStartDate]+1,[Pay_Month]),IIf([RevenueEndDate]<DateAdd("m",1,[Pay_Month]),[RevenueEndDate]+1,DateAdd("m",1,[Pay_Month])))*[Per_Day] AS Payment
FROM MikeyCan_1;

--
KARL DEWEY
Build a little - Test a little


mikeycan said:
Hello. I am a moderate user of Access and I am trying to build a database to
track our “amortized†revenues. I have a table that contains invoice header
information and other table with invoice detail information including fields
that indicate the begin and end dates of the period in which revenue should
be amortized. I would like to create a set of ???? (queries and/or marcos)
that would create records in a third table. For example, I have an invoice
for $12,000 and RevenueStartDate is 12/16/07 and the RevenueEndDate is
12/15/08. The process should create 12 records in the new table that
allocates the amount based on the number of days in each month. See chart
below. Is this possible for a moderate user?
Number of Days Month Amount
15 Dec-07 493.15
31 Jan-08 1,019.18
28 Feb-08 920.55
31 Mar-08 1,019.18
30 Apr-08 986.30
31 May-08 1,019.18
30 Jun-08 986.30
31 Jul-08 1,019.18
31 Aug-08 1,019.18
30 Sep-08 986.30
31 Oct-08 1,019.18
30 Nov-08 986.30
16 Dec-08 526.03
365 12,000.00
Click to expand...
Click to expand...
 
K

KARL DEWEY

You did not enclose the table names in brackets that contain spaces. I am
not sure of your table names as at the top you indicate - 2. [tblSYS –
CountNumber] but in the query you have CountNumber. So I dropped the tblRA
and tblSYS.
I assumed the first query was named [qryTest – Advice 1] so I used that in
the second query FROM section instead of what you had ([tblSYS –
CountNumber]).

qryTest – Advice 1
SELECT [Invoices by Revenue Category].Invoice, [Invoices by Revenue
Category].RevenueStartDate, [Invoices by Revenue Category].RevenueEndDate,
DateAdd("m",[CountNUM],([RevenueStartDate])-Day([RevenueStartDate])+1) AS
Pay_Month, DateDiff("d",[RevenueStartDate],[RevenueEndDate]) AS Total_Days,
[Invoice]/DateDiff("d",[RevenueStartDate],[RevenueEndDate]) AS Per_Day
FROM [Invoices by Revenue Category], CountNumber
WHERE
(((DateAdd("m",[CountNUM],([RevenueStartDate])-Day([RevenueStartDate])+1))<=[RevenueEndDate]));

qryTest – Advice 2
SELECT
DateDiff("d",IIf([RevenueStartDate]>[Pay_Month],[RevenueStartDate]+1,[Pay_Month]),IIf([RevenueEndDate]<DateAdd("m",1,[Pay_Month]),[RevenueEndDate]
+1,DateAdd("m",1,[Pay_Month]))) AS Number_Of_Days,
Format([Pay_Month],"mmm-yy") AS Payment_Month,
DateDiff("d",IIf([RevenueStartDate]>[Pay_Month],[RevenueStartDate]+1,[Pay_Month]),IIf([RevenueEndDate]<DateAdd("m",1,[Pay_Month]),[RevenueEndDate]+1,DateAdd("m",1,[Pay_Month])))*[Per_Day] AS Payment
FROM [qryTest – Advice 1];

--
KARL DEWEY
Build a little - Test a little


mikeycan said:
Thank you very much for the response! I am not very familiar with SQL coding,
so I attempted to make sense of your suggestion, but I ran into some trouble.
Somewhere in the suggestion, I did not make the correct transition to my
table/field names.

My tables are as follows:
1. [tblRA - Invoices by Revenue Category]
a. fields are [Invoice Num], [RevenueStartDate], and [RevenueEndDate]
2. [tblSYS – CountNumber]
a. Field is [CountNum] (with values from 0 to 48)

Is this how I should substitute them?

qryTest – Advice 1
SELECT tblRA - Invoices by Revenue Category.Invoice, tblRA - Invoices by
Revenue Category.RevenueStartDate,
tblRA - Invoices by Revenue Category.RevenueEndDate,
DateAdd("m",[CountNUM],([RevenueStartDate])-Day([RevenueStartDate])+1) AS
Pay_Month, DateDiff("d",[RevenueStartDate],[RevenueEndDate]) AS Total_Days,
[Invoice]/DateDiff("d",[RevenueStartDate],[RevenueEndDate]) AS Per_Day
FROM tblRA - Invoices by Revenue Category, CountNumber
WHERE
(((DateAdd("m",[CountNUM],([RevenueStartDate])-Day([RevenueStartDate])+1))<=[RevenueEndDate]));

qryTest – Advice 2
SELECT
DateDiff("d",IIf([RevenueStartDate]>[Pay_Month],[RevenueStartDate]+1,[Pay_Month]),IIf([RevenueEndDate]<DateAdd("m",1,[Pay_Month]),[RevenueEndDate]+1,DateAdd("m",1,[Pay_Month])))
AS Number_Of_Days, Format([Pay_Month],"mmm-yy") AS Payment_Month,
DateDiff("d",IIf([RevenueStartDate]>[Pay_Month],[RevenueStartDate]+1,[Pay_Month]),IIf([RevenueEndDate]<DateAdd("m",1,[Pay_Month]),[RevenueEndDate]+1,DateAdd("m",1,[Pay_Month])))*[Per_Day] AS Payment
FROM tblSYS – CountNumber;


KARL DEWEY said:
December 2008 would only have 15 days.
Use these two queries substituting your table and field names.
MikeyCan_1 ---
SELECT Invoice_Detail.Invoice, Invoice_Detail.RevenueStartDate,
Invoice_Detail.RevenueEndDate,
DateAdd("m",[CountNUM],([RevenueStartDate])-Day([RevenueStartDate])+1) AS
Pay_Month, DateDiff("d",[RevenueStartDate],[RevenueEndDate]) AS Total_Days,
[Invoice]/DateDiff("d",[RevenueStartDate],[RevenueEndDate]) AS Per_Day
FROM Invoice_Detail, CountNumber
WHERE
(((DateAdd("m",[CountNUM],([RevenueStartDate])-Day([RevenueStartDate])+1))<=[RevenueEndDate]));


SELECT
DateDiff("d",IIf([RevenueStartDate]>[Pay_Month],[RevenueStartDate]+1,[Pay_Month]),IIf([RevenueEndDate]<DateAdd("m",1,[Pay_Month]),[RevenueEndDate]+1,DateAdd("m",1,[Pay_Month])))
AS Number_Of_Days, Format([Pay_Month],"mmm-yy") AS Payment_Month,
DateDiff("d",IIf([RevenueStartDate]>[Pay_Month],[RevenueStartDate]+1,[Pay_Month]),IIf([RevenueEndDate]<DateAdd("m",1,[Pay_Month]),[RevenueEndDate]+1,DateAdd("m",1,[Pay_Month])))*[Per_Day] AS Payment
FROM MikeyCan_1;

--
KARL DEWEY
Build a little - Test a little


mikeycan said:
Hello. I am a moderate user of Access and I am trying to build a database to
track our “amortized†revenues. I have a table that contains invoice header
information and other table with invoice detail information including fields
that indicate the begin and end dates of the period in which revenue should
be amortized. I would like to create a set of ???? (queries and/or marcos)
that would create records in a third table. For example, I have an invoice
for $12,000 and RevenueStartDate is 12/16/07 and the RevenueEndDate is
12/15/08. The process should create 12 records in the new table that
allocates the amount based on the number of days in each month. See chart
below. Is this possible for a moderate user?
Number of Days Month Amount
15 Dec-07 493.15
31 Jan-08 1,019.18
28 Feb-08 920.55
31 Mar-08 1,019.18
30 Apr-08 986.30
31 May-08 1,019.18
30 Jun-08 986.30
31 Jul-08 1,019.18
31 Aug-08 1,019.18
30 Sep-08 986.30
31 Oct-08 1,019.18
30 Nov-08 986.30
16 Dec-08 526.03
365 12,000.00
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Repeat the value from the previous record 6
Suming Up Time over the course of days 1
Query/Expression Help Required! 2
When building a chart Dates are been included that are not in thedate range 1
Find within Date Range 4
creating report in access 1
I am trying to create a formula 2
Excel less than greater than formula 1

Top