Running Query with Not Equal to Criteria for Multiple Identifiers

  • Thread starter Thread starter Jeremy
  • Start date Start date
J

Jeremy

I am trying to run a query where I set the criteria to <>ID, but I
have about 1000 different IDs I want to run this for. In my SQL
statement below, the number 12490 that I have hard coded I would like
to make a flexible variable. Any ideas??? I would also like to save
the output, so hopefully a make-table or append query would work with
this solution. Since it will take a long-time to run, any ideas for
speed would be great as well.


I have four fields: Date, ID, USDRETURN, MKTVAL_OLD,

SELECT Data.DATE, Sum(Data.USDRETURN) AS SumOfUSDRETURN,
Sum(Data.MKTVAL_OLD) AS SumOfMKTVAL_OLD,
Sum([USDRETURN])/Sum([MKTVAL_OLD]) AS AggRet
FROM Data
WHERE (((Data.ID)<>12490))
GROUP BY Data.DATE;
 
It seems tome that you are calculating Sum([MKTVAL_OLD])
and Sum([USDRETURN]) for each record, unconditioanally.
When you calculate AggRet you say:

Sum([USDRETURN])/Sum([MKTVAL_OLD]) AS AggRet

This will yield same result for each record and will
calculate totals of all USDRETURN and all MKTVAL_OLD
each time. that is slowing you down.

Step 1: Try to write separate query, named qryTotalSum

SELECT Sum([USDRETURN]) AS U, Sum([MKTVAL_OLD]) AS M
FROM Data

This returns only 1 record. Add any WHERE conditions if
needed.

Step 2) Rewrite your original query, qrySum, to look like
this:



Step 3) Write another query which joins qrySum and
qryTotalSum. Since qryTotalSum returns only one record,
you can write SQL like this:

SELECT qrySum.SumOfUSDRETURN,qrySum.SumOfMKTVAL_OLD,
qryTotalSum.U, qryTotalSum.M,
qryTotalSum.U/qryTotalSum.M AS AggRet
FROM qrySum, qrySumTotal
SELECT Data.DATE, Sum(Data.USDRETURN) AS SumOfUSDRETURN,
Sum(Data.MKTVAL_OLD) AS SumOfMKTVAL_OLD,
FROM Data
WHERE (((Data.ID)<>12490))
GROUP BY Data.DATE;
The final query has all the fields you had in your
original one, and values used for calculating AggRet are
calculated only once, by the qryTotalSum.

As for flexibility in WHERE criteria, instead of hard
coding it, make ita parameter, like this:

SELECT Data.DATE, Sum(Data.USDRETURN) AS SumOfUSDRETURN,
Sum(Data.MKTVAL_OLD) AS SumOfMKTVAL_OLD,
FROM Data
WHERE (((Data.ID)<>[Please Enter ID:]))
GROUP BY Data.DATE;

Text withinn [] will appear on the screen when you try to
open the query. Then you simply type the value.


-----Original Message-----
I am trying to run a query where I set the criteria to
Click to expand...
have about 1000 different IDs I want to run this for. In my SQL
statement below, the number 12490 that I have hard coded I would like
to make a flexible variable. Any ideas??? I would also like to save
the output, so hopefully a make-table or append query would work with
this solution. Since it will take a long-time to run, any ideas for
speed would be great as well.


I have four fields: Date, ID, USDRETURN, MKTVAL_OLD,

SELECT Data.DATE, Sum(Data.USDRETURN) AS SumOfUSDRETURN,
Sum(Data.MKTVAL_OLD) AS SumOfMKTVAL_OLD,
Sum([USDRETURN])/Sum([MKTVAL_OLD]) AS AggRet
FROM Data
WHERE (((Data.ID)<>12490))
GROUP BY Data.DATE;
.
Click to expand...
 
Back
Top