IN clause and subquery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a huge database (20 mil records) behind my front-end Access db.
Trying to do a make table to narrow the down the data before running queries
and reports. If we manually enter in the criteria - (pointID) in a query it
is superfast. When I use the subquery it takes forever. The thought is to
have
the make table query select points from the ViewPoints table using an IN
select but instead of a subquery, somehow make it generate a list of values
(i.e. 4759, 4755, xxx, yyy, zzz).

Here is my current code.
SELECT FlowData.* INTO Flowdata_TempTable
FROM FlowData
WHERE (((FlowData.PointID) In (SELECT ViewPoints.PointID
FROM ViewPoints
WHERE (((ViewPoints.ViewID)=[Forms]![AA_MainMenu]![SelectView]))
)) AND ((FlowData.MeasurementDate) Between [Forms]![AA_MainMenu]![FromDate]
And [Forms]![AA_MainMenu]![ToDate]));

Can someone please help?
 
You need eithe a LEFT JOIN or a RIGHT JOIN, depending on how you want to
retrieve the data. Here is an example using a LEFT JOIN. The LEFT JOIN will
include all data from the Left (first) table and only data from the
right(second) table where the criteria in the JOIN state are true. I think
that is what you are trying to do.

SELECT monsterplusswcap.CVCN, monsterplusswcap.ActiveInactive,
monsterplusswcap.Monster, monsterplusswcap.MActivity,
monsterplusswcap.AuthorizedAmount, monsterplusswcap.SumOfSumOfSWCAPSEL,
monsterplusswcap.SumOfSumOfSWCAPSUP, monsterplusswcap.SumOfSumOfSWCAPPSOL,
monsterplusswcap.SumOfSumOfSWCAPSTRU, monsterplusswcap.SumOfSumOfSWCAPODCB,
monsterplusswcap.SumOfSumOfSWCAPCON, monsterplusswcap.TotalSWCAP,
monsterhwcap.SumOfSumOfHWCAPSEL, monsterhwcap.SumOfSumOfHWCAPSUP,
monsterhwcap.SumOfSumOfHWCAPPSOLL, monsterhwcap.SumOfSumOfHWCAPSTRU,
monsterhwcap.SumOfSumOfHWCAPODCB, monsterhwcap.SumOfSumOfHWCAPCON,
monsterhwcap.TotalHWCAP
FROM monsterplusswcap LEFT JOIN monsterhwcap ON (monsterplusswcap.Monster =
monsterhwcap.Monster) AND (monsterplusswcap.MActivity =
monsterhwcap.MActivity);
 
Back
Top