Mapping out an access database and keeping track of how your database works

[Bob]

Hi all,

I have taught myself how to create queries and forms etc and I use
Access for a lot of data manipulation then export back to excel or pump
the info into forms.

My biggest problem is trying to remember which queries feed into which
queries and what criteria I have placed in each query so that when I
come back the next week to make changes I know what I have to change
straight away and not have to test every single query and table to make
sure nothing has changed. Is there an easy way to map out the database
so you know exactly what feeds into what, and is it just that I'm
making too many queries to do something simple?

Just to give you an idea... If I want to group something but include
fields that are not included in the grouping or the criteria then I do
the group query then do another query to incorporate the grouped query
and also its origin, but with the fields from the original table in the
second query along with the grouped fields. I tend to make a lot of
queries because of that sort of workaround. So my main questions are:

1) How do I keep track of all the queries and what their criteria and
uses are (so that if I want to change something I don't have to track
back through all the queries)
2) Is there a lot that I could learn to reduce the amount of queries i
use. Ie. If I did more SQL could I build a lot less queries? so should
I study SQL more, or Access more?

 

[Van T. Dinh]

1. I try not to use sets of queries where one query depends on another
query (or other queries) but if I have to, I nonmally name them like:

qselQueryPurpose_Stage1
qselQueryPurpose_Stage2
....
qselQueryPurpose_StageXF

X is the last number and F indicates to me that this is the Final Query in
the set. This way, the related queries are sorted together in the Queries
Container window.

2. To reduce the number of sets of queries, I use a fair bit of
sub-queries. You certain need to know SQL to be able to construct
sub-queries. While the QBE can create some sub-queires, you can do a lot
more by typing the SQL directly.

3. If you use Access 2003, you may be able to use the "Object Dependencies"
feature to work out which object depends on which objects. Check Access
Help on Object Dependecies.

Note: This requires the Name Auto-Correct (at least "tracking") to be
turned on but the Name Auto-Correct has some problems of itsown so turn it
off after you finish checking Object Dependencies.

4. It is always good if you know more about Access and JET SQL ... (I am
still finding things that I don't know about Access or JET SQL ...). The
more you know, the more you can do with Access / JET.