Im looking for advise on working summing together all the time spent on a particular job, however also including child jobs.
So currently I have a table with 3 coloums:-
Filled with some test data:-
JobNo ParentJobNo TimeSpent
11 10 10
12 10 15
13 10 5
14 10 10
15 14 15
16 14 20
17 13 5
20 19 10
Currently we have the query:-
SELECT Jobs.JobNo, Jobs.TimeSpent
WHERE (((Jobs.ParentJobNo) Is Null));
However what I would like it to do is sum all the child jobs, and child jobs in that, so the correct answer would be:-
My current idea is to create a function in PHP to look at each row, and look for child jobs and then children of that returning the number each time, but I would prefer to do this in the query itself.
I did think of creating a function in MySQL, but as the function would have to have use recursion, which is set to quite low on my shared server.
Im going to be using MySQL in the end, but using Access to quickly show my issue.
I hope someone can cope up with a better idea.