Results 1 to 3 of 3
  1. #1
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80

    Convert Union Query to non-union help

    I have been attempting to make editable queriest and datasheet forms. The problem I have is the business network is slow and the complex queries really run slow. My queries are used as alerts for when action is needed. Below is one of the simple versions of this and I wondered how one would turn this in to a non-union query (or if it can be). Some of the more complex involve 20 or so unions in a single query. From looking at it I cannot see how to run this on a single query but if I am missing something obvious I am more than willing to listn
    Code:
    SELECT DISTINCTROW "Aged Consult" AS Alert, Scheduler_Sheet.ConsultID, Scheduler_Sheet.LastName, Scheduler_Sheet.FirstName, Scheduler_Sheet.SSN, Scheduler_Sheet.Urgency, Scheduler_Sheet.Program, Scheduler_Sheet.Clinic, Scheduler_Sheet.LastAction, Scheduler_Sheet.DateofAction, Scheduler_Sheet.AppointmentDate, Scheduler_Sheet.RNComment, Scheduler_Sheet.SchedulerComment, Scheduler_Sheet.RNActionNeeded, Scheduler_Sheet.DateOfConsult, Scheduler_Sheet.ConsultNumber
    FROM tblEmployeeFiles INNER JOIN (Scheduler_Sheet INNER JOIN tblSchedulerClinics ON Scheduler_Sheet.Clinic = tblSchedulerClinics.Clinic) ON tblEmployeeFiles.AlertTeam = tblSchedulerClinics.AlertTeam
    WHERE (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.AppointmentDate)<Date()-2) AND ((Scheduler_Sheet.DateOfConsult)<=Date()-74) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.AppointmentDate) Is Null) AND ((Scheduler_Sheet.DateOfConsult)<=Date()-74) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.LastAction)="Records Received") AND ((Scheduler_Sheet.DateofAction)<Date()-6) AND ((Scheduler_Sheet.DateOfConsult)<=Date()-74) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.LastAction)="Records Requested") AND ((Scheduler_Sheet.DateofAction)<Date()-6) AND ((Scheduler_Sheet.DateOfConsult)<=Date()-74) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser]))
    UNION SELECT DISTINCTROW "Red Zone, Attempt to Close" AS Alert, Scheduler_Sheet.ConsultID, Scheduler_Sheet.LastName, Scheduler_Sheet.FirstName, Scheduler_Sheet.SSN, Scheduler_Sheet.Urgency, Scheduler_Sheet.Program, Scheduler_Sheet.Clinic, Scheduler_Sheet.LastAction, Scheduler_Sheet.DateofAction, Scheduler_Sheet.AppointmentDate, Scheduler_Sheet.RNComment, Scheduler_Sheet.SchedulerComment, Scheduler_Sheet.RNActionNeeded, Scheduler_Sheet.DateOfConsult, Scheduler_Sheet.ConsultNumber
    FROM tblEmployeeFiles INNER JOIN (Scheduler_Sheet INNER JOIN tblSchedulerClinics ON Scheduler_Sheet.Clinic = tblSchedulerClinics.Clinic) ON tblEmployeeFiles.AlertTeam = tblSchedulerClinics.AlertTeam
    WHERE (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.AppointmentDate)<Date()-2) AND ((Scheduler_Sheet.DateOfConsult)<Date()-59 And (Scheduler_Sheet.DateOfConsult)>Date()-75) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.AppointmentDate) Is Null) AND ((Scheduler_Sheet.DateOfConsult)<Date()-59 And (Scheduler_Sheet.DateOfConsult)>Date()-75) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.LastAction)="Records Requested") AND ((Scheduler_Sheet.DateofAction)<Date()-6) AND ((Scheduler_Sheet.DateOfConsult)<Date()-59 And (Scheduler_Sheet.DateOfConsult)>Date()-75) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.LastAction)="Records Received") AND ((Scheduler_Sheet.DateofAction)<Date()-6) AND ((Scheduler_Sheet.DateOfConsult)<Date()-59 And (Scheduler_Sheet.DateOfConsult)>Date()-75) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser]));


  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    breakup the union query into the individual queries.
    run them all in a macro BUT instead of select queries, make them append queries that write to a 'temp' table.
    display the 'temp' table at the end.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    are all the fields used in the joins and criteria indexed? Doesn't matter about the boolean fields, indexing will not be effective for them

    Union queries are often a sign of poor table design. From the example provided, they both use the same FROM clause, so the criteria can be combined with an OR into one query (subject to doing something with your Alert column). I haven't gone through it in detail, but if you then find you have common criteria for both 'sides' of the OR, create a query on this first to reduce the returned recordset height. I've only done the first few and you can see there is a lot of commonality - so all these comparisons are being done twice, which simplistically means it takes twice as long to run. So far as I can see, create a query based on the criteria in red which appears to me to be common, then a new query based on that query for the rest of it.

    1st query element
    WHERE (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.AppointmentDate)<Date()-2) AND ((Scheduler_Sheet.DateOfConsult)<=Date()-74) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.AppointmentDate) Is Null) AND ((Scheduler_Sheet.DateOfConsult)<=Date()-74) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.LastAction)="Records Received") AND ((Scheduler_Sheet.DateofAction)<Date()-6) AND ((Scheduler_Sheet.DateOfConsult)<=Date()-74) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.LastAction)="Records Requested") AND ((Scheduler_Sheet.DateofAction)<Date()-6) AND ((Scheduler_Sheet.DateOfConsult)<=Date()-74) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser]))

    2nd query element
    WHERE (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.AppointmentDate)<Date()-2) AND ((Scheduler_Sheet.DateOfConsult)<Date()-59 And (Scheduler_Sheet.DateOfConsult)>Date()-75) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.AppointmentDate) Is Null) AND ((Scheduler_Sheet.DateOfConsult)<Date()-59 And (Scheduler_Sheet.DateOfConsult)>Date()-75) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.LastAction)="Records Requested") AND ((Scheduler_Sheet.DateofAction)<Date()-6) AND ((Scheduler_Sheet.DateOfConsult)<Date()-59 And (Scheduler_Sheet.DateOfConsult)>Date()-75) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser])) OR (((Scheduler_Sheet.Program)<>"Choice") AND ((Scheduler_Sheet.LastAction)="Records Received") AND ((Scheduler_Sheet.DateofAction)<Date()-6) AND ((Scheduler_Sheet.DateOfConsult)<Date()-59 And (Scheduler_Sheet.DateOfConsult)>Date()-75) AND ((Left([Scheduler_Sheet].[LastName],1)) Between Left([tblEmployeeFiles].[AlphaSplit],1) And Right([tblEmployeeFiles].[AlphaSplit],1)) AND ((Scheduler_Sheet.Complete)=False) AND ((Scheduler_Sheet.DC)=False) AND ((tblEmployeeFiles.EmployeeID)=[Forms]![frmLogin]![cboUser]));

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

Similar Threads

  1. Replies: 4
    Last Post: 12-20-2015, 02:35 PM
  2. Union Query
    By jo15765 in forum Queries
    Replies: 1
    Last Post: 02-04-2014, 06:35 PM
  3. Sum in Union Query
    By beckysright in forum Queries
    Replies: 3
    Last Post: 07-23-2013, 12:06 PM
  4. Please help me out with union query
    By radicrains in forum Access
    Replies: 1
    Last Post: 10-29-2010, 01:48 AM
  5. Replies: 0
    Last Post: 12-28-2008, 01:56 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