Data Dependencies Function

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

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(.[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(.[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
Click to expand...
task "1.1.1". That means that
 
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(.[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 said:
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(.[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(.[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
Click to expand...
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





.
Click to expand...
Click to expand...
 
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.

********************
Click to expand...
~~~~~~~~~~~~~~~~~~~~~
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(.[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(.[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?


********************
Click to expand...


~~~~~~~~~~~~~~~~~~~~~
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(.[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

********************
Click to expand...

~~~~~~~~~~~~~~~~~~~~~
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.
Click to expand...

~~~~~~~~~~~~~~~~~~~~~~~~~~
You are welcome, and we'll get this figured out.
~~~~~~~~~~~~~~~~~~~~~~~~~~
 
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(.[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 said:
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(.[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(.[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
Click to expand...
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





.
Click to expand...
Click to expand...
 
Chris...

oops, please see my reply to my own thread.

--
Thanks,
Tom


Chris Nebinger said:
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.

********************
Click to expand...
~~~~~~~~~~~~~~~~~~~~~
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(.[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(.[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?


********************
Click to expand...


~~~~~~~~~~~~~~~~~~~~~
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(.[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

********************
Click to expand...

~~~~~~~~~~~~~~~~~~~~~
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.
Click to expand...

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

Tom
Click to expand...
Click to expand...
 
Tom,

I tried sending you an email..
If you didn't get it, send me an email at my
(e-mail address removed)


Chris Nebinger

-----Original Message-----
Chris...

oops, please see my reply to my own thread.

--
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"
Click to expand...
[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
Click to expand...
False),
is there a way
around to use
a text data type instead? If not, I move on to the
Click to expand...
next
step.
********************
Click to expand...
~~~~~~~~~~~~~~~~~~~~~
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(.[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(.[task],Len([a].[task]))=[a].[task];


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

When I executed Query1, I got the same number of
Click to expand...
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
Click to expand...
the "1
Yes" twice.
What am I doing wrong? Should I expect to see 7
Click to expand...
records
vs. 9 records?
********************
Click to expand...

~~~~~~~~~~~~~~~~~~~~~
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(.[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

********************
Click to expand...

~~~~~~~~~~~~~~~~~~~~~
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
Click to expand...
my
part).
I would truly appreciate any additional help from you.
Click to expand...

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

Tom
Click to expand...
Click to expand...



.
Click to expand...
 
Chris,

I email you the sample file a moment ago.

Thank you very much for your help in this matter!

Tom


Chris Nebinger said:
Tom,

I tried sending you an email..
If you didn't get it, send me an email at my
(e-mail address removed)


Chris Nebinger

-----Original Message-----
Chris...

oops, please see my reply to my own thread.

--
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(.[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(.[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(.[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
Click to expand...



.
Click to expand...
Click to expand...
 
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 said:
Chris,

I email you the sample file a moment ago.

Thank you very much for your help in this matter!

Tom


Chris Nebinger said:
Tom,

I tried sending you an email..
If you didn't get it, send me an email at my
(e-mail address removed)


Chris Nebinger

-----Original Message-----
Chris...

oops, please see my reply to my own thread.

--
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(.[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(.[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(.[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






.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top