Max subquery

[Guest]

I have been working with a query that reads from another query the max data
where that query's date is <= to the date of each item of my query.
It does work, but it is too slow!
How can I make the same thing mor efficient?

Thanks a lot, Lina

SELECT qry_Rslts_UnitsProductionForecastingTmp.IdPrdctByCmpny,
qry_Rslts_UnitsProductionForecastingTmp.Date,
qry_Rslts_UnitsProductionForecastingTmp.CumulativeQuantitySales,
(Select max(TempCumulativeQuantityProduction) as MAXQtity From
qry_Rslts_UnitsProductionForecastingTmpM where
qry_Rslts_UnitsProductionForecastingTmp.IdPrdctByCmpny=qry_Rslts_UnitsProductionForecastingTmpM.IdPrdctByCmpny
having
qry_Rslts_UnitsProductionForecastingTmp.Date>=qry_Rslts_UnitsProductionForecastingTmp.Date) AS CumulativeQuantityProduction
FROM qry_Rslts_UnitsProductionForecastingTmp;

 

[[MVP] S.Clark]

Any or all of the following:
1. Replace the = in the WHERE with an inner join.
2. Add indexes to the supporting tables
3. Save the subquery as a regular query
4. Write data, using action queries, to temp tables, then use the temp
tables in the query, instead of trying to do everything in one query.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

 

[John Spencer]

I would first try moving the having clause criteria into the where clause.
I also used "aliases" for your query names

SELECT Q.IdPrdctByCmpny,
Q.Date,
Q.CumulativeQuantitySales,
(Select Max(TempCumulativeQuantityProduction) as MAXQtity
From qry_Rslts_UnitsProductionForecastingTmpM as T
WHERE Q.IdPrdctByCmpny=T.IdPrdctByCmpny
AND Q.Date>=T.Date) AS CumulativeQuantityProduction
FROM qry_Rslts_UnitsProductionForecastingTmp As Q

 

[Guest]

Thanks a lot, I will try it

I would first try moving the having clause criteria into the where clause.
I also used "aliases" for your query names

SELECT Q.IdPrdctByCmpny,
Q.Date,
Q.CumulativeQuantitySales,
(Select Max(TempCumulativeQuantityProduction) as MAXQtity
From qry_Rslts_UnitsProductionForecastingTmpM as T
WHERE Q.IdPrdctByCmpny=T.IdPrdctByCmpny
AND Q.Date>=T.Date) AS CumulativeQuantityProduction
FROM qry_Rslts_UnitsProductionForecastingTmp As Q

 

[Guest]

Thanks a lot, I will try all of them

Any or all of the following:
1. Replace the = in the WHERE with an inner join.
2. Add indexes to the supporting tables
3. Save the subquery as a regular query
4. Write data, using action queries, to temp tables, then use the temp
tables in the query, instead of trying to do everything in one query.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html