[Function] Avg

[Mat1024]

[Function Series Based] Average only for columns?

Hello,

I am designing a form and would like to implement a text box with an average.
I would like it to calculate the average of the value of different fields. But the average function seems to work only with one expression .

Example:

01/2000|02/2000|03/2000|
F1| 10| 32| 18| = (10+32+18)/3 = 20 this is the number I want
F2| 60|
= (60+10)/2=35 this is the number I do not want

How do you do this?

I tried to input this:
= Avg (Between [01/2000] And [03/2000]) But it does not work...

Thanks!

 

[Mile-O]

Your table seems designed as if it is a spreadsheet. You should fix this to a proper database design and then calculating the average wouldn't be a problem.

Can you explain the structure of this table?

 

[Mat1024]

You are right, the database looks like a spreadsheet.
(cf. the 2 screenshots)
Do you have any idea on how I could improve it?
Thankyou very much!

 

[Mile-O]

You have a HUGE repeating group spanning months of data - a common feature of databases built incorrectly with spreadsheet thinking. You should create your fields and leave them as is - there shouldn't be a need to continuously add new fields in a database. You would eventually run out of fields - there's a 255 limit.

A database should grow down with its records so your month fields should not be fields at all - they shoudl be records.

You need a table for funds, which you have. And you need a second table for monthly data.

The I have no idea what all the monthly data is. I'll just call it amounts.

So, let's make table amounts.

tblAmounts
AmountID - Autonumber (primary key)
DateOfAmount - Date/Time
Amount - Number
FundID - Number (foreign key)

The other table - called tblFunds - should be:

tblFunds
FundID - Autonumber (primary key)
FundName - Text
Strategy - Text
Universe - Text
Zone - Text
RedemptionPeriod - Text
NoticePeriod - Number
ReceiptDays - Number
Lockup - Number
Penalty - Number

I don't know what all these fields are. Perhaps they can be broken out to more lookups. Zone and RedemptionPeriod certainly can.

Now, you can create a relationship between FundID in both tables, Enforce Referential Integrity, and Cascade Delete Records.

This structure is how you should have it. By using a form you can enter your data into the table via a form/subform combination.

In the tblFunds you keep all your information about your fund. The tblAmounts keeps only info on your amounts. Each record contains a foreign key to the funds table so that you know to which fund each amount applies.

Once you have this you will find it much easier to get the average as the amounts are all within one field - you just supply the criteria on the fund.

 

[Mile-O]

And here's a quick example...

 

[Mat1024]

Wow! thanks a lot for your advice. I'll try this and keep you informed.
Thanks again!

 

[Mat1024]

Funds are our "point of entry" for any further data input. There is one record per fund.

...the thing is: we have a good thousand of funds with an average of 132 monthly data per fund on a huge, excel sheet-like table.

I wonder how I am going to compile all of this, because for example, for only the DateOfAmount field, there would be like 132.000 entries Does your strategy still apply?

Thankyou again for your help!

(clicking on each link on DVD and Games to support the forum)