Difficult Relationship- Sum sales by month

[goldcougar]

I am rewriting a database system that used a calculation field for each month and year to calculate the sales for a given company. So there is Jan04TotalSales...etc for each month.

I'd like to try to get out of that by using relationships a bit better (if possible). So, if you have these tables:

Sales - holds a record per sale, and contains a companyId

Company - holds a record per company

And then I want to be able to look at a record for the company, and view their cases summed by month (in regular browse mode).

Is there a better way than a calculation field per month and date?

[JamieT]

One way is to create a stored calculation field in the Company table that holds the month and year. Then create a relationship using that field and create a calculation field that sums all sales fields from that relationship.

I suppose you could even make it self relating with that field in every sales record showing the current total for the month of that record but that would slow things down a little.

[goldcougar]

What about creating a record per month/year, per company.

So a table called MonthlyCompanySales where it had fields for companyID, month, year, and the number of sales. If I have 4,000 companies, that would accumulate to 48,000 records per year. Would that be too much?

[Ugo DI LUCA]

Hi,

Be sure I'm perfectly cautious in advocating this here, but in this very particular case, I'd use repeaters to store the stats as this is the kind of data :

- that you can easily reference afterwards

- frozen in time

- fixed in length (12 months*N years)

- browsable

- managable as arrays for statistical displays

- quicker than any relationships if you need to perform calcs from them.