Results 1 to 4 of 4
  1. #1
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192

    Subquery

    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.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Do NOT use Dlookup in a query. It is slow.
    queries ARE the Dlookup. Join the Dlookup table into the query.

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The simplest route would be to Join your second query to the first query (after removing the dlookup) in another 3rd query.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Alex Motilal is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2008
    Location
    Coimbatore, India
    Posts
    192
    Soleved by way of Subquery:

    SELECT tblSeason.SeasonID, tblSeason.Season, tblCenter.CenterID, tblCenter.CenterName, tblCenter.CenterAbriviation, tblFactory.FactoryID, tblFactory.FactoryName, Sum(tblGinning.SeedRealised) AS SumOfSeedRealised, Seed_Sold_Qry.TtlSold, [SumOfSeedRealised]-[TtlSold] AS BalanceReadySeed
    FROM (tblSeason 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 tblSeason.SeasonID = tblHeap.Season_ID) INNER JOIN (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) AS Seed_Sold_Qry ON (tblHeap.Factory_ID = Seed_Sold_Qry.Factory_ID) AND (tblHeap.Center_ID = Seed_Sold_Qry.Center_ID) AND (tblHeap.Season_ID = Seed_Sold_Qry.Season_ID)
    GROUP BY tblSeason.SeasonID, tblSeason.Season, tblCenter.CenterID, tblCenter.CenterName, tblCenter.CenterAbriviation, tblFactory.FactoryID, tblFactory.FactoryName, Seed_Sold_Qry.TtlSold
    HAVING (((tblSeason.SeasonID)=[Forms]![SelectSeasonCenter]![cmbSeason]) AND ((tblCenter.CenterID)=[Forms]![SelectSeasonCenter]![cmbCenter]));

    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. A subquery inside a subquery
    By tbjmobile@gmail.com in forum Queries
    Replies: 15
    Last Post: 04-22-2018, 11:48 AM
  2. Subquery Help
    By MTSPEER in forum Queries
    Replies: 1
    Last Post: 12-05-2017, 01:57 PM
  3. Subquery Help
    By MTSPEER in forum Queries
    Replies: 1
    Last Post: 06-05-2017, 09:54 AM
  4. Subquery sum?
    By anemoskkk in forum Access
    Replies: 0
    Last Post: 04-29-2011, 12:36 PM
  5. Subquery
    By combine21 in forum Queries
    Replies: 2
    Last Post: 09-17-2010, 04:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums