Jump to content

Difficult Relationship- Sum sales by month


This topic is 6623 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites


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.

Link to comment
Share on other sites

This topic is 6623 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.