Moving Average Calculation Field

[mrchew]

I'm new to Filemaker though I have experience with MySQL. I have a very simple, one table database of stock quotes:

Date Symbol Open High Low Close Volume

-----------------------------------------------------------

10/31/08 ABC 10 12 9 11 9990303

10/31/08 DEF 20 22 18 21 1909933

.

.

.

I'm trying to create a calculated field that lists the trailing "moving" average for each stock based on the last 5, 10, 30 day:

Symbol 5dayAVG 10dayAVG 30dayAVG-------------------------------------------------------------------------

ABC 11 10 9

DEF 22 20 18

.

.

.

In MySQL this is straightforward, you'd have code that looks approximately like:

AVERAGE(SELECT close FROM quotes WHERE date is <=today...5 days ago AND symbol=this.symbol)

AVERAGE(SELECT close FROM quotes WHERE date is <=today...10 days ago AND symbol=this.symbol)

AVERAGE(SELECT close FROM quotes WHERE date is <=today...30 days ago AND symbol=this.symbol)

Is there a way to do this in Filemaker? I'm struggling trying to figure out a way to do 'Find' queries as part of calculations. Scripting seems capable of running multiple queries but what I really need is these moving averages as calculated fields in the same table as the plain quotes.

Any advice?

Thanks,

Chew

[comment]

In Filemaker, this is a two-step process: first, you need to define a self-join relationship for each of the SELECT statements. Then you can have calculation fields to aggregate the related records.

[mrchew]

Thanks, I'll investigate doing this.

Chew