Queries of Queries of Queries work style 3

[lonetree]

I have been working with Access for several years. I am untrained in programing or sql. In my crude fashion, I have begun to develop what I would call (almost) batteries of queries, sometimes stringing eight to twelve queries together in linkages. I keep looking for others doing the same sort of work and do not see others doing this. So I will ask my foolish question. Am I the only one doing this and am I being dumb. Some of my programmer friends suggest that if I were using sql it would be better but when I try and look at the sql behind some of my chains of queries I have created in Access graphic user interface, it looks so ugly that I am afraid I could never make sense of it.

I guess what I am asking is for some of the power users to either tell me I should give this up and take sql classes or it is OK to work the way I am working. Most of my work is not routine and would fall under the guise of scientific investigation. I am a scientist and my approach is very empirical, my work goes to maps and I can see if it makes sense or not by mapping the results. I have developed query naming techniques to keep the queries in order and to show which queries are linked and in what order. I try and make extensive use of documenting the intent of each step along the chain. All of my joins are ad hoc to the problem at hand and done in queries.

I would also like to make connections with other workers doing similar work.

Another stupid question, is there such a thing as patenting complex query systems for specific tasks?

Thank you

 

[ghubbell]

Hi Lonetree,
I don't understand peoples fear of having tons of queries, or stacking queries. I have some that do complex math - you know .0123456 x123 to the power of -3 and stuff like that. I have to. I recently posted here of how I cut the run time of a procedure used to populate a form from 30 seconds using what looks like nothing in code, to 3 seconds using 6 "stacked" saved queries. I think the most I've ever had to do was 8 and to reproduce this in Visual Basic with SQL statements would take pages of work. SQL is the real language of the database. If you can have a saved query, do it. It's a fact: queries are the real power behind any database. In Access 2000 I save everything as a query: The record source to every form, combo box, listbox, report...you get the idea. Why? In 2000 you cannot modify a form or report while they're running but you can modify the query! The big database here has over 800. (Oh, and for those who doubt the benefits, you can recycle them anywhere you'd like: "Customers combo box" - used on say, 10 forms, 1 query.)

SQL classes? You're doing them. Place a new simple query in "SQL" view and start to study it. Keep flipping from design to SQL to see the effect of adding a field or sorting or summing...It comes together pretty quickly... There's lots of good info in F1 help and libraries full of good reading if you choose.

Tek-Tips is the connection to meet others that's for sure. The variety of issues that come through here are astounding and I don't go a day without picking up a little more knowledge or just "seeing things from a different perspective". Take a tour and pick a problem someone has posted and see if you can understand or solve it!

Patents. Not a clue. You certainly are allowed to hold the rights to intellectual property and your Access application if created by you is yours. If your work is treasured, you might spend some time studying Access security to keep those prying eyes away.

If your queries do what their supposed to, I have some really great doubts that with Access you would find a better way. Stick around though. Someone's bound to debate this! Gord
ghubbell@total.net

 

[lonetree]

Thank you. I appreciate the response. I am semi retired and working largely on my own trying to create consultant reports for sale and I was feeling like the limb I was climbing out on was was getting rather long. I work with three tables, the largest is over 46,000 records now and some of my query chains do take up to 5 to 10 seconds now, but only ten years ago I was trying to do this with spreadsheets and before that,,,,, well it was impossible. This does seem like an excellent forum, however, it is amazing to me how in just one program like Access so many folks are working on so many distinct things. What I am trying to say is the scope of what is being done in these database programs is very large. I suspect that the objectives of my work and the nature of my three tables leads me in very specific directions, so the problem is sorting out from all of these messages which pertains to my current problems. Thank you again.

 

[ghubbell]

Anytime and Thank you!
You might place your email address in this thread so anyone working in your specific field could contact you directly. You can always grab a free "hotmail" or similar account if you have any doubts or concerns. I'm sure someone will come along with even more ideas to keep you going! Gord
ghubbell@total.net

 

[lonetree]

Excuse me for one more dumb question. Is the appropriate jargon "stacking queries". When I search for "stacking" or "stacking queries" I find one previous message by yourself, Ghubbell, on what you and I mean by "stacking."

Thanks again
Lonetree

 

[ghubbell]

You'll have to excuse me as I tend to speak in a language all my own: I call them stacked when you use the results of one as the basis of the next and so on.
"Subqueries" by example, are queries within queries, usually used to define criteria.
If you do a search using F1, just type in "Query" then select from the 400+ topics available. There's some "fluff" (another Gord word but I think you know what I mean ;-) ) but there's some really good details and examples to be found! Gord
ghubbell@total.net

 

[MichaelRed]

I believe the "PC" term is 'layered'. This is a common process for complex processes. I have worked in a situation where the 'data came from as many as seventeen differnt sources with five or six different data 'formats'. Often, the data sets were offset in time, so a process to create a consolidated report involved importing some data, linking to some data and creating dynamic connections to other sources, checking date/time stamps of files/tables and sources, 'normalizing' these to common terms (date/time, currency ... ). Obviously, this is not done with 'single' queries. So your use of multiple queries for transformation is - to me - rather commonplace.

As for the use of SQL vs the Query by grid from Ms. Access, I wouldn't really get very concerned about the 'purity' of hte process. I tend to do a mixture of the processes, as he QByG is often a quick way to get the basics of a query pretty quickly. Fine tuning the query in SQL is a good way to improve the overall sytem. One simple example,. To build a Crostab query entirely "by hand" takes perhaps five to ten minutes. Building the basic query in QByG and adding fields might take only two or three. The only caution I would offer is that database operations are often not at all obvious. A through understanding of the process is really necessary to assure the results are really correct. Many 'programmers' make the mistake of hinking of SQL as a programming language, while it is really quite different from traditional programming (like VB / VBA). It would be adviseable to take at least a some formal coursework in SQL if you are going to base commercial applications on the results.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over