My task is to maintain a small database that consists of a list of items and those items are organized into categories and subcategories (and sub-subcategories categories, etc.). I also have to prepare reports that consist of individual items, subtotals one for each if the subcategory and the grand total. What makes this task complicated is that values for some (but not all) of the subcategories is known. So, only the unknown totals have to be calculated, otherwise the given total value has to be used.
To illustrate the point, consider this oversimplified example. Imagine that you are building a database to track budget allocation. You know the total budget, and you split it between two projects: project A and project B. The total budget is $10, project A gets $5, project B gets $3. And $2 is unallocated, but this value is computed, it’s not given to you.

The main table in the database would look like this:
ID……..ParentID........Item.........Amount


1........‘Null’.............Gtotal.......10
2........1.................ProjectA.....5
3........2.................ProjectB.....3

I want the report to look like this:
........ProjectA.........5
........ProjectB.........3
TOTAL..................10

From the database, I can create the preliminary query
SELECT main.Item, main_1.Item, main_1.Amount, main.Amount
FROM main LEFT JOIN main AS main_1 ON main.ID = main_1.ParentID
WHERE (((main.ParentID)=0));
But it doesn’t get me to the report that I want