I do have two queries in a Cotton Processing Dababase. Seed generated is sold on day to day basis. We need to know the unsold seed lying in the Factories.
First Query: Seed Generated Query:
SELECT tblSeason.SeasonID, tblCenter.CenterID, tblCenter.CenterName, tblFactory.FactoryID, tblFactory.FactoryName, Sum(tblGinning.SeedRealised) AS SumOfSeedRealised, Nz(DLookUp("[TtlSold]","[SeedSoldQry]","[Season_ID]=" & [SeasonID] & "And[Center_ID]=" & [CenterID] & "And[Factory_ID]=" & [FactoryID] & ""),0) AS SeedSold, [SumOfSeedRealised]-[SeedSold] AS Unsold
FROM tblSeason INNER JOIN (tblVariety INNER JOIN (tblFactory INNER JOIN (tblCenter INNER JOIN (tblHeap INNER JOIN tblGinning ON tblHeap.HeapID = tblGinning.Heap_ID) ON tblCenter.CenterID = tblHeap.Center_ID) ON tblFactory.FactoryID = tblHeap.Factory_ID) ON tblVariety.VtyID = tblHeap.Variety_ID) ON tblSeason.SeasonID = tblHeap.Season_ID
GROUP BY tblSeason.SeasonID, tblCenter.CenterID, tblCenter.CenterName, tblFactory.FactoryID, tblFactory.FactoryName, Nz(DLookUp("[TtlSold]","[SeedSoldQry]","[Season_ID]=" & [SeasonID] & "And[Center_ID]=" & [CenterID] & "And[Factory_ID]=" & [FactoryID] & ""),0);
Second Query: Seed Sold Query:
SELECT GetSeasonID(DateNo([tblSeedSaleOfferDate]![SdOfferDate])) AS Season_ID, tblSeedSaleOfferDate.Center_ID, tblSeedSaleOfferDate.Factory_ID, Sum(tblSeedContract.SdqtySold) AS TtlSold
FROM tblSeedSaleOfferDate INNER JOIN tblSeedContract ON tblSeedSaleOfferDate.SdSaleOfferDtID = tblSeedContract.SdSaleOfferDt_ID
GROUP BY GetSeasonID(DateNo([tblSeedSaleOfferDate]![SdOfferDate])), tblSeedSaleOfferDate.Center_ID, tblSeedSaleOfferDate.Factory_ID;
The SeedSold in the First Query is through DLookup from the Second Query. It takes considerable time to run. I feel if the field could included in the First query as Subquery, running time could be reduced. I need help in constructing the Subquery.
Thanks.