Query where used diagram

I have a where used utility that shows where any table or query is used
- in a query, form or table.

I would like some kind of diagram that shows these relations. I have
some reports that require a lot of queries with query_totals and union
queries to produce current day, current week, current month, current
year to date and prior day, week month and year data.. these are
Unioned together with appropriate keys to serve as input to a single report.

I would like to produce a diagram that starts at the report, and shows
an indented structure of the queries used to create the query.

something like:

Rpt_Daily_Summary

qry_Daily_Summary_Data

qry_Daily_Data_combined

query_daily_ data

query_Daily_data_Prioryear

qry_Weekly_Data_Combined

query_Weekly_ data

query_Weekly_data_Prioryear

qry_Monthly_Data_Combined
..
..
..

Suggestions?

Bob

re: Query where used diagram

On Fri, 12 Aug 2005 15:23:45 -0500, Bob Alston
<tulsaalstonsNOSPAM@cox.net> wrote:

I did this once. You have to write a recursive function that looks at
the sql statement for each query, figures out where the FROM clause
is, and recursively looks at the elements of that clause to dig deeper
to the next layer.

-Tom.


[color=blue]
>I have a where used utility that shows where any table or query is used
>- in a query, form or table.
>
>I would like some kind of diagram that shows these relations. I have
>some reports that require a lot of queries with query_totals and union
>queries to produce current day, current week, current month, current
>year to date and prior day, week month and year data.. these are
>Unioned together with appropriate keys to serve as input to a single report.
>
>I would like to produce a diagram that starts at the report, and shows
>an indented structure of the queries used to create the query.
>
>something like:
>
>Rpt_Daily_Summary
>
> qry_Daily_Summary_Data
>
> qry_Daily_Data_combined
>
> query_daily_ data
>
> query_Daily_data_Prioryear
>
> qry_Weekly_Data_Combined
>
> query_Weekly_ data
>
> query_Weekly_data_Prioryear
>
> qry_Monthly_Data_Combined
>.
>.
>.
>
>Suggestions?
>
>Bob[/color]

re: Query where used diagram

Tom van Stiphout wrote:[color=blue]
> On Fri, 12 Aug 2005 15:23:45 -0500, Bob Alston
> <tulsaalstonsNOSPAM@cox.net> wrote:
>
> I did this once. You have to write a recursive function that looks at
> the sql statement for each query, figures out where the FROM clause
> is, and recursively looks at the elements of that clause to dig deeper
> to the next layer.
>
> -Tom.
>
>
>
>[color=green]
>>I have a where used utility that shows where any table or query is used
>>- in a query, form or table.
>>
>>I would like some kind of diagram that shows these relations. I have
>>some reports that require a lot of queries with query_totals and union
>>queries to produce current day, current week, current month, current
>>year to date and prior day, week month and year data.. these are
>>Unioned together with appropriate keys to serve as input to a single report.
>>
>>I would like to produce a diagram that starts at the report, and shows
>>an indented structure of the queries used to create the query.
>>
>>something like:
>>
>>Rpt_Daily_Summary
>>
>> qry_Daily_Summary_Data
>>
>> qry_Daily_Data_combined
>>
>> query_daily_ data
>>
>> query_Daily_data_Prioryear
>>
>> qry_Weekly_Data_Combined
>>
>> query_Weekly_ data
>>
>> query_Weekly_data_Prioryear
>>
>> qry_Monthly_Data_Combined
>>.
>>.
>>.
>>
>>Suggestions?
>>
>>Bob[/color]
>
>[/color]
Thanks. I have such a function that shows a single level where used for
any table or query. Want I need is a way to transform that into a
diagram that will show the 3 or four levels of table --> query -->
query..... to Report or Form.

Bob

re: Query where used diagram

On Fri, 12 Aug 2005 15:23:45 -0500, Bob Alston <tulsaalstonsNOSPAM@cox.net>
wrote:
[color=blue]
>I have a where used utility that shows where any table or query is used
>- in a query, form or table.
>
>I would like some kind of diagram that shows these relations. I have
>some reports that require a lot of queries with query_totals and union
>queries to produce current day, current week, current month, current
>year to date and prior day, week month and year data.. these are
>Unioned together with appropriate keys to serve as input to a single report.
>
>I would like to produce a diagram that starts at the report, and shows
>an indented structure of the queries used to create the query.
>
>something like:
>
>Rpt_Daily_Summary
>
> qry_Daily_Summary_Data
>
> qry_Daily_Data_combined
>
> query_daily_ data
>
> query_Daily_data_Prioryear
>
> qry_Weekly_Data_Combined
>
> query_Weekly_ data
>
> query_Weekly_data_Prioryear
>
> qry_Monthly_Data_Combined
>.
>.
>.
>
>Suggestions?
>
>Bob[/color]

The most obvious thought that comes to mind is to use a treeview control. The
other option would be to write the data to a table in which each record has a
parent and child query name, then make the table its own sub-datasheet. That
allows you to open the table, and drill down as desired.

re: Query where used diagram

On Fri, 12 Aug 2005 22:13:57 -0500, Bob Alston
<tulsaalstonsNOSPAM@cox.net> wrote:

How about if you wrote the results to a table:
Level Query
0 Rpt_Daily_Summary
1 qry_Daily_Summary_Data
2 qry_Daily_Data_combined
2 qry_Weekly_Data_Combined
etc.
Then you could create a report with a wide textbox, and prefix the
Query value by Level times a few spaces:
select Space$(Level*4) & Query from MyTable

-Tom.

[color=blue]
>Tom van Stiphout wrote:[color=green]
>> On Fri, 12 Aug 2005 15:23:45 -0500, Bob Alston
>> <tulsaalstonsNOSPAM@cox.net> wrote:
>>
>> I did this once. You have to write a recursive function that looks at
>> the sql statement for each query, figures out where the FROM clause
>> is, and recursively looks at the elements of that clause to dig deeper
>> to the next layer.
>>
>> -Tom.
>>
>>
>>
>>[color=darkred]
>>>I have a where used utility that shows where any table or query is used
>>>- in a query, form or table.
>>>
>>>I would like some kind of diagram that shows these relations. I have
>>>some reports that require a lot of queries with query_totals and union
>>>queries to produce current day, current week, current month, current
>>>year to date and prior day, week month and year data.. these are
>>>Unioned together with appropriate keys to serve as input to a single report.
>>>
>>>I would like to produce a diagram that starts at the report, and shows
>>>an indented structure of the queries used to create the query.
>>>
>>>something like:
>>>
>>>Rpt_Daily_Summary
>>>
>>> qry_Daily_Summary_Data
>>>
>>> qry_Daily_Data_combined
>>>
>>> query_daily_ data
>>>
>>> query_Daily_data_Prioryear
>>>
>>> qry_Weekly_Data_Combined
>>>
>>> query_Weekly_ data
>>>
>>> query_Weekly_data_Prioryear
>>>
>>> qry_Monthly_Data_Combined
>>>.
>>>.
>>>.
>>>
>>>Suggestions?
>>>
>>>Bob[/color]
>>
>>[/color]
>Thanks. I have such a function that shows a single level where used for
>any table or query. Want I need is a way to transform that into a
>diagram that will show the 3 or four levels of table --> query -->
>query..... to Report or Form.
>
>Bob[/color]

re: Query where used diagram

Tom van Stiphout wrote:[color=blue]
> On Fri, 12 Aug 2005 22:13:57 -0500, Bob Alston
> <tulsaalstonsNOSPAM@cox.net> wrote:
>
> How about if you wrote the results to a table:
> Level Query
> 0 Rpt_Daily_Summary
> 1 qry_Daily_Summary_Data
> 2 qry_Daily_Data_combined
> 2 qry_Weekly_Data_Combined
> etc.
> Then you could create a report with a wide textbox, and prefix the
> Query value by Level times a few spaces:
> select Space$(Level*4) & Query from MyTable
>
> -Tom.
>
>
>[color=green]
>>Tom van Stiphout wrote:
>>[color=darkred]
>>>On Fri, 12 Aug 2005 15:23:45 -0500, Bob Alston
>>><tulsaalstonsNOSPAM@cox.net> wrote:
>>>
>>>I did this once. You have to write a recursive function that looks at
>>>the sql statement for each query, figures out where the FROM clause
>>>is, and recursively looks at the elements of that clause to dig deeper
>>>to the next layer.
>>>
>>>-Tom.
>>>
>>>
>>>
>>>
>>>
>>>>I have a where used utility that shows where any table or query is used
>>>>- in a query, form or table.
>>>>
>>>>I would like some kind of diagram that shows these relations. I have
>>>>some reports that require a lot of queries with query_totals and union
>>>>queries to produce current day, current week, current month, current
>>>>year to date and prior day, week month and year data.. these are
>>>>Unioned together with appropriate keys to serve as input to a single report.
>>>>
>>>>I would like to produce a diagram that starts at the report, and shows
>>>>an indented structure of the queries used to create the query.
>>>>
>>>>something like:
>>>>
>>>>Rpt_Daily_Summary
>>>>
>>>> qry_Daily_Summary_Data
>>>>
>>>> qry_Daily_Data_combined
>>>>
>>>> query_daily_ data
>>>>
>>>> query_Daily_data_Prioryear
>>>>
>>>> qry_Weekly_Data_Combined
>>>>
>>>> query_Weekly_ data
>>>>
>>>> query_Weekly_data_Prioryear
>>>>
>>>> qry_Monthly_Data_Combined
>>>>.
>>>>.
>>>>.
>>>>
>>>>Suggestions?
>>>>
>>>>Bob
>>>
>>>[/color]
>>Thanks. I have such a function that shows a single level where used for
>>any table or query. Want I need is a way to transform that into a
>>diagram that will show the 3 or four levels of table --> query -->
>>query..... to Report or Form.
>>
>>Bob[/color]
>
>[/color]
Thanks. I was starting to develop something like that in my head.
However it is the overall process that seems daunting to me. I would
need to take the where used table and then recursively process each
object to identify its predecesser objects. I much prefer to develop a
business system than a utility like this.

Thanks for the help.

Anyone ever done this before?? Surely I am not alone in wanting this
(and not wanting to spend $299 to get the commercial solution).

Bob