Saving queires

  • Thread starter Thread starter annysjunkmail
  • Start date Start date
A

annysjunkmail

Hi,

I manage a A2K database and am continually asked by management to
produce various reports. Reports are often complex and require several
queries to be constructed before desired results are produced.

This leads to the cause of my problem. My query window is building up
so many queries to the point that it's getting a bit messy and is
particularly hard for colleagues to follow the logic of saved queries,
i.e. reports are often based on previous reports etc.

Is there a better way of manging queries other than saving them in the
query window? Can someone recommend a way forward or preferably does
someone have a sample database they wish to share or maybe can point me
to a website with a suggested solution?

Grateful for a reply as I'm sure I'm not the first person to suffer
from 'query overload'?

Many thanks
Chris
 
Chris,

Not very clear on what exactly you are after. Are you looking for a way
to (a) "show" users just the "final" in each chain of queries, so they
only get to see the ones they should be running, or (b) keep track of
which query uses which?

In the former case, there are a few alternatives to choose from:
(1) hide the queries the users should not be "seeing" (assuming their
settings are to not show hidden objects)
(2) create a new group to put "final" queries only
(3) use a listbox on a form to show "final" queries only, and use a
command button to run the selected one. A convenient trick is to prefix
the "final" queries with a standard prefix, to use for filtering query
names from MSysObjects into the listbox, so it's updated dynamically.

In the latter case, standard Access is not very helpful, but there are
third party tools out there to help with it. I use a simple piece of
code to export the SQL expression of every query in a text file, so I
can then open it in any editor and do a search on a query name, to see
if it's used in another. Can share if you want it.

HTH,
Nikos
 
Hi Nikos,

Thanks for your reply.
Apologies for being unclear as to my exact requirements - I was wanting
to keep the question brief - maybe a bit too brief as it seems :-(

Thanks for the tip using Groups - was not aware of this facility in
Access and is initiallly very useful. My main problem that I have to
build between 2-5 queries for each report and, as I am asked for approx
2 reports a day (usually different ones), this means that I save
between 4-10 queries a day, which is approx 20-50 a week - you can see
where I'm going. I have to save these queries for reference and audit
purposes. I use a naming convention as close as I can to a requested
report (i.e. qryAllSuccessfulApplicationsByYearByGroup) but some staff
find it difficult to follow this system.

Using your list box idea I created a possible working solution. I
created 2 tables - tblQueryName and tblQueriesSQL, which I joined using
QueryID. I then used the form wizard to create a linked form scenario
and now I save the SQL of each query as a continuous record in
tblQueriesSQL. Staff can now view the name of the report; its title
and description; who requested it; deadlines; and more importantly the
SQL statements used to build the final report.

It is not perfect (I have to do a lot of copying and pasting) but at
least is sort of works. Probably clear as mud again but if you can
suggest a better system for managing queries then I would welcome it.


Regards

Chris
 
Chris,

Sounds like you've gone quite some way! If you're going to be storing
SQL statements in a table, then here's something you might find useful:

Sub Export_Query_Design()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Query Design.txt" For Output As #1
Print #1, "Query Design for Access Project:" & vbTab & CurrentDb.Name
For Each qry In CurrentDb.QueryDefs
Print #1,
Print #1, "Query:" & vbTab & qry.Name
Print #1, qry.SQL
Next
Close #1
End Sub

I put this together to export to a text file, but you could modify it to
populate a table instead, using a recordset operation.

Regards,
Nikos
 
Nikos,

Thanks for the code - very efficient and I can see the potential for
modifying it for populating a table - could save me a lot of copying
and pasting. Will have to research how to do this though but enjoying
the challenge of it all.

Many thanks for all your help today

Chris
 
Back
Top