Results 1 to 9 of 9
  1. #1
    tbrown1110 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4

    Union query optimization and form criteria

    I have a large union query that I wrote a few years ago and it performs exactly as I want, however I haven't done much with the database since I first wrote it and don't totally understand it anymore . I am wondering if it could be optimized at all, it runs pretty quickly already, but thought I would ask. My other question is in regards to displaying the result on a form. I am currently using this formula:

    =IIf([lstQuoteName]="","",DLookUp("[SumofQuoteTotal]","qryQuoteTotal","[QuoteID]=" & [lstQuoteName]))

    in a text box but it works really slowly. I am wondering how I can put the criteria in the query and if that might speed it up?

    Here is the query:

    SELECT QuoteID, Sum(QuoteTotal) AS SumOfQuoteTotal
    FROM (SELECT QuoteID, Sum(NonMUTotal) AS QuoteTotal

    FROM (SELECT tblQuoteLineItems.QuoteID, Sum(tblQuoteLineItems.[LineTotal]) AS NonMUTotal, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    FROM tblSettings, tblQuotes INNER JOIN tblQuoteLineItems ON tblQuotes.QuoteID = tblQuoteLineItems.QuoteID
    GROUP BY tblQuoteLineItems.QuoteID, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    HAVING (((tblQuoteLineItems.ApplyMarkup)=No) AND ((tblQuoteLineItems.Taxable)=No))

    UNION ALL

    SELECT tblQuoteLineItems.QuoteID, Sum([LineTotal]*(1+[defaultproducttaxrate])) AS NonMUTotal, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    FROM tblSettings, tblQuotes INNER JOIN tblQuoteLineItems ON tblQuotes.QuoteID = tblQuoteLineItems.QuoteID
    GROUP BY tblQuoteLineItems.QuoteID, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    HAVING (((tblQuoteLineItems.ApplyMarkup)=No) AND ((tblQuoteLineItems.Taxable)=Yes)))
    GROUP BY QuoteID

    UNION ALL

    SELECT QuoteID, Sum(MUTotal) AS QuoteTotal
    FROM (SELECT tblQuoteLineItems.QuoteID, Sum([LineTotal]*(1+[Markup])) AS MUTotal, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    FROM tblSettings, tblQuotes INNER JOIN tblQuoteLineItems ON tblQuotes.QuoteID = tblQuoteLineItems.QuoteID
    GROUP BY tblQuoteLineItems.QuoteID, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    HAVING (((tblQuoteLineItems.ApplyMarkup)=Yes) AND ((tblQuoteLineItems.Taxable)=No))

    UNION ALL

    SELECT tblQuoteLineItems.QuoteID, Sum(([LineTotal]*(1+[defaultproducttaxrate]))*(1+[Markup])) AS MUTotal, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    FROM tblSettings, tblQuotes INNER JOIN tblQuoteLineItems ON tblQuotes.QuoteID = tblQuoteLineItems.QuoteID
    GROUP BY tblQuoteLineItems.QuoteID, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    HAVING (((tblQuoteLineItems.ApplyMarkup)=Yes) AND ((tblQuoteLineItems.Taxable)=Yes)))

    GROUP BY QuoteID
    ) AS [%$##@_Alias]


    GROUP BY QuoteID;

    Thanks!!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Domain aggregates do have slow performance in query and textbox. Doubt that expression can be faster. Is textbox on form or report?

    Want to provide sample data? Can attach database by following instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    tbrown1110 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    All of my tables are linked Sharepoint tables. I converted them to local tables in order to be able to attach the db, and it performs much quicker now. So it looks like the reason its so slow is because of the sharepoint tables. The query itself performs quite quickly, its just when I use the DLookup() on the form that it acts slowly. Is there another way that I could show that info without using the Dlookup? I've attached the db. I know there are probably a hundred other areas that improvements can be made, I just don't know how to make that happen, and since it works for us haven't done it :-)
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Which form?

    There is no UNION query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    tbrown1110 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    It populates the Quote Total Text box on frmMain.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why is qryQuoteLaborTotal filtered to ProductID 471? No records return. I removed that field from query for following.

    One possible approach may be to bind the main form to a query that has the aggregate data then textboxes can be bound to fields to eliminate DLookup calls. Synchronize main and subform with Master/Child Links properties set to QuoteID. Complication here is the subform is filtered by QuoteID and JobID which doesn't really make sense since filtering by QuoteID already limits to a specific job. I removed filter criteria.

    SELECT qryQuoteTotal.QuoteID, qryQuoteTotal.SumOfQuoteTotal, qryQuoteLaborTotal.LaborTotal
    FROM qryQuoteLaborTotal INNER JOIN qryQuoteTotal ON qryQuoteLaborTotal.QuoteID = qryQuoteTotal.QuoteID;

    There are 364 records in tblQuotes but qryQuoteLaborTotal and qryQuoteTotal each have 362. QuoteID 82 and 368 do not appear in the queries.

    Trying to replicate data in qryQuoteTotal with a simple aggregate and not getting same results. Consider:

    SELECT tblQuotes.QuoteID, Sum([Quantity]*[UnitPrice]+[quantity]*[UnitPrice]*IIf([Taxable],0.07125,0)+[quantity]*[UnitPrice]*IIf([ApplyMarkup],[Markup],0)) AS QuoteTotal
    FROM tblQuotes LEFT JOIN tblQuoteLineItems ON tblQuotes.QuoteID = tblQuoteLineItems.QuoteID
    GROUP BY tblQuotes.QuoteID;

    qryQuoteTotal returns 49357.1296875 for Quote1
    my query returns 48346.003125 for Quote1
    Last edited by June7; 01-21-2019 at 09:28 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    tbrown1110 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    qryQuoteLaborTotal should be filtered to ProductID 469 instead of 471. Looks like I deleted a couple products without realizing that I had that criteria. I need to address that too I guess.

    I can't do an aggregate query for both as sometime as there isn't always a product 469 in the quote, so qryQuoteLaborTotal won't always return a result. I'm ok continuing to use that dlookup as that one works well and quickly.

    I'm now trying to just add a subform to the main form that has a record source of qryQuoteTotal and filter that based on the QuoteID from lstQuoteName. It works, but when I try to requery it when selecting a new customer or job name it gives me a #Name error that I can't figure out.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Okay, but as I show, a UNION for qryQuoteTotal is not needed to return (nearly) same data. Which is the accurate result?

    If you want to filter aggregate data then probably need WHERE clause with parameters that reference form controls.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    The main query looks like:
    Code:
    
    SELECT QuoteID, Sum(QuoteTotal) AS SumOfQuoteTotal
    FROM (QueryFromYourUnionQuery)
    GROUP BY QuoteID;
    


    Advice number 1 - create a saved query from your Union query. Like:
    Code:
    
    YourUnionQuery =
    SELECT tblQuoteLineItems.QuoteID, Sum(tblQuoteLineItems.[LineTotal]) AS NonMUTotal, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    FROM tblSettings, tblQuotes INNER JOIN tblQuoteLineItems ON tblQuotes.QuoteID = tblQuoteLineItems.QuoteID
    GROUP BY tblQuoteLineItems.QuoteID, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    HAVING (((tblQuoteLineItems.ApplyMarkup)=No) AND ((tblQuoteLineItems.Taxable)=No))
     
    UNION ALL
     
    SELECT tblQuoteLineItems.QuoteID, Sum([LineTotal]*(1+[defaultproducttaxrate])) AS NonMUTotal, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    FROM tblSettings, tblQuotes INNER JOIN tblQuoteLineItems ON tblQuotes.QuoteID = tblQuoteLineItems.QuoteID
    GROUP BY tblQuoteLineItems.QuoteID, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    HAVING (((tblQuoteLineItems.ApplyMarkup)=No) AND ((tblQuoteLineItems.Taxable)=Yes)))
    GROUP BY QuoteID
     
    UNION ALL
     
    SELECT QuoteID, Sum(MUTotal) AS QuoteTotal
    FROM (SELECT tblQuoteLineItems.QuoteID, Sum([LineTotal]*(1+[Markup])) AS MUTotal, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    FROM tblSettings, tblQuotes INNER JOIN tblQuoteLineItems ON tblQuotes.QuoteID = tblQuoteLineItems.QuoteID
    GROUP BY tblQuoteLineItems.QuoteID, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    HAVING (((tblQuoteLineItems.ApplyMarkup)=Yes) AND ((tblQuoteLineItems.Taxable)=No))
    
    UNION ALL
     
    SELECT tblQuoteLineItems.QuoteID, Sum(([LineTotal]*(1+[defaultproducttaxrate]))*(1+[Markup])) AS MUTotal, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    FROM tblSettings, tblQuotes INNER JOIN tblQuoteLineItems ON tblQuotes.QuoteID = tblQuoteLineItems.QuoteID
    GROUP BY tblQuoteLineItems.QuoteID, tblQuoteLineItems.ApplyMarkup, tblQuoteLineItems.Taxable
    HAVING (((tblQuoteLineItems.ApplyMarkup)=Yes) AND ((tblQuoteLineItems.Taxable)=Yes)))
    


    Now your main query (make it saved one too - but probably you have done this anyway) will look like:
    Code:
    
    qryQuoteTotal = 
    SELECT QuoteID, Sum(QuoteTotal) AS SumOfQuoteTotal
     FROM YourUnionQuery
    GROUP BY QuoteID;
    
    The formula in text box is returning a string, because one possible return value of IIF() is empty string. And poor Access is desperately looking for number ""
    Advice number 2: replace "" for return value with 0
    Code:
    =IIf([lstQuoteName]="",0,DLookUp("[SumofQuoteTotal]","qryQuoteTotal","[QuoteID]=" & [lstQuoteName]))
    When the formula in text box is working slowly anyway, and the data returned by query must not be "at-the-current-time", then you can have a table for it's data. E.g. at nighttime a scheduled task opens the database, and runs a script which deletes all data from table, and inserts new data from query. Your tex-box Dlookup() searches table instead of query.

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

Similar Threads

  1. Replies: 4
    Last Post: 08-09-2017, 12:06 PM
  2. SQL Querry Optimization in MS Access
    By shabar in forum Queries
    Replies: 1
    Last Post: 02-12-2013, 01:38 AM
  3. VBA Optimization - Forms in Object Variables
    By GeekInOhio in forum Programming
    Replies: 1
    Last Post: 09-18-2012, 02:28 PM
  4. Criteria Union Error
    By Jerseynjphillypa in forum Queries
    Replies: 2
    Last Post: 04-30-2012, 12:04 PM
  5. Query Optimization Inquiry
    By Nobody in forum Queries
    Replies: 1
    Last Post: 07-29-2010, 08:53 AM

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