Dynamic Query Generation

[TastyWheat]

As much as I would like to tell my boss he's insane I'd also like to keep my job. I know this is possible, but it sounds very complicated. Basically, I need a way to generate queries on the fly. This is geared towards someone who doesn't know SQL of course. I know basically what he's looking for, comparing sales over some period of time. However, he might want annual numbers, quarterly numbers, percentages, overages/shortages, and any kind of sales related query you can think of. The only way I figure I can do that is to have a form build the SQL statement, save the SQL statement as a query, then have him open the query. Is there another way I can do this without necessarily saving a query first?

 

[The_Doc_Man]

Pat's right (as usual). A little education wouldn't hurt. But...

Here's the kicker. If you have a "pointy-haired boss" type, don't DARE give him insert/update/delete access to your live tables. And you might be faced with "you can lead a boss to ideas but you can't make him think."

If your boss wants what I think he wants, your counter is "Boss, I can write what you want but it will cost xyz hours. Or I can teach you how to use the query grid yourself, which will cost only abc hours. OR you can define the things you want to see ahead of time and I can just pre-define them for you so a click of a button will open up the required canned query. That will cost a lot less than the generalized query routine I would have to write for you. And it would have less chance of error. I can build what you want now and as time goes on, tailor them to more exactly meet your needs."

Here is where you'll run into counter arguments. "My time is too valuable to waste on learning such technical details." "That's too complicated. I'll never learn how to do that." "Nonsense, you were hired to do ad-hoc jobs like this. So buckle down and do your job." "As often as I hear that the DB has changed structures, I'll never remember now to find the things I want to see."

Trust me - been there, done that. So decide what kind of boss you have before you approach him. Your choices:

a. Attack the literal problem. Start writing general query code - with the understanding that there are limits to string size that you can execute without using a pre-defined QueryDef type query. Look it up in the help files under "Specification" (I think) for AC2K and above, or "Limits" for AC97 and below. Hope you have AC2K or above. Look in the "Similar Threads" list or search for "Dynamic Query" within this forum for a myriad of solutions to this often-requested solution.


b. Educate the boss in using the Query Grid. Justify it with a cost/benefit type of analysis.

c. Pin down the boss on what he really wants to see and build that (them) instead. Justify it with a cost/benefit/accuracy type of analysis.

d. Log on to Monster.Com and post your resume, because before long this pointy-haired boss will drive you nuts.

 

[TastyWheat]

Well, these are only SELECT queries that I'm doing btw. I don't think creating the queries are too complicated. At the very least I think I can spit out the SELECT statement that should be run. Still, I want these queries to be displayed dynamically. Using a subform in datasheet view doesn't sound like the right choice since those fields need to be pre-defined and I don't know of any code that can add/remove text boxes.

 

[TastyWheat]

For those of you who are interested, I finished the project. Making the queries was a pain so I had to create a custom Query class to make the seperate clauses (SELECT, FROM, WHERE, etc.) and put them all together.

The actual display wasn't very hard though. I just made up a subform datasheet with every possible column (only 15) and hid whichever ones I didn't use on-the-fly.