Parent and child relationships conundrum!

[chuckles1066]

I've posted this in both Excel and Access forums as, although it's initially an Access question, it may be possible to dump the data into Excel and solve it that way.

I'll try and explain as best I can!

I have inherited a database with numerous tables but the two I'm interested in are named "worklogs" and "relationships".

The "worklogs" table lists hours recorded against projects - the key field is named "project id".

The "relationships" table lists relationships between parent projects and children and children of children. The key fields are named "parent id" (which links to "project id" in the "worklogs" table) and "child id".

To illustrate what I'm trying to achieve:

In "worklogs" table I might have

project id 1 = 3 hours
project id 2 = 2 hours
project id 3 = 1 hour
project id 4 = 7 hours

In "relationships" table I might have

project 2 = child of project 1
project 3 = child of project 2

What I need to report is a roll-up of all hours to parent level.

In the example above, the report would show

project id 1 = 6 hours (because projects 2 & 3 are related to it)
project id 4 = 7 hours (because it has no relationships)

Can any of you wise ones point me in the right direction as to what sort of query(ies) I need to construct?

TIA.

 

[VoG]

Link to your thread in the Access forum http://www.mrexcel.com/forum/showthread.php?t=593501

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).