I have a MS Access database with 2 tables.
Table1 is FileProperties and Table2 is TreeStructure.
The TreeStructure table is used to generate a tree where records with a 'IsFolder = Yes' will be a folder while records with a 'IsFolder = No' are nodes that will fall under its parent(folder).
When I insert a record into FileProperties, 'FileID' is auto-generated.
As I insert the a new record into FileProperties, I need to insert a record into TreeStructure where NodeID will be auto-generated,
The 'NodeName' will be same as the 'FileName' in FileProperties, IsFolder is No and ParentID will be a Node in TreeStructure which is a folder.
Each file in FileProperties has a duration in seconds.
Now, my question.
Let's say I have a tree like this, pls see picture.
and I would like to get the sum of the duration of files and folders given a NodeID from TreeStructure which is a folder.
I know I can get the sum of duration for 1 folder using
SELECT SUM(Duration) FROM FilePropertiesDB WHERE ParentValueDB = the folder's NodeID
My problem is I need to get the sum(Duration) of the files under the given folder and the sum(Duration) of all the files under its child folders. There can be many child folders and child folders can have child folders... This goes on..
How do I do it like in Windows Explorer where when I open the properties of a folder, I can get the total size of the files inside the folder itself and its child folders (in my case duration)?
If this is not possible, please suggest alternative.
Thank you very much.