Multiple Queries do Same Functions

[shepkrm]

Hello,

I'm looking for a HIGH LEVEL answer to my question...

I am currently working in a database that calculates budget information from several fields in several tables and user input forms. The only way I know how to perform the functionality that I need is to create multiple queries that do the same thing over and over with slight changes in the criteria or the tables they pull from.

I am wondering if there is an easier way to do this.

Thanks!

 

[rac2]

Slight changes in queries such as optional search fields can be handled by writing a more complicated SQL statement that uses indicator variables and IIf() functions. This is not too difficult but it works for rather limited situations. I think that retrieving data from alternative tables definitely will require a different query for every table.

The notion of dynamic queries rests on the use of a procedural language that builds different queries based on the data received from a form. In Access this would be VBA, Visual Basic for Applications, or whatever the current Microsoft offering might be. If your application is web based, then use the scripting language to build a string that is submitted as the SQL statement.

/* Maybe a criterion is optional when the menu selection is "Any". */
SELECT good, things
FROM CurrentBudget
WHERE 
            ( @Dept = "Any" OR Department = @Dept )
     AND
           TxDate BETWEEN @dtBegin AND @dtEnd

(The symbols that begin with @ are the values supplied by the form.)

/* Maybe a criterion is optional unless a checkbox for a field is selected. */
SELECT good, things
FROM CurrentBudget
WHERE 
            ( @DeptSearch = False OR Department = @Dept )
     AND
           TxDate BETWEEN @dtBegin AND @dtEnd

Here the form has a field to specify that a criterion should be used in the query. The value of that field, @DeptSearch, is used in the query.

In these examples, the first condition in the WHERE clause is always true if we dont want to use the field as a criterion. If that condition is false then only the rows with the specified value are retrieved.

/* Build a SQL statement based of the form data, then submit it. */
Dim strSQL
strSQL = "SELECT good, things FROM "
strSQL = strSQL & formTableName
strSQL = strSQL & " WHERE 1=1 "
If DeptSearch = True Then
   strSQL = strSQL & " AND Department = formDept"
End If
strSQL = strSQL & " AND TxDate BETWEEN #" & formDtBegin "# AND #" & formDtEnd & "#"

Here the formDept variables have the values provided by the form. In this case the form could specify alternative tables and the code would build an appropriate query.

Hope this gives you a couple of general ideas. The code does not run.

 

[Golom]

Unfortunately, any answer to this must needs be equally HIGH LEVEL ... and probably not terribly useful.

Telling you to do it in code for example doesn't advance the cause very much. Similarly build your queries dynamically is probably unhelpful.

Some description of your problem with a bit more detail may allow us to offer more constructive suggestions.

 

[shepkrm]

Thanks, rac2. Not sure that forms are the answer...I want this to be a one button process.

Golom, here's some detail of what I'm doing. Let me know if you need more.

1. calculate the total number of bytes from all divisions (sum bytes from table) - Total_Bytes query
2. calculate the total number of bytes from individual divisions (sum bytes from table where div = whatever) - Total_Div_Bytes query
3. divide divisional total by total to get percent of bytes - Div_Percentage query
4. multiply percent of bytes by estimate (user input) to get allocation - Div_Allocation query
5. combine all allocations in a report

as there are 5 divisions and i am running 3 queries for each allocation, i have produced 15 queries plus the 1 to calculate total number of bytes.

I hope this helps you help me.

Thanks again!