TeamDesk Knowledge Base & Support

   Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
  
dealing with running totals tables
I am guessing this is a basic thing that somehow I missed in all my programming training. I am embarrassed to ask...

I have this problem in multiple databases. In one example we are tracking vacation time.
We have a table that records when a person earns time or uses time.

The only way to find a persons accurate total is to calculate all entries from the beginning of time.

IF we were to use a spreadsheet to track this, we would have a sheet for 2020 that has all the entries. We then have a sheet for 2021 the first entry would be 2020 carry over that would be the starting amount.

The clients like to see it this way. They wish too see last years, or last quarter.

I have this same problem tracking inventories.


Reports are confusing. If we do a report on say this year and allow the report to show totals, it will only show the total for the year. But that would not be correct because the person may have had a total from last year.

There must be an elegant way to handle this.
Accurately show totals at any time period

Plus deal with the table getting so large that it slows everything down.

I would like to know how others deal with this!

Thanks!



ID
1489
Category
TeamDesk
Author

cooper collier
Date Created
12/8/2021 11:18:19 AM
Date Updated
12/9/2021 8:15:39 AM
Comments
basenine 12/8/2021 2:24:31 PM
Have you considered using
Between([Date Field]) <ask user> <ask user>
basenine 12/8/2021 2:25:10 PM
….In the filter of the view
Pierre 12/8/2021 4:16:26 PM
I did the following
I have a workspace called 'Reporting'.
In this table, you can see three tables: 'YEARS', 'QUARTERS' and 'MONTHS'.
Indexes for YEARS is the year (ex 2021), resp for QUARTERS is YEAR-TX (ex 2021-T4), resp for MONTHS is YEAR-mm (ex 2021-08)
I have many tables linked to these three tables using based on the date of the event.
In each of the reporting table I put the same informations, summarizing the relevant fields.
Pierre 12/8/2021 4:25:15 PM
or is your question mainly about the carryover calculation ?
cooper collier  12/9/2021 8:15:39 AM
That is an interesting concept, I never thought to populate more than one table simultaneous.

But that would not solve the issue with the database getting so large.
Consider a table to keep inventory, there could be thousands of items each one having daily changes.
You need a rolling total for each item.
This would require the database to calculate the total of every record.
In a couple years the database is going to be slow.

I considered something like making a flag in the record called Archive.
Then at a certain date maybe the 1st of the year. Make a new record for the total of the previous year
Then mark all the previous records as Archived.
Then only calculate the records that are not archived.

This would allow a person to view all the transactions.
It would also make the views much easier for the user.
But I am not sure it would resolve performance issues?


Feedback
Back to Search Results