reduce queries by using code

[anil]

hi all
I have around 30 queries for one particular report due to each
parameter having different formula and format.I will try to explain and
needs advice and help if it will be better to convert it into code and
in which way.

first is parameterized query which is based on many tables and gets
'Date' from the form and other filters such as type = 'R' and model.
In next query it filters for required 9 parameters and formats as per
in full format(thm in trihalomethanes).

then I have to get 3 queries for EACH parameter as requires total
Results,Number of pass results ,Number of fail results.% results passed
- for each monthly and yearly results .

Some queries are also using the few functions e.g. 95% percentile
,stddev , which is written in code.
In yearly results I need the results for last 1 year and also calculate
total Results,Number of pass results ,Number of fail results.% results
passed.

Then the another problem is each parameter has to be expressed in
different decimal place format as parameter 1& 4 in 3 decimal
places,parameter 2 in 1 decimal places and remaining in 2 decimal
places.

I have made union query in last to combine all the parameters fro
monthly and yearly results.

In VBA I have done DAO code to get parameterized query as recordset and
then filter it.But i don't know how to get each recordset for
particular formula for each parameter and decimal places like the query
for group by and max,min or count.
e.g " select A,B,---Count(R)max(X),format(min(Z),"0.00") from query P ;

Since some of these queries also become the base for other queries
like general board results etc,so I am in doubt of moving towards code
but 30 queries are driving me crazy.

I hope I have explained properly
thanks
anil

 

[onedaywhen]

each parameter has to be expressed in
different decimal place format as parameter 1& 4 in 3 decimal
places,parameter 2 in 1 decimal places and remaining in 2 decimal
places.

I hope I have explained properly

I'm not sure I got the question.

If you are asking about the data type of parameters then yes, you can
declare them to be the appropriate number of decimal places. Note that
the DECIMAL type exhibits truncation (the following is 'ADO' flavour,
to take advantage of default parameter values):

CREATE TABLE Test (
dec_col1 DECIMAL(18, 3) DEFAULT 0.000 NOT NULL,
dec_col2 DECIMAL(17, 2) DEFAULT 0.00 NOT NULL,
dec_col3 DECIMAL(16, 1) DEFAULT 0.0 NOT NULL,
dec_col4 DECIMAL(18, 3) DEFAULT 0.000 NOT NULL,
dec_col5 DECIMAL(17, 2) DEFAULT 0.00 NOT NULL
)
;
CREATE PROCEDURE ProcTest (
arg_col1 DECIMAL(18, 3) = 0.000,
arg_col2 DECIMAL(17, 2) = 0.00,
arg_col3 DECIMAL(16, 1) = 0.0,
arg_col4 DECIMAL(18, 3) = 0.000,
arg_col5 DECIMAL(17, 2) = 0.00
) AS
INSERT INTO Test (dec_col1, dec_col2,
dec_col3, dec_col4, dec_col5)
VALUES (arg_col1, arg_col2,
arg_col3, arg_col4, arg_col5)
;
EXECUTE ProcTest 123.456, 123.456,
123.456, 123.456, 123.456
;

If (baker's) rounding, rather than truncation, is required then declare
an additional decimal place (e.g. arg_col1 DECIMAL(18, 3) etc).

Alternatively, if you are asking whether the number of decimal places
for formatting purposes can be passed as a parameter, again the answer
is yes e.g.

CREATE PROCEDURE ProcTest2 (
arg_decimal_places_amount INTEGER = 2
) AS
SELECT FORMAT(T1.dec_col1, DT1.decimal_places_format),
FORMAT(T1.dec_col2, DT1.decimal_places_format),
FORMAT(T1.dec_col3, DT1.decimal_places_format),
FORMAT(T1.dec_col4, DT1.decimal_places_format),
FORMAT(T1.dec_col5, DT1.decimal_places_format)
FROM Test AS T1, (
SELECT DISTINCT SWITCH(
arg_decimal_places_amount = 2, '0.00',
arg_decimal_places_amount = 3, '0.000',
arg_decimal_places_amount = 1, '0.0'
) AS decimal_places_format
FROM Test AS T2
) AS DT1
WHERE arg_decimal_places_amount BETWEEN 1 AND 3;

Jamie.

--

 

[anil]

Thanks for your help
Atleast you solved my one problem and it helped in other module.

Now to old problem see I have one query built on other,say in first
query I filter for 9 parameters from 95 parameters and

then calculate min,max,mean(95% in some parameter and function in some
parameter) for each seperate parameter - monthly and yearly as well.

Then i combine Both monthly and yearly query as
Name,date,Mmin,Mmax,Mmean,Ymean for each parameter.
Then i have used union query to combine all parameters(which I can
reduce by use of ur functions).

What I was mainly concerned is that if i get first query as recordset
in VBAcode then I am not able to use the query on it so as to filter
for each parameter and then combine all of them

can I do it anyhow
thanks
anil