?? SQL string in table as a record

[OPMCoordinator]

Hello all.

im having a little trouble explaining what i nedd so please bear with me..

What I am essentially trying to do is have a record in a table that includes a query.

The thing is although each dataset will have the same setout, (Date, Amount, Target Amount), each record may have a totally different query (Amount = Tons, Staff Numbers, Downtime, Reject Percent, Customer Complaints from any table in any database that is used to monitor performance).

The idea is for each record in a table to have a query that returns a dataset to use in a graph in the various forms/reports.
I should be able to set the graphs dataset to the appropriate query for each record.

I think I need to construct the query in SQL (or copy the SQL code after building the query the normal way) and insert it in a field as a string, use the string from the field as a variable in VBA to define the query.

something like

Sub WhateverWillHappenToShowGraph()
dim WhatHaveYouBeenDoingAboutIt as string
WhatHaveYouBeenDoingAboutIt = me.QuerySQLString.Value
me.ProgressGraph.Recordset = "'" & WhatHaveYouBeenDoingAboutIt & "'"
me.ProgressGraph.Requery
End Sub

(i know thw syntax will be way wrong, but i dont know it off the top of my head right now)

Does the general drift make sense?
Will it work?
Is there a better way?

Cheers
Mike C
-----------------------------------------------
The only stupid question is the one that goes unasked...

 

[rosenn]

I have been working on something similar

I have a form that end-users will use to build a query. The query appears in a subform. They first choose from 3 categories (with cmd buttons) - each button enables or disables the visibility property of several option buttons based on which criteria go with which search category. Then, next to the option buttons are list boxes with the criteria that they can pick to use for the query. They also have a choice of archived searchs that have their SQL already stored as a string in tblSearches.

Then, they click the my "search" button. If they chose an archived search, it opens the table, pulls up the sql and uses it to query the subform. If they have not chosen an archived search, the routine 'reads' the form and, from the category it chooses the subform's source form and creates the "SELECT" and "FROM" and "ORDER BY". I could have done those all customizable as well, but there are only so many hours in the day.. Then, the routine generates its own little search algorithm string by 'reading' the options chosen and sends those into a public function I wrote which returns the custom "WHERE" statement. The function is just a big bunch of SELECT Case statements to build the SQL's 'where' statement. To store it, I store the whole SQL in a string in tblSearches and allow users to name and date the search and write a little attached memo about it.

Is this what you are trying to do?

-Nelson

 

[pono1]

Mike and Nelson,

This may or may not be off-point for one or both of you: Perhaps you can make and save parameter queries (using textboxes on a form to get changing criteria), using some sort of logical naming scheme so that you can call the appropriate query depending on user selections...

'simple illustration
If me.chkboxA = -1 and me.chkBoxB = 0 Then
	DoCmd.OPenQuery "QryGetAllLastNames"
		Exit sub
End if

If chkBoxA = 0 and chkBox b = -1 Then
	Docmd.Openquery "QryGetLikeLastNames"
		Exit sub
End if

That is, Access, in a sense, already provides a "table" for housing SQL strings: Saved Queries.

Regards,
Tim

 

[rosenn]

I guess you could do that

The only problem I have with that is then, depending on how flexible you want the thing to be, you have to apply a filter or something to focus the query. I haven't explored the world of filters yet, but its not to complicated to make the SQL builder and store it (or maybe it is complicated, but I already know that stuff, I couldn't say).

If you have only a few choices, I would go with that - DoCmd.OpenQuery and open pre-made queries. But if you wan't customizability to allow for fields you haven't even thought of yet, go with the SQL builder. In reality the 2 solutions are the same, just one is done with VBA.

-Nelson

 

[OPMCoordinator]

not quite...

close but no cigar.

the individual queries will not change over time. each query though, is likely to be very different to pull the information in the same format. one idea of storing the sql string in a table is for ease of sql maintenance (hopefully).

the intended end result is a report that graphs the query result (dataset) for each row in the table (query). if this works, i only need to build one report to show data from a myriad of sources.

the work is in the query design.

cheers
mike

 

[pono1]

No beef, guys. Do as you see best for your apps.

I appreciate the spirit of your idea because, of course, ultimately there will be only twelve people left in the world who can write code -- seven of whom will work for Microsoft. Everybody else will be relegated to using an ActiveX control or its future equivalent: Want to make an order entry database for your refrigerator repair business? Buy our ActiveX control and step through the wizards. Want to buy our ActiveX control using monthly payments? Buy their ActiveX control and install it on our server. Need help in making this decision? Buy our Decision Matrix... And so on. Independant-minded people and their ideas are often tossed aside in the name of standardization and convenience.

And yet, on another score, the less time I spend fighting through VBA and SQL syntax, the better my mood.

In that vein, I think the convenience of creating and saving queries with the Access query grid is an OK solution for most people. Fairly sophisticated queries can be created without knowing one iota of SQL. And if you believe what I read, saved queries execute more quickly than SQL strings created on the fly in code. Also, it can often make for more manageable code (easier to read and adjust after time passes and you've forgotten what you did) when you use a query name rather than a heavy-duty SQL string in a procedure. For example, of the following two code snippets, both performing the same task, I prefer the first.

Me.ListBoxName.RowSource = "QryLikeNameCus"

or...

Dim strSQL as SQL 

strSQL = "SELECT TblCus.cName AS [Customer Name]," & _
"TblCus.cStreet AS Street, " & _
"TblCus.cCity AS City, " & _
"TblCus.cState AS State, TblCus.cPcode AS Zip, " & _
"TblCus.cContact AS Contact, " & _
"TblCus.cPhone1 AS [Phone 1], " & _
"TblCus.cPhone2 AS [Phone 2], " & _
"TblCus.cCell AS Cell, " & _
"TblCus.cFax AS Fax, " & _
"TblCus.cWeb AS Webpage, " & _
"TblCus.cMapKey AS MapKey, " & _
"TblCus.Active, TblCus.CusID " & _
"FROM TblCus " & _
"GROUP BY TblCus.cName, TblCus.cStreet, " & _
"TblCus.cCity, " & _
"TblCus.cState, TblCus.cPcode, " & _
"TblCus.cContact, TblCus.cPhone1, " & _
"TblCus.cPhone2, TblCus.cCell, " & _
"TblCus.cFax, TblCus.cWeb, " & _
"TblCus.cMapKey, TblCus.Active, " & _
"TblCus.CusID, " & _
"TblCus.cType, " & _
"IIf([Forms]![FrmQry]![txtAct]<>2, " & _
"[Active]=[Forms]![FrmQry]![txtAct],True), " & _
"TblCus.Active " & _
"HAVING (((TblCus.cName) Like " & _
"[Forms]![FrmQry]![txtName] & "*") " & _
"AND ((TblCus.cPcode) Like 
"[Forms]![FrmQry]![txtZip] & "*") " & _
"AND ((TblCus.cType)=[forms]![frmQry]![cbotype]) " & _
"AND " & _
"etc.

me.listboxname.RowSource = StrSQL

You can use a saved query's name to dynamically fill a listbox (if that's how you want to display a resultset) as well as to fill a combo box or a form.

Putting this together with the ability to query by form usually provides for a fair amount of flexibility, enough for many Access apps, though, no, not all the flexibility of the Access query grid itself, which, from a distance, seems like the tool you are trying to emulate.

One man's lost art is another man's modern convenience.

Regards,
Tim