Organizing Queries and Reports

[Suzanne Knapp]

Are there any tools available to help in organizing and/or analyzing the
structure and relationships among queries, subqueries, and reports? I have
a terrible time trying to remember which queries use which subqueries, which
reports use which queries, etc. I've tried various naming schemes but
nothing has worked really well. I think I have way too many queries but
every time I try to weed them out I end up deleting one that is used
somewhere. Any advice would be appreciated.

Suzanne

 

[Jeff Boyce]

Suzanne

Black Moshannon sells "Speed Ferret", and FMS sells "Total Access
Analyzer" -- these two commercial products give you a way to analyze the
objects in an Access database. I've also seen references to a freeware
analyzer (check Google.com).

Rather than deleting a query and then finding out, try adding "ZZZ" or some
such as a prefix to the query's name. If things break, you'll be able to
"un-Z" it. If nothing breaks, you can be fairly confident you can delete it
permanently.

A naming scheme I've used with some success involves sequencing numbers as
part of the name of a "group" of queries (e.g., qry_AAA_10_firststep,
qry_AAA_20_secondstep, qry_AAA_30...).

Good luck!

Jeff Boyce
<Access MVP>

 

[PC Datasheet]

There are two types of reports: reports and subreports. I begin the name of
ALL reports with either Rpt or SRpt. I also make sure what follows the
prefix is descriptive of what the report is about. One thing this does is
group all reports (Rpt) together in the database window and group all
subreports (SRpt) together in the database window. The names of all my
queries begin with Qry to ditinguish them from tables. (Tables begin with
Tbl). Queries that are the basis of a report have Qry followed by the name
of the report (QryRpt .... or QrySRpt.....). So besides the name telling you
what the query is used for, queries which are the basis of reports
(QryRpt...) are grouped together in the database window and queries that are
the basis of subreports (QrySRpt....) are grouped together in the database
window. I use a naming scheme for forms similar to reports and a naming
scheme for queries that are the basis of forms similar to report queries.

 

[Suzanne Knapp]

Jeff,

I downloaded the "Total Access Analyzer" demo - it is just what I was
looking for, if somewhat out of my price range for now! Speed Ferret is
somewhat less expensive (though still not cheap), but it's an interactive
"find and replace" function rather than a full analysis tool. No luck on
finding any freeware or shareware analyzers.

Thanks for the info, and for the advice on renaming before deleting - I'll
do that until I save up for TAA.

Suzanne

 

[Suzanne Knapp]

Thanks for your reply. I've tried similar naming schemes, but they don't
handle the case when one query is used as the basis for several reports or
one subquery for several queries. I seem to have that a lot!

 

[John Spencer (MVP)]

Find and Replace is a shareware. It is similiar to Speed Ferret. If I recall
correctly, the full version can give you a cross-ref listing.

Find and Replace http://www.rickworld.com

 

[Suzanne Knapp]

Yes, it does have a cross reference function in the registered version.
Perfect, thanks!