Hi Bret,

In your case, I think that you are best served with a join table and get rid of the parent task fields in the main task table. Consider this: if you have a join table with ParentTask and ChildTask fields, then you can imply the Child-Grandchild-Greatgrandchild relationship.

Now, your SQL statements are going to be a bitch in this scenario, and I'm not sure how you're going to pull it off in Access.


>>No, you're not overthinking it. What I may end up is the join table from Hell with a foreign key consisting of the 3 overriding categories, the class, and the function. The join table would hold the attributes common to only that combination.
>>
>>>>category, a subcategory, a sub-sub category, then classed 4 ways.
>>>>attributes common to each level and, finally, attributes that will only be >needed with certain combinations of categories, subs, subs-subs and classes.
>>>
>>>Are the sub- and sub-sub- categories exclusive or common across parent categories? The answer will greatly affect how you model the data.
>
>I may have a similar situation, and one I don't get to completely control. I have a table of tasks identified by task number, with several tasks in a Job Order. We used to list them and make totals, grouping on job order. These fields are in a time and attendance system which we can't change. Now they want to have a "parent task" and "child tasks" and group on those. I added a "ParentTask" field which contains the task number of a task's parent and is usually blank. So far it has only been used to avoid double-counting certain task values. Now they want reports that group on ParentTask. Further, there can be grandchild tasks and maybe even great-grandchild tasks, with an unknown number of levels, and a requirement to group reports on this unknown number of levels.
>
>A suggestion here includes a field that identifies group level. I don't think I want to oblige users to keep track of that and enter the value for my convenience. I would still need validation code to ensure that the group level was correct. That code could be used better when building cursors and reports.
>
>I haven't developed anything yet. I expect that I will abandon SQL and use a recursive procedure to build cursors with the necessary group fields, adding more fields for more grouping levels. The report will have groups added in runtime. I am obliged to do all this in Access 97, which has object-based reports that should make runtime modification easier (no hacking report tables).
>
>To make it clearer, my data looks like this:
>
>

>JobOrder     TaskNum     ParentTask
>4323X        10100
>4323X        10110       10100
>4323X        10111       10110
>4323X        10112       10110
>4323X        10120       10100
>4323X        10130       10100
>4329X        1
>4329X        2
>4329X        3
>

>I think I would scan the data to determine the number of grouping levels, and create cursors that look like this:
>

>JobOrder     TaskNum     Parent1     Parent2
>4323X        10100       10100       10100
>4323X        10110       10110       10100
>4323X        10111       10110       10100
>4323X        10112       10110       10100
>4323X        10120       10120       10100
>4323X        10130       10130       10100
>4329X        1           1           1
>4329X        2           2           2
>4329X        3           3           3
>

>If the code found a fourth level of grouping, it would add a Parent3 field, and so on. The whole system would account for the fact that some Job Orders don't get into all this parent task nonsense, and might be using the same reports.