how to do it in access instead in excel?

[jozino01]

hi,
i calculate monthly interest for project in excel; all my input data is taken from ms access and manually entered into spreadsheet, which looks like:
MONTH REVENUE EXPENSE MONTHLY RUNNING BAL. INTEREST DAYS RATE

Jun-06 0.00 78,350.79 -78,350.79 -78,350.79 -579.58 30 9.00%
Jul-06 0.00 137.63 -137.63 -78,488.42 -599.95 31 9.00%
Aug-06 0.00 44,454.48 -44,454.48 -122,942.90 -939.76 31 9.00%
Sep-06 0.00 73,708.35 -73,708.35 -196,651.25 -1,454.68 30 9.00%
Oct-06 0.00 94,889.33 -94,889.33 -291,540.58 -2,228.49 31 9.00%
Nov-06 0.00 106,862.60 -106,862.60 -398,403.18 -2,947.09 30 9.00%
Dec-06 0.00 254,010.00 -254,010.00 -652,413.18 -4,986.94 31 9.00%
Jan-07 0.00 401,056.79 -401,056.79 -1,053,469.97 -8,052.55 31 9.00%
Feb-07 1,370,159.95 760,342.24 609,817.71 -443,652.26 -3,063.02 28 9.00%
Mar-07 1,871,628.85 1,348,311.72 523,317.13 79,664.87 0.00 31 9.00%

data in access (query recordset):

interest:
Idate interest
June 2006 9.00%
July 2006 9.00%
August 2006 9.00%
September 2006 9.00%
October 2006 9.00%
November 2006 9.00%
December 2006 9.00%
January 2007 9.00%
February 2007 9.00%
March 2007 9.00%

revenue:
rdate PROJECT tot_revenue
March 2007 07 $1,871,628.85
June 2006 07 $0.00
February 2007 07 $1,370,159.95
April 2007 07 $1,069,734.88

expenses:
edate PROJECT TOT_EXPENSE
September 2006 07 $74,207.59
October 2006 07 $97,126.82
November 2006 07 $109,818.40
March 2007 07 $1,348,311.72
June 2006 07 $78,930.37
July 2006 07 $735.48
January 2007 07 $409,118.34
February 2007 07 $763,338.55
December 2006 07 $259,005.94
August 2006 07 $45,418.63
April 2007 07 $300,781.31

is there any way (one or more queries), how to do it directly in access?
my issues/problems are:
- there are not revenue/expenses for each month
- make monthly running totals
- set automatically proper number of days for each month

thanks for your help

 

[randy700]

Are you saying you can't create a query from your three tables?

Randy

 

[jozino01]

well, yes. that's what i am saying :-(
i tried, but i couldn't get it working; i wasn't able to manage month for zero/null revenue/expenses.

i am not proud of it, but i work as an accountant...

 

[lespaul]

you need a table that contains ALL the months to JOIN to in order to report 0 or null, but in your example above you have records with a 0 value (revenue), those should show up in your query if those records really exist. Additionally, once you have a table with all the months, you'll need to modify the JOIN from INNER to LEFT or RIGHT. See the article linked below for more on JOINS.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[jozino01]

yes, in this particular example (above) i manually entered one "dummy" month in order to get proper results for different, year-end report.
normally, if they are no record/transactions for 0 (zero) revenue/expenses.

some projects look like:
MONTH REVENUE EXPENSE MONTHLY RUNNING BAL. INTEREST

Jul-04 0.00 1,132.50 -1,132.50 -1,132.50 0.00
Aug-04 0.00 0.00 0.00 -1,132.50 -6.97
Sep-04 0.00 0.00 0.00 -1,132.50 -6.75
Oct-04 0.00 0.00 0.00 -1,132.50 -6.97
Nov-04 0.00 0.00 0.00 -1,132.50 -6.75
Dec-04 0.00 9,105.59 -9,105.59 -10,238.09 -63.04

 

[lespaul]

Then you'll need someway to identify what's missing, that's what a table of the months will provide for you.

 

[jozino01]

ok, it tooks me a while, but it looks i got it working now (in four queries - maybe it can be done in less than four):

1) interest1
SELECT [interest_monthly].[Idate], [interest_monthly].[interest], [expenses_monthly1].[TOT_EXPENSE], *
FROM interest_monthly LEFT JOIN expenses_monthly1 ON [interest_monthly].[Idate]=[expenses_monthly1].[edate];

2) interest2
SELECT *
FROM interest1 LEFT JOIN revenue_monthly1 ON [interest1].[Idate]=[revenue_monthly1].[rdate];

3) interest3
SELECT [interest2].[anum], [interest2].[Idate], IIf([interest2].[tot_revenue] Is Null,0,[interest2].[tot_revenue]) AS mrevenue, IIf([interest2].[TOT_EXPENSE] Is Null,0,[interest2].[tot_expense]) AS mexpense, (mrevenue-mexpense) AS mbalance, [interest2].[interest], [interest2].[days]
FROM interest2;

4) interest_by_project
SELECT q3.Idate, q3.mrevenue, q3.mexpense, q3.mbalance, (select sum([interest3].[mbalance]) as rbalance from interest3 where ((([interest3].[anum])<=[q3].[anum])) AS rbalance, IIf(rbalance*q3.interest/365*q3.days>0,0,round(rbalance*q3.interest/365*q3.days,2)) AS idue, q3.interest, q3.days
FROM interest3 AS q3;

resultset
Idate mrevenue mexpense mbalance rbalance idue interest days
Mar 2005 $0.00 $3,464.97 -$3,464.97 -$3,464.97 -21.34 7.25% 31
Apr 2005 $0.00 $15,274.00 -$15,274.00 -$18,738.97 -111.66 7.25% 30
May 2005 $0.00 $24,092.00 -$24,092.00 -$42,830.97 -263.73 7.25% 31
Jun 2005 $0.00 $0.00 $0.00 -$42,830.97 -255.23 7.25% 30
Jul 2005 $0.00 $0.00 $0.00 -$42,830.97 -263.73 7.25% 31
Aug 2005 $0.00 $2,530.00 -$2,530.00 -$45,360.97 -279.31 7.25% 31
Sep 2005 $0.00 $0.00 $0.00 -$45,360.97 -279.62 7.50% 30
Oct 2005 $0.00 $0.00 $0.00 -$45,360.97 -298.57 7.75% 31
Nov 2005 $0.00 $0.00 $0.00 -$45,360.97 -288.94 7.75% 30
Dec 2005 $0.00 $0.00 $0.00 -$45,360.97 -308.21 8.00% 31

thanks everybody for your input!

 

[groleau]

For what it's worth, if you have clients that like Excel,
you can do whatever you want to do in Access (or another DB
and then have Excel automagically fetch the data with an
SQL Query.

The data will be in rows and columns just like you'd see it in access, but you can put the upper left corner where you
want on your spreadsheet and refer to any cell in a formula
in exactly the same way you would if it were "taken from
ms access and manually entered"

--
Wes Groleau

http://www.Parkview.com/

 

[jozino01]

well, the main point (in my case/exercise) is that everything is in access; only interest was calculated using excel (manualy entering access data) so i wanted to do eveything in access.

 

[groleau]

Understood. The Excel link is handy sometimes, though.
When a non-technical coworker needs some data, you can develop a nice Access report for them, which they
"appreciate." Or, you give them the almost raw data
in Excel and even though it's not as pretty as an Access
report, they are "thrilled," because they know Excel,
and they can do all their favorite Excel tricks on the
data--and never actually mess with the database itself.

--
Wes Groleau

http://www.Parkview.com/

 

[jozino01]

sure, that's true.

in this case i am also an user too
and i try automatize my work whenever it's possible. i rather do a small program than manually do something that can be done automatically/by computer.