hi,
i have two queries:
one is called EXPENSES_MONTHLY
SELECT [DATE], [ACCT_NUM], SUM([AMOUNT]) AS TOT_EXPENSE
FROM EXPENSES
GROUP BY [EXPENSES].[DATE], [EXPENSES].[ACCT_NUM];
second is REVENUE_MONTHLY
SELECT Sum(([REVENUE].[CAD]+[B/g_VALUE]+[B/S_VALUE]+[LOGS_SCALING]+[B/S_SCALING])) AS TOT_REV, [REVENUE].[ACCT_NUM], [REVENUE].[DATE]
FROM REVENUE
GROUP BY [REVENUE].[DATE], [REVENUE].[ACCT_NUM];
and table MONTHLY_INTEREST
MONTH INTEREST
30/11/05 7.75%
31/12/05 8.00%
31/01/06 8.25%
28/02/06 8.25%
31/03/06 8.50%
30/04/06 8.75%
31/05/06 9.00%
and i need to build a query which would calculate monthly balance for each ACCT_NUM (expenses-revenue), calculate running balance and if this running balance is negative then calculate monthly interest for it (interest*running_balance/365*number_of_days_in_month) so the result would look like this (it is presently excel spreadsheet where are typed manually from on access reports; i want to do it in automatically access):
ACCT_NUM MONTH REVENUE EXPENSE MONTHLY RUN_BAL INTEREST RATE
5 Nov-05 0.00 10,178.50 -10,178.50 -10,178.50 -64.84 7.75%
5 Dec-05 0.00 3,682.48 -3,682.48 -13,860.98 -94.18 8.00%
5 Jan-06 0.00 446,507.33 -446,507.33 -460,368.31 -3,225.73 8.25%
5 Feb-06 364,538.14 496,866.62 -132,328.48 -592,696.79 -3,751.04 8.25%
5 Mar-06 634,659.33 88,441.23 546,218.10 -46,478.69 -335.54 8.50%
5 Apr-06 155,668.32 43,440.20 112,228.12 65,749.43 0.00 8.75%
5 May-06 27,509.40 146,626.82 -119,117.42 -53,367.99 -407.94 9.00%
5 Jun-06 98,594.17 1,491.80 97,102.37 43,734.38 0.00 9.00%
5 Jul-06 0.00 828.63 -828.63 42,905.75 0.00 9.00%
5 Aug-06 4,707.30 -685.33 5,392.63 48,298.38 0.00 9.00%
5 Sep-06 3,795.00 0.00 3,795.00 52,093.38 0.00 9.00%
5 Oct-06 0.00 0.00 0.00 52,093.38 0.00 9.00%
any suggestion please?
i have two queries:
one is called EXPENSES_MONTHLY
SELECT [DATE], [ACCT_NUM], SUM([AMOUNT]) AS TOT_EXPENSE
FROM EXPENSES
GROUP BY [EXPENSES].[DATE], [EXPENSES].[ACCT_NUM];
second is REVENUE_MONTHLY
SELECT Sum(([REVENUE].[CAD]+[B/g_VALUE]+[B/S_VALUE]+[LOGS_SCALING]+[B/S_SCALING])) AS TOT_REV, [REVENUE].[ACCT_NUM], [REVENUE].[DATE]
FROM REVENUE
GROUP BY [REVENUE].[DATE], [REVENUE].[ACCT_NUM];
and table MONTHLY_INTEREST
MONTH INTEREST
30/11/05 7.75%
31/12/05 8.00%
31/01/06 8.25%
28/02/06 8.25%
31/03/06 8.50%
30/04/06 8.75%
31/05/06 9.00%
and i need to build a query which would calculate monthly balance for each ACCT_NUM (expenses-revenue), calculate running balance and if this running balance is negative then calculate monthly interest for it (interest*running_balance/365*number_of_days_in_month) so the result would look like this (it is presently excel spreadsheet where are typed manually from on access reports; i want to do it in automatically access):
ACCT_NUM MONTH REVENUE EXPENSE MONTHLY RUN_BAL INTEREST RATE
5 Nov-05 0.00 10,178.50 -10,178.50 -10,178.50 -64.84 7.75%
5 Dec-05 0.00 3,682.48 -3,682.48 -13,860.98 -94.18 8.00%
5 Jan-06 0.00 446,507.33 -446,507.33 -460,368.31 -3,225.73 8.25%
5 Feb-06 364,538.14 496,866.62 -132,328.48 -592,696.79 -3,751.04 8.25%
5 Mar-06 634,659.33 88,441.23 546,218.10 -46,478.69 -335.54 8.50%
5 Apr-06 155,668.32 43,440.20 112,228.12 65,749.43 0.00 8.75%
5 May-06 27,509.40 146,626.82 -119,117.42 -53,367.99 -407.94 9.00%
5 Jun-06 98,594.17 1,491.80 97,102.37 43,734.38 0.00 9.00%
5 Jul-06 0.00 828.63 -828.63 42,905.75 0.00 9.00%
5 Aug-06 4,707.30 -685.33 5,392.63 48,298.38 0.00 9.00%
5 Sep-06 3,795.00 0.00 3,795.00 52,093.38 0.00 9.00%
5 Oct-06 0.00 0.00 0.00 52,093.38 0.00 9.00%
any suggestion please?
