interest calculations

[trawets]

Hi
I am looking for help if possible I have a data base i have been tasked
to do some calculations,
what do I have
a Table with quite a few field's most are irrelevant to what I need
the main ones I think

Asset, (Asset no)
Task, (task No XXXX1,XXXX2 ect approx 300)
Trade, ( hourly rate charge so just a No ie £30)
Time, (How long this task take)
Task cost
Frequency, (how often is the task carried out on each asset)
Freq No (How many times is task carried out across asset base)


these are the field's that I am working with which are all in the same
table, now some are blank because they are waiting the basic calc from
other field's ie task cost


Q1 can this be done inside the table or does it need to be through a
query ( trade* time = task cost)


Q2 when I have the figure for the task cost I need to multiply by the
2
frequencies, (can this be done in the table)

I am required to add all tasks together for the per annum
cost ( I think just a sum calc of the field)

but also to project a + - 10%
figure (I don't know how to do this)


Q3 When I have this figure, it is requested that I can then project
over the next say 10 years what the cost would be by adding an interest

calculation, well at this point, I thought you must be joking I can't
do that, well as it stands I can't, I simply just don't know how to do
this.
So I am asking , do you guys ?
Any help would be great or even just to tell them don't be daft,

So thanking you in anticipation
regards
Stewart

 

[Guest]

Stuart,

I believe all of this can be done. And I can help you with some of it.
First, I have been told that it is never a good idea to use calculations in
tables. Always use a query or report, usually both.

Second, Access is a relational database and as such almost always has more
than one table. Not sure but by your post I get the impression you have one
table? The following is some info I copied from another person's post (John
W. Vinson[MVP])with good info that you should review before you get too far.
This info tells about how the table structure should be. In the example the
db is storing law for a law student but the examples are solid. In the below
examples I added info that relates to you.

Tables and Columns.

Tables are Entities of importance and the fields or columns are Attributes
of the Entity.

1. Identify the Entities of importance (Tables). This might include
jurisdictions (Federal laws, fifty State laws, etc. etc.); Statutes; etc.
etc. In Stuart's case: Assets, Tasks, Customers (If it is a customer related
service), Price Rates (if there are different Pricing Structures for
different customers) and other Entities....

2. Identify the Attributes of each entity (Columns in the Table) - the
pieces of information that you need to know. In Stuart's case: The Assets
table needs a unique identifier or Column called something like AssetNumber.
(Notice Column names do not have spaces. This will help later in your
database when you use code). AssetNumber could be an auto number that only
gives out a number once usually used if assets are like chairs, cups or items
that many of the same asset processed and entered without a unique number to
identify it or it could be a property tag number as long as it uniquely
identifies the Asset. Other columns would be AssetDescription, AssetValue or
anything that is an Attribute of the Asset.

3. Create a Table for each kind of Entity, with a field/Column for each
Attribute. If you find yourself with repeated fields/Columns (1992 Statute,
1993 Statute, etc. etc.) Rethink - you need another table.

4. Identify the Relationships between the tables.


For some helpful hints, see

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Once you structure your and make your tables, you should make forms to enter
your data. Using forms to enter your data, as opposed to entering data
directly into your tables can prevent the user from entering erroneous
information. Erroneous information will not be returned in a query as queries
will only return what you request. Example if I want information returned on
Desks and I entered by mistake Desk in my table, the Desk record will not
return with the other Desks and your report will have errors. You can make
all kinds of Input Masks to ensure data in input in a certain and consistent
way. You can also use Validation Rules so if you input data that does not
meet the rule you db will not let you enter and save the record. Once you
make forms to enter your data you can use queries to make your calculations.
You can use these queries as the basis for your reports or use forms to
display the results of your queries. I recommend you start slowly and post as
many questions as you need to get help through each step mentioned above. If
you put in a good foundation, your db will be a stout tool to serve you for
years. hope this helps.


Dennis

 

[Guest]

Stuart,

You can download a sample db with example tables, forms and queries Here:
http://members.cox.net/jr1956/index.htm


Dennis