Calling a parameterised query with an array of parameter values

[neo]

Dear MSAccess Group users,

I have a dataset which has three columns:

NewDate Element Packets

There are multiple elements for each date for which I have packet
measurements.

I need:
- for each NewDate
the number of elements
the number of elements whose packet count is greater than >50
the number of elements whose packet count is greater than >100

and so on...

To do this, I created two queries:
n1: this has two columns: NewDate and Elem_Count which simply counts
the number of elements per day
n2: this also has two columns, NewDate and Tcount, and is a
parameterised query, with a parameter called threshold, which count the
number of elements with packet counts greather than threshold

Now, I have an umbrella query which selects from both Where
n1.NewDate=n2.newData

When I call n1n2, a dialog box asks the threhsold to use.

This is great but I would like to be able to call n2 istantiated with
an array of threshold parameter values. and for n1n2 to be called once
and to return one query result with number of columns equal to n+2
where n is the number of thresholds I use, and 2 is for NewDate and
elemcount.

also, the column names should be indexed to the array of thresholds
somehow.

How can I do this in SQl ?

Thanks,
Tolga

This is N1:
SELECT newdate AS n1, Count(element_name) AS elemcount
FROM Performance
GROUP BY newdate;

This is N2:
SELECT newdate AS n2, Count(element_name) AS thresholdcount
FROM Performance
WHERE packet>threshold
GROUP BY newdate;


This is N1N2:
SELECT n1.n1, n1.elemcount, n2.thresholdcount
FROM n1, n2
WHERE n1.n1=n2.n2;

So, I want something like:

PARAMETERS threshold = [50,100,150,200]
SELECT n1.n1, n1.elemcount, (for loop of names)
FROM n1, (for loop of queries instantiated with threshold[1],
threshold[2] and so on...(
WHERE n1.n1=(for loop of queries).n2


I am trying to not have more than three queries....

Thanks

 

[Douglas J Steele]

You can't do it using SQL.

However, that doesn't mean you have to have more than 3 queries.

You can modify the SQL of the existing query through VBA code for each of
the parameters in turn, then run your N1N2 after each change.