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 spreadsheet 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 spreadsheet 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 spreadsheet 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

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 spreadsheet 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 spreadsheet 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 spreadsheet 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
>
>
>
>
>
>.
>

Chris:

Thank you for your reply... reading your approach seems very promising;
however, I was not able to replicate your instructions and results.

In between the ***s, I placed some questions... I hope you'll be able to
get me on right track again.

Here's what I done (step by step)...


STEP 1. TABLE DESIGN
- Created a table called "Table1"
- Added 2 fields: "Task" [Data type = Text]; "Done" [Data type = Text]
- Appended the values below (as you suggested)

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


**** Question 1 ****

Based on the values "Yes" & " No", should the "Done" field be a "Yes/No"
field (boolean) or shall I leave it "Text"?

If your example requires a boolean value (True or False), is there a way
around to use
a text data type instead? If not, I move on to the next step.

********************




STEP 2. QUERY DESIGN
- Open a query in design view and switched to SQL MODE
- Copied/pasted the SQL below and saved query as "Query1"
- Executed the query below...

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];


**** Feedback & Question 2 ****

When I executed Query1, I got the same number of records (count = 9) -- just
like the
record count in Table1.

Your record count (in your instructions under the "I get:" section),
however, changes
from 9 to 7 records. Also, in your example I see the "1 Yes" twice.

What am I doing wrong? Should I expect to see 7 records vs. 9 records?


********************


**** Question 3 ****

What is the source for the following SELECT statement? Is it "Table1" or
is it
"Query1"?

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

********************


Again, I am really glad about your replying to this thread and I hope that I
will be
able to get this to work (I'm sure the mistakes are on my part).

I would truly appreciate any additional help from you.


THANKS AGAIN!!!

Tom






"Chris Nebinger" <(E-Mail Removed)> wrote in message
news:1c76f01c42236$10797470$(E-Mail Removed)...
> 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 spreadsheet 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 spreadsheet 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 spreadsheet 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
> >
> >
> >
> >
> >
> >.
> >

Comments inside of ~~~~~~~~~~~~~~~~~'s


>-----Original Message-----
>Chris:
>
>Thank you for your reply... reading your approach seems
very promising;
>however, I was not able to replicate your instructions
and results.
>
>In between the ***s, I placed some questions... I hope
you'll be able to
>get me on right track again.
>
>Here's what I done (step by step)...
>
>
>STEP 1. TABLE DESIGN
>- Created a table called "Table1"
>- Added 2 fields: "Task" [Data type = Text]; "Done" [Data
type = Text]
>- Appended the values below (as you suggested)
>
>Task Done
>1 Yes
>1.1 No
>1.1.1 Yes
>1.1.2 No
>1.2 No
>2 No
>2.1 No
>2.1.1 No
>2.1.2 Yes
>
>
>**** Question 1 ****
>
>Based on the values "Yes" & " No", should the "Done"
field be a "Yes/No"
>field (boolean) or shall I leave it "Text"?
>
>If your example requires a boolean value (True or False),
is there a way
>around to use
>a text data type instead? If not, I move on to the next
step.
>
>********************
~~~~~~~~~~~~~~~~~~~~~
It really doesnt matter. If the field is only going to
hold a yes or no, then I would leave it as boolean. If
you change it to text, then the SQL should look like:
SELECT a.*
FROM Table1 AS a, [SELECT Table1.Task
FROM Table1
WHERE Table1.Done="YES"]. AS b
WHERE Left([b].[task],Len([a].[task]))=[a].[task];
~~~~~~~~~~~~~~~~~~~~~
>
>
>
>STEP 2. QUERY DESIGN
>- Open a query in design view and switched to SQL MODE
>- Copied/pasted the SQL below and saved query as "Query1"
>- Executed the query below...
>
>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];
>
>
>**** Feedback & Question 2 ****
>
>When I executed Query1, I got the same number of records
(count = 9) -- just
>like the
>record count in Table1.
>
>Your record count (in your instructions under the "I
get:" section),
>however, changes
>from 9 to 7 records. Also, in your example I see the "1
Yes" twice.
>
>What am I doing wrong? Should I expect to see 7 records
vs. 9 records?
>
>
>********************

~~~~~~~~~~~~~~~~~~~~~
It seems that if item 1 is also selected as Done, it shows
up twice.

So, the SQL code should finally be:

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



>
>**** Question 3 ****
>
>What is the source for the following SELECT statement?
Is it "Table1" or
>is it
>"Query1"?
>
>SELECT Table1.Task
>FROM Table1
>WHERE Table1.Done=True
>
>********************

~~~~~~~~~~~~~~~~~~~~~
It is from a table, Table1.

My SQL code is the same as if you paste the SQL code into
a new query and save it. The 2nd query would join that
query on the original table. This way was easier to put
into a post, but a bit harder to understand.
~~~~~~~~~~~~~~~~~~~~~

>
>Again, I am really glad about your replying to this
thread and I hope that I
>will be
>able to get this to work (I'm sure the mistakes are on my
part).
>
>I would truly appreciate any additional help from you.

~~~~~~~~~~~~~~~~~~~~~~~~~~
You are welcome, and we'll get this figured out.
~~~~~~~~~~~~~~~~~~~~~~~~~~

>
>THANKS AGAIN!!!
>
>Tom
>
>
>

Chris,

thanks for the prompt reply...

I still don't get this work... hopefully you won't mind helping me a bit
more.


This is what I have now:

Table1:
=====

- "Done" = Text data type

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




Query1:
=====

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


Query 1 pulls the following records:

Query1 Task Done
1 Yes
1.1.2 Yes
2.1.2 Yes



Now, I'm lost... I opened a new query and used Table1 & Query 1 as the
source. I tried all 3 version... Inner Join, Left Outer & Right Outer
Join.

Here are the results

Inner Join on "Task"

Query2 Table1.Task Table1.Done Query1.Task Query1.Done
1 Yes 1 Yes
1.1.2 Yes 1.1.2 Yes
2.1.2 Yes 2.1.2 Yes



Left Outer Join on "Task"

Query2 Table1.Task Table1.Done Query1.Task Query1.Done
1 Yes 1 Yes
1.1 No


1.1.2 Yes 1.1.2 Yes
1.1.1 No


1.2 No


2 No


2.1 No


2.1.1 No


2.1.2 Yes 2.1.2 Yes



Right Outer Join on "Task"
Query2 Table1.Task Table1.Done Query1.Task Query1.Done
1 Yes 1 Yes
1.1.2 Yes 1.1.2 Yes
2.1.2 Yes 2.1.2 Yes




Was is supposed to join Table1 with Query1? If yes, I'm not sure how to
interpret the results.

"Yes" means that "member has selected the task" while...
"No" means that there is a "logical parent" (but the parent was no selected
as "Yes").


PLEASE HELP!

Tom









--
Thanks,
Tom


"Chris Nebinger" <(E-Mail Removed)> wrote in message
news:1c76f01c42236$10797470$(E-Mail Removed)...
> 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 spreadsheet 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 spreadsheet 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 spreadsheet 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
> >
> >
> >
> >
> >
> >.
> >

Chris...

oops, please see my reply to my own thread.

--
Thanks,
Tom


"Chris Nebinger" <(E-Mail Removed)> wrote in message
news:1ca1101c42269$90ccae20$(E-Mail Removed)...
> Comments inside of ~~~~~~~~~~~~~~~~~'s
>
>
> >-----Original Message-----
> >Chris:
> >
> >Thank you for your reply... reading your approach seems
> very promising;
> >however, I was not able to replicate your instructions
> and results.
> >
> >In between the ***s, I placed some questions... I hope
> you'll be able to
> >get me on right track again.
> >
> >Here's what I done (step by step)...
> >
> >
> >STEP 1. TABLE DESIGN
> >- Created a table called "Table1"
> >- Added 2 fields: "Task" [Data type = Text]; "Done" [Data
> type = Text]
> >- Appended the values below (as you suggested)
> >
> >Task Done
> >1 Yes
> >1.1 No
> >1.1.1 Yes
> >1.1.2 No
> >1.2 No
> >2 No
> >2.1 No
> >2.1.1 No
> >2.1.2 Yes
> >
> >
> >**** Question 1 ****
> >
> >Based on the values "Yes" & " No", should the "Done"
> field be a "Yes/No"
> >field (boolean) or shall I leave it "Text"?
> >
> >If your example requires a boolean value (True or False),
> is there a way
> >around to use
> >a text data type instead? If not, I move on to the next
> step.
> >
> >********************
> ~~~~~~~~~~~~~~~~~~~~~
> It really doesnt matter. If the field is only going to
> hold a yes or no, then I would leave it as boolean. If
> you change it to text, then the SQL should look like:
> SELECT a.*
> FROM Table1 AS a, [SELECT Table1.Task
> FROM Table1
> WHERE Table1.Done="YES"]. AS b
> WHERE Left([b].[task],Len([a].[task]))=[a].[task];
> ~~~~~~~~~~~~~~~~~~~~~
> >
> >
> >
> >STEP 2. QUERY DESIGN
> >- Open a query in design view and switched to SQL MODE
> >- Copied/pasted the SQL below and saved query as "Query1"
> >- Executed the query below...
> >
> >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];
> >
> >
> >**** Feedback & Question 2 ****
> >
> >When I executed Query1, I got the same number of records
> (count = 9) -- just
> >like the
> >record count in Table1.
> >
> >Your record count (in your instructions under the "I
> get:" section),
> >however, changes
> >from 9 to 7 records. Also, in your example I see the "1
> Yes" twice.
> >
> >What am I doing wrong? Should I expect to see 7 records
> vs. 9 records?
> >
> >
> >********************
>
> ~~~~~~~~~~~~~~~~~~~~~
> It seems that if item 1 is also selected as Done, it shows
> up twice.
>
> So, the SQL code should finally be:
>
> SELECT DISTINCT a.*
> FROM Table1 AS a, [SELECT Table1.Task
> FROM Table1
> WHERE Table1.Done="YES"]. AS b
> WHERE Left([b].[task],Len([a].[task]))=[a].[task];
>
>
>
> >
> >**** Question 3 ****
> >
> >What is the source for the following SELECT statement?
> Is it "Table1" or
> >is it
> >"Query1"?
> >
> >SELECT Table1.Task
> >FROM Table1
> >WHERE Table1.Done=True
> >
> >********************
>
> ~~~~~~~~~~~~~~~~~~~~~
> It is from a table, Table1.
>
> My SQL code is the same as if you paste the SQL code into
> a new query and save it. The 2nd query would join that
> query on the original table. This way was easier to put
> into a post, but a bit harder to understand.
> ~~~~~~~~~~~~~~~~~~~~~
>
> >
> >Again, I am really glad about your replying to this
> thread and I hope that I
> >will be
> >able to get this to work (I'm sure the mistakes are on my
> part).
> >
> >I would truly appreciate any additional help from you.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~
> You are welcome, and we'll get this figured out.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> >
> >THANKS AGAIN!!!
> >
> >Tom
> >
> >
> >
>

Tom,

I tried sending you an email..
If you didn't get it, send me an email at my
(E-Mail Removed)


Chris Nebinger


>-----Original Message-----
>Chris...
>
>oops, please see my reply to my own thread.
>
>--
>Thanks,
>Tom
>
>
>"Chris Nebinger" <(E-Mail Removed)>
wrote in message
>news:1ca1101c42269$90ccae20$(E-Mail Removed)...
>> Comments inside of ~~~~~~~~~~~~~~~~~'s
>>
>>
>> >-----Original Message-----
>> >Chris:
>> >
>> >Thank you for your reply... reading your approach seems
>> very promising;
>> >however, I was not able to replicate your instructions
>> and results.
>> >
>> >In between the ***s, I placed some questions... I hope
>> you'll be able to
>> >get me on right track again.
>> >
>> >Here's what I done (step by step)...
>> >
>> >
>> >STEP 1. TABLE DESIGN
>> >- Created a table called "Table1"
>> >- Added 2 fields: "Task" [Data type = Text]; "Done"
[Data
>> type = Text]
>> >- Appended the values below (as you suggested)
>> >
>> >Task Done
>> >1 Yes
>> >1.1 No
>> >1.1.1 Yes
>> >1.1.2 No
>> >1.2 No
>> >2 No
>> >2.1 No
>> >2.1.1 No
>> >2.1.2 Yes
>> >
>> >
>> >**** Question 1 ****
>> >
>> >Based on the values "Yes" & " No", should the "Done"
>> field be a "Yes/No"
>> >field (boolean) or shall I leave it "Text"?
>> >
>> >If your example requires a boolean value (True or
False),
>> is there a way
>> >around to use
>> >a text data type instead? If not, I move on to the
next
>> step.
>> >
>> >********************
>> ~~~~~~~~~~~~~~~~~~~~~
>> It really doesnt matter. If the field is only going to
>> hold a yes or no, then I would leave it as boolean. If
>> you change it to text, then the SQL should look like:
>> SELECT a.*
>> FROM Table1 AS a, [SELECT Table1.Task
>> FROM Table1
>> WHERE Table1.Done="YES"]. AS b
>> WHERE Left([b].[task],Len([a].[task]))=[a].[task];
>> ~~~~~~~~~~~~~~~~~~~~~
>> >
>> >
>> >
>> >STEP 2. QUERY DESIGN
>> >- Open a query in design view and switched to SQL MODE
>> >- Copied/pasted the SQL below and saved query
as "Query1"
>> >- Executed the query below...
>> >
>> >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];
>> >
>> >
>> >**** Feedback & Question 2 ****
>> >
>> >When I executed Query1, I got the same number of
records
>> (count = 9) -- just
>> >like the
>> >record count in Table1.
>> >
>> >Your record count (in your instructions under the "I
>> get:" section),
>> >however, changes
>> >from 9 to 7 records. Also, in your example I see
the "1
>> Yes" twice.
>> >
>> >What am I doing wrong? Should I expect to see 7
records
>> vs. 9 records?
>> >
>> >
>> >********************
>>
>> ~~~~~~~~~~~~~~~~~~~~~
>> It seems that if item 1 is also selected as Done, it
shows
>> up twice.
>>
>> So, the SQL code should finally be:
>>
>> SELECT DISTINCT a.*
>> FROM Table1 AS a, [SELECT Table1.Task
>> FROM Table1
>> WHERE Table1.Done="YES"]. AS b
>> WHERE Left([b].[task],Len([a].[task]))=[a].[task];
>>
>>
>>
>> >
>> >**** Question 3 ****
>> >
>> >What is the source for the following SELECT statement?
>> Is it "Table1" or
>> >is it
>> >"Query1"?
>> >
>> >SELECT Table1.Task
>> >FROM Table1
>> >WHERE Table1.Done=True
>> >
>> >********************
>>
>> ~~~~~~~~~~~~~~~~~~~~~
>> It is from a table, Table1.
>>
>> My SQL code is the same as if you paste the SQL code
into
>> a new query and save it. The 2nd query would join that
>> query on the original table. This way was easier to put
>> into a post, but a bit harder to understand.
>> ~~~~~~~~~~~~~~~~~~~~~
>>
>> >
>> >Again, I am really glad about your replying to this
>> thread and I hope that I
>> >will be
>> >able to get this to work (I'm sure the mistakes are on
my
>> part).
>> >
>> >I would truly appreciate any additional help from you.
>>
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~
>> You are welcome, and we'll get this figured out.
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~
>>
>> >
>> >THANKS AGAIN!!!
>> >
>> >Tom
>> >
>> >
>> >
>>
>
>
>.
>

Chris,

I email you the sample file a moment ago.

Thank you very much for your help in this matter!

Tom


"Chris Nebinger" <(E-Mail Removed)> wrote in message
news:1a59f01c42325$33817220$(E-Mail Removed)...
> Tom,
>
> I tried sending you an email..
> If you didn't get it, send me an email at my
> (E-Mail Removed)
>
>
> Chris Nebinger
>
>
> >-----Original Message-----
> >Chris...
> >
> >oops, please see my reply to my own thread.
> >
> >--
> >Thanks,
> >Tom
> >
> >
> >"Chris Nebinger" <(E-Mail Removed)>
> wrote in message
> >news:1ca1101c42269$90ccae20$(E-Mail Removed)...
> >> Comments inside of ~~~~~~~~~~~~~~~~~'s
> >>
> >>
> >> >-----Original Message-----
> >> >Chris:
> >> >
> >> >Thank you for your reply... reading your approach seems
> >> very promising;
> >> >however, I was not able to replicate your instructions
> >> and results.
> >> >
> >> >In between the ***s, I placed some questions... I hope
> >> you'll be able to
> >> >get me on right track again.
> >> >
> >> >Here's what I done (step by step)...
> >> >
> >> >
> >> >STEP 1. TABLE DESIGN
> >> >- Created a table called "Table1"
> >> >- Added 2 fields: "Task" [Data type = Text]; "Done"
> [Data
> >> type = Text]
> >> >- Appended the values below (as you suggested)
> >> >
> >> >Task Done
> >> >1 Yes
> >> >1.1 No
> >> >1.1.1 Yes
> >> >1.1.2 No
> >> >1.2 No
> >> >2 No
> >> >2.1 No
> >> >2.1.1 No
> >> >2.1.2 Yes
> >> >
> >> >
> >> >**** Question 1 ****
> >> >
> >> >Based on the values "Yes" & " No", should the "Done"
> >> field be a "Yes/No"
> >> >field (boolean) or shall I leave it "Text"?
> >> >
> >> >If your example requires a boolean value (True or
> False),
> >> is there a way
> >> >around to use
> >> >a text data type instead? If not, I move on to the
> next
> >> step.
> >> >
> >> >********************
> >> ~~~~~~~~~~~~~~~~~~~~~
> >> It really doesnt matter. If the field is only going to
> >> hold a yes or no, then I would leave it as boolean. If
> >> you change it to text, then the SQL should look like:
> >> SELECT a.*
> >> FROM Table1 AS a, [SELECT Table1.Task
> >> FROM Table1
> >> WHERE Table1.Done="YES"]. AS b
> >> WHERE Left([b].[task],Len([a].[task]))=[a].[task];
> >> ~~~~~~~~~~~~~~~~~~~~~
> >> >
> >> >
> >> >
> >> >STEP 2. QUERY DESIGN
> >> >- Open a query in design view and switched to SQL MODE
> >> >- Copied/pasted the SQL below and saved query
> as "Query1"
> >> >- Executed the query below...
> >> >
> >> >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];
> >> >
> >> >
> >> >**** Feedback & Question 2 ****
> >> >
> >> >When I executed Query1, I got the same number of
> records
> >> (count = 9) -- just
> >> >like the
> >> >record count in Table1.
> >> >
> >> >Your record count (in your instructions under the "I
> >> get:" section),
> >> >however, changes
> >> >from 9 to 7 records. Also, in your example I see
> the "1
> >> Yes" twice.
> >> >
> >> >What am I doing wrong? Should I expect to see 7
> records
> >> vs. 9 records?
> >> >
> >> >
> >> >********************
> >>
> >> ~~~~~~~~~~~~~~~~~~~~~
> >> It seems that if item 1 is also selected as Done, it
> shows
> >> up twice.
> >>
> >> So, the SQL code should finally be:
> >>
> >> SELECT DISTINCT a.*
> >> FROM Table1 AS a, [SELECT Table1.Task
> >> FROM Table1
> >> WHERE Table1.Done="YES"]. AS b
> >> WHERE Left([b].[task],Len([a].[task]))=[a].[task];
> >>
> >>
> >>
> >> >
> >> >**** Question 3 ****
> >> >
> >> >What is the source for the following SELECT statement?
> >> Is it "Table1" or
> >> >is it
> >> >"Query1"?
> >> >
> >> >SELECT Table1.Task
> >> >FROM Table1
> >> >WHERE Table1.Done=True
> >> >
> >> >********************
> >>
> >> ~~~~~~~~~~~~~~~~~~~~~
> >> It is from a table, Table1.
> >>
> >> My SQL code is the same as if you paste the SQL code
> into
> >> a new query and save it. The 2nd query would join that
> >> query on the original table. This way was easier to put
> >> into a post, but a bit harder to understand.
> >> ~~~~~~~~~~~~~~~~~~~~~
> >>
> >> >
> >> >Again, I am really glad about your replying to this
> >> thread and I hope that I
> >> >will be
> >> >able to get this to work (I'm sure the mistakes are on
> my
> >> part).
> >> >
> >> >I would truly appreciate any additional help from you.
> >>
> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~
> >> You are welcome, and we'll get this figured out.
> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~
> >>
> >> >
> >> >THANKS AGAIN!!!
> >> >
> >> >Tom
> >> >
> >> >
> >> >
> >>
> >
> >
> >.
> >

Chris:

Got your email w/ the database. Wow, very impressive... the logical
dependencies work superb!!!

Thank you so much for helping me out.

--
Thanks,
Tom


"Tom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Chris,
>
> I email you the sample file a moment ago.
>
> Thank you very much for your help in this matter!
>
> Tom
>
>
> "Chris Nebinger" <(E-Mail Removed)> wrote in message
> news:1a59f01c42325$33817220$(E-Mail Removed)...
> > Tom,
> >
> > I tried sending you an email..
> > If you didn't get it, send me an email at my
> > (E-Mail Removed)
> >
> >
> > Chris Nebinger
> >
> >
> > >-----Original Message-----
> > >Chris...
> > >
> > >oops, please see my reply to my own thread.
> > >
> > >--
> > >Thanks,
> > >Tom
> > >
> > >
> > >"Chris Nebinger" <(E-Mail Removed)>
> > wrote in message
> > >news:1ca1101c42269$90ccae20$(E-Mail Removed)...
> > >> Comments inside of ~~~~~~~~~~~~~~~~~'s
> > >>
> > >>
> > >> >-----Original Message-----
> > >> >Chris:
> > >> >
> > >> >Thank you for your reply... reading your approach seems
> > >> very promising;
> > >> >however, I was not able to replicate your instructions
> > >> and results.
> > >> >
> > >> >In between the ***s, I placed some questions... I hope
> > >> you'll be able to
> > >> >get me on right track again.
> > >> >
> > >> >Here's what I done (step by step)...
> > >> >
> > >> >
> > >> >STEP 1. TABLE DESIGN
> > >> >- Created a table called "Table1"
> > >> >- Added 2 fields: "Task" [Data type = Text]; "Done"
> > [Data
> > >> type = Text]
> > >> >- Appended the values below (as you suggested)
> > >> >
> > >> >Task Done
> > >> >1 Yes
> > >> >1.1 No
> > >> >1.1.1 Yes
> > >> >1.1.2 No
> > >> >1.2 No
> > >> >2 No
> > >> >2.1 No
> > >> >2.1.1 No
> > >> >2.1.2 Yes
> > >> >
> > >> >
> > >> >**** Question 1 ****
> > >> >
> > >> >Based on the values "Yes" & " No", should the "Done"
> > >> field be a "Yes/No"
> > >> >field (boolean) or shall I leave it "Text"?
> > >> >
> > >> >If your example requires a boolean value (True or
> > False),
> > >> is there a way
> > >> >around to use
> > >> >a text data type instead? If not, I move on to the
> > next
> > >> step.
> > >> >
> > >> >********************
> > >> ~~~~~~~~~~~~~~~~~~~~~
> > >> It really doesnt matter. If the field is only going to
> > >> hold a yes or no, then I would leave it as boolean. If
> > >> you change it to text, then the SQL should look like:
> > >> SELECT a.*
> > >> FROM Table1 AS a, [SELECT Table1.Task
> > >> FROM Table1
> > >> WHERE Table1.Done="YES"]. AS b
> > >> WHERE Left([b].[task],Len([a].[task]))=[a].[task];
> > >> ~~~~~~~~~~~~~~~~~~~~~
> > >> >
> > >> >
> > >> >
> > >> >STEP 2. QUERY DESIGN
> > >> >- Open a query in design view and switched to SQL MODE
> > >> >- Copied/pasted the SQL below and saved query
> > as "Query1"
> > >> >- Executed the query below...
> > >> >
> > >> >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];
> > >> >
> > >> >
> > >> >**** Feedback & Question 2 ****
> > >> >
> > >> >When I executed Query1, I got the same number of
> > records
> > >> (count = 9) -- just
> > >> >like the
> > >> >record count in Table1.
> > >> >
> > >> >Your record count (in your instructions under the "I
> > >> get:" section),
> > >> >however, changes
> > >> >from 9 to 7 records. Also, in your example I see
> > the "1
> > >> Yes" twice.
> > >> >
> > >> >What am I doing wrong? Should I expect to see 7
> > records
> > >> vs. 9 records?
> > >> >
> > >> >
> > >> >********************
> > >>
> > >> ~~~~~~~~~~~~~~~~~~~~~
> > >> It seems that if item 1 is also selected as Done, it
> > shows
> > >> up twice.
> > >>
> > >> So, the SQL code should finally be:
> > >>
> > >> SELECT DISTINCT a.*
> > >> FROM Table1 AS a, [SELECT Table1.Task
> > >> FROM Table1
> > >> WHERE Table1.Done="YES"]. AS b
> > >> WHERE Left([b].[task],Len([a].[task]))=[a].[task];
> > >>
> > >>
> > >>
> > >> >
> > >> >**** Question 3 ****
> > >> >
> > >> >What is the source for the following SELECT statement?
> > >> Is it "Table1" or
> > >> >is it
> > >> >"Query1"?
> > >> >
> > >> >SELECT Table1.Task
> > >> >FROM Table1
> > >> >WHERE Table1.Done=True
> > >> >
> > >> >********************
> > >>
> > >> ~~~~~~~~~~~~~~~~~~~~~
> > >> It is from a table, Table1.
> > >>
> > >> My SQL code is the same as if you paste the SQL code
> > into
> > >> a new query and save it. The 2nd query would join that
> > >> query on the original table. This way was easier to put
> > >> into a post, but a bit harder to understand.
> > >> ~~~~~~~~~~~~~~~~~~~~~
> > >>
> > >> >
> > >> >Again, I am really glad about your replying to this
> > >> thread and I hope that I
> > >> >will be
> > >> >able to get this to work (I'm sure the mistakes are on
> > my
> > >> part).
> > >> >
> > >> >I would truly appreciate any additional help from you.
> > >>
> > >> ~~~~~~~~~~~~~~~~~~~~~~~~~~
> > >> You are welcome, and we'll get this figured out.
> > >> ~~~~~~~~~~~~~~~~~~~~~~~~~~
> > >>
> > >> >
> > >> >THANKS AGAIN!!!
> > >> >
> > >> >Tom
> > >> >
> > >> >
> > >> >
> > >>
> > >
> > >
> > >.
> > >
>
>