Data Dependencies Function

From: Chris Nebinger (anonymous_at_discussions.microsoft.com)
Date: 04/14/04 

Date: Wed, 14 Apr 2004 08:35:07 -0700

Okay, lets make some assumptions:
The Task will always be an outline type number. 1.1 will
always be a child of 1, and always be a parent of 1.1.1

You can use the fact that 1.1 starts with 1, and 1.1.1
starts with 1.1.

I created a table, Table1, with 2 fields, Task and Done.
My data looks like:

Task Done
1 Yes
1.1 No
1.1.2 Yes
1.1.1 No
1.2 No
2 No
2.1 No
2.1.1 No
2.1.2 Yes

Using this query:

SELECT a.*
FROM Table1 AS a, [SELECT Table1.Task
FROM Table1
WHERE Table1.Done=True]. AS b
WHERE Left([b].[task],Len([a].[task]))=[a].[task];

I get:

Task Done
1 Yes
1 Yes
1.1 No
1.1.2 Yes
2 No
2.1 No
2.1.2 Yes

Okay, lets look to see how it works. In order to use just
one query, I used a Select statement in the FROM clause of
the main query. This is the same as creating another
query and using it.

SELECT Table1.Task
FROM Table1
WHERE Table1.Done=True

Simple enough. Pull all the tasks that are done.

I'm going to call that query B.

SELECT a.*
FROM Table1 AS a

Okay, show everything from Table1. We'll call this one B.

The catch is in the Where Clause.

Left([b].[task],Len([a].[task]))=[a].[task]

Again, b is the tasks that are done, a is all the tasks.

if the left characters of B.Task is equal to the A task,
then it is a parent.

If you need more help, let me know.

Chris Nebinger

>-----Original Message-----
>I need some help with an Access query or user-defined
function. Hopefully
>this is the proper message board for this issue.
>
>Before I get into Access, I'd like to provide some basic
background about
>the data source and how it is collected.
>
>
>BACKGROUND INFO - EXCEL (what's done before data is
important into Access):
>Data is collected in a spread*** on 3 levels
(e.g. "1", "1.1", "1.1.1",
>etc.). The numbers (the field is actually TEXT data
type) represent tasks
>that various members of an organization complete in their
day-to-routine.
>
>The spread*** contains a column where the members
select either "Yes" or
>"No". Members may indicate e.g. a "Yes" for
task "1.1.1". That means that
>they complete this task. Although implied, members do
not have to select
>"Yes" for the parent tasks "1" & "1.1". However,
logically speaking, it
>makes sense that if "1.1.1" is completed, there must be
some work effort on
>the parent task level(s).
>
>
>
>BACKGROUND INFO - ACCESS (here lies the challenge):
>Again, in the example of "1.1.1", the "logical parent
tasks" are not checked
>in the spread*** and, therefore, not pulled by the
queries in Access.
>
>
>What I need to achieve in Access:
>- Create a function or query that will "pull" all parent
tasks (pending on
>the "child level" or "grandchild level").
>
>
>Example of CURRENT Query results (where TASKNO is the
field header):
>
>
>TASKNO DONE
>1 YES
>1.1.2 YES
>2.1 YES
>2.1.2 YES
>3.1.3 YES
>
>
>
>If done properly, the same query/function should produce
the following
>results:
>
>TASKNO DONE COMMENT (why this record is/should
be pulled)
>1 YES record was selected by member
>1.1 record must be pulled because
of "1.1.2"
>1.1.2 YES record was selected by member
>2 record must be pulled because
of "2.1" & "2.1.2"
>2.1 YES record was selected by member
>2.1.2 YES record was selected by member
>3 record must be pulled because
of "3.1" & "3.1.3"
>3.1 record must be pulled because
of "3.1.3"
>3.1.3 YES record was selected by member
>
>
>I truly would appreciate feedback from anyone who could
provide me some
>advice as to how I could tackle this.
>
>Thanks!!!
>
>Tom
>
>
>
>
>
>.
>