Solved

Sum of duration in parent & child folders in database

Posted on 2004-09-03
7
389 Views
Last Modified: 2006-11-17
Hi all,

I have a MS Access database with 2 tables.
Table1 is FileProperties and Table2 is TreeStructure.

FileProperties has
FileID,
FileName,
ParentValue,
Duration and
......

TreeStructure has
NodeID,
NodeName,
IsFolder and
ParentID.

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.
http://www.geocities.com/coltan81/tree.gif

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.      
0
Comment
Question by:coltan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11974669
Hmm....first of all, a comment on your table structure. Shouldn't you be using the FileID rather than a nodename to link the two tables? Files in different folders might have the same name - or are you using the full path of the file?

One way to do this is to iterate through the Nodes collection and check for any node where the FullPath property includes the target folder's FullPath property. If it does, that node is a child of your target folder. You can then retrieve the record that matches that node and then add the filesize to your running value.
0
 
LVL 9

Expert Comment

by:solution46
ID: 11975416
Something else you could try (this will only work in code in the interface) is to build the Summing process into the function where you build the tree. Last time I wrote code to populate a tree-view, iended up with a function that called itself for each child id; thereby recursing through each branch. As you draw each node, make a note of the duration and write it back to the parent node. This isn't trivial to do, but if you're build a tree anyway, it wont add too much compexity.

Presumably you are using the front end to enter files, etc. If this is the case, each time you draw the tree (or each time you close it, or whatever) you could write the total duration of each folder back to the table. I know this seems 'wrong way round' but I can't think of an easier approach.

Incidentally, if you need a sample of tree view code I can probably dig some up from somewhere. You could also try getting hold of a book called 'VB COntrols in a Nutshell' - it's an excellent reference source for the controls of the various common control libraries.

s46.
0
 

Author Comment

by:coltan
ID: 11988936
Hi all,

Thanks for trying to help. I have found a solution for my problem.

I added a 'Duration' column in my TreeStructure table. So, whenever I need to insert a record to the database, I insert the file duration to the TreeStructure table at the same time. Besides that, I update the parent folder and 'grandparent' folders' duration field. Hence, when I need the sum of duration of files and folders in a big folder, I just read the 'Duration' column from the TreeStructure database.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11989054
No objections.
0
 
LVL 9

Expert Comment

by:solution46
ID: 11990886
Me neither.
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 12030018
PAQd, 250 points refunded.

GhostMod
Community Support Moderator
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question