calculated fields

[Dermot]

I want to create a database that uses calculated fields.

The results would be printed in a report

The calculations are basic as follows:
Calculation 1.........(X/Y) *Z
X is a variable and each client has many
Y is the sum of a number of items a client has this varies also.
Z is a calculated value.....the (A-B)
A is the sum of a range of values
B is a variable value

Question 1
What tables do I need to create to use calculated fields.

Question 2
Do I need to use separate table for all my numeric data?

Question 3
I read that I have to use a query to work with calculated fields
How would I do this using my above calculations?

Question 4
Would anyone be able to provide me with a simple database example to
demonstrate how I should approch my above calculations.

Many thanks in advance for any help possible.

Dermot

 

[Jeff Boyce]

Dermot

This sounds quite a bit like what a spreadsheet does. How have you come to
the conclusion that you need a database?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dermot]

Hi Jeff
Thanks for the reply.
I am already using a spreadsheet to do it.
I am using it as a practical example to understand how I could do it as a
database using calculated fields....because I would like to know how I would
design the appropriate tables / queries to incorporate the numeric data and
calculated fields.

There are features in a database that in some situations I would rather
develop a self contained database rather that a spreadsheet. Spreadsheet are
by far best for calculations and databses for storage and retreaval of data,
I realise this.......

Please persevere with me and advise anyway Jeff as questioned.....and add
any suggestions of your own that would be worth me considering, I would very
much appreciate any help to grasp this.

I also know I could possibly use a database and export (correct choice of
term?) the data to a spreadsheet.
Thanks
Dermot

 

[Pat Hartman \(MVP\)]

To properly design a database application, you'll need to remove your
spreadsheet hat and replace it with a new relational database hat. How the
calculations are performed depends on how the data is stored and where you
need to use the result not the other way around. You need to identify
entities and attributes and how they relate to each other. Once the data is
all in its place, we can discuss how to bring it back together again to use
it.

Tell us more about what this spreadsheet contains and what purpose it serves
in business terms.

 

[Dermot]

Hi Pat,
To keep it simple it's a prorata distribution of currency.

I recognised that the underlying tables are critical, but am not sure how I
deal with the numeric data.

I have created a clients table (tblClients) using an autonumber primary key
Using the client ID as a foreign key (Long Integer) in all the other tables
to relate them.
Each client can have many creditors and owe £xxxxx (tblCreditors).

One Creditor may be owed £YYYY

Each client can have many incomes and expenditures tblIncExp
Calcualtion1
Inc- Exp=Surplus

Calculation 3
Availble £ = Surplus - Fee ( variable amountcould be consided fixed say £30
or £50)
I am not sure what table I would put the fees in?

Calculation 4
Prorata Div = (£ YYYY Creditor Amount Owed / £XXXXXTotal Owed) * Available £

Question
Are the above table correct?
There are other calculations I could incorporate for reports which I would
create based on calculated field queries.

I think I understand the theory, but it's putting it to practice to do the
calculations.

I hope this is an ample explanation.

Any help would be appreciated.....even a link to a basic sample mdb that I
could study...to assist my learning.

Thanks
Demot

 

[Dermot]

Hi Pat,
Can you provide me with some feedback with respect to my last posting?
Thanks
Dermot

 

[John W. Vinson]

You don't use tables for calculated fields... period.

Your Table should store the "real life" basic data - ideally, no
calculated fields at all.

Given the vagueness of your post, I have no idea. You would create a
Query and type an expression in a vacant Field cell to do the
calculation. If the calculation involves summing or averaging across
multiple records, you would click the Greek Sigma icon (looks like a
sideways M) and set the Totals row to the appropriate expression for
your particular calculation.

Probably not one that would do YOUR specific calculations, no.

John W. Vinson [MVP]

 

[Dermot]

Hi John
I thought I explained myself reasonably well in the follow up example.
The initial posting I admit was not so clear.

I would appreciate if you could help mke a little further.....
All I want to grasp is how to correctly store the numerica values in the
database and how to correctly use that numeric data in calculated fields.

I realise you won't have any databse for my specific simple example
above....but would you have any simple example mdb that demonsrtates how to
correctly use calcualted field correctly......as a foundation from which I
can build my understanding.......If I can see a functional sample......it may
help me cross the hurdle I'm at........I have many Access database books
......I constantly refer to them but......in my opinion they don't explain the
basics of the subject very well.....hence my posting.....I am not being
lazy.....just need a little direction.

Any further assistance would be very much appreciated,
Thanks
Dermot

 

[John W. Vinson]

Hi John
I thought I explained myself reasonably well in the follow up example.
The initial posting I admit was not so clear.

Dermot, I think I got into this thread in the middle. All I saw as an
example was:


A is a sum of... what?
X is a variable and each client has many... of what? Records in a
table? Fields in a table?

You understand your data. You can see your data. We cannot.

John W. Vinson [MVP]

 

[Dermot]

Thanks for the reply John
It's a prorata distribution of currency. say (£1000/£5000)*£275 =£55

Here is another explanation..........I hope this is clearer.

I recognised that the underlying tables are critical, but am not sure how I
deal with the numeric data.

I have created a clients table (tblClients) using an autonumber primary key
Using the client ID as a foreign key (Long Integer) in all the other tables
to relate them.
Each client can have many creditors and owe £xxxxx (tblCreditors).

One Creditor may be owed £YYYY

Each client can have many incomes and expenditures tblIncExp
Calcualtion1
Inc- Exp=Surplus

Calculation 3
Availble £ = Surplus - Fee I am not sure what table I would put the fees in?
Note: The Fee could be a variable amount considered fixed say £30
or £50)

Calculation 4
Prorata Div = (£ YYYY Creditor Amount Owed / £XXXXXTotal Owed) * Available £

Question
Are the above table correct?
There are other calculations I could incorporate for reports which I would
create based on calculated field queries.

I think I understand the theory, but it's putting it to practice to do the
calculations.

Any help would be appreciated.....even a link to a basic sample mdb that I
could study...to assist my learning.

Thanks
Demot

 

[Dermot]

Hi Jamie,
Thanks for your two posting and link to calculated columns.
I was not aware the data type could change in this manner....thanks.

Can you help me a little further...........
I appreciate that in an ideal world all the data should be stored separately
an brought together using views. (Queries in Access). ...correct me if I am
wrong.

I am using Access 2003.
Question 1
Can you advise me briefly.....Developed correctly.....would I create a
separate data table for all my numeric data........(Choosing my data type
carefully).....each field in the table containing only one single numeric
value?

Question 2
Using a query.....How do I then concatenate the data to provide calculated
results?

Question3
How would I then store the calculated results.....to be used in either
Printed Reports or Further Calculations?

Lastly
I now think I have been using the term "Calculated Field" wrongly.
I think my question is more about pulling the data together to produce
"Calculated Results".......am I correct to observe a difference between the
two terms Field and Results?

Thanks, very much appreciated, Dermot

 

[Dermot]

I found a good basic link here which help me out too...

http://www.fontstuff.com/index.html

If anyone else as some good links on the subject of calculated
fields...please post it.

Thanks all help here
Dermot