I am getting an error that the expression in my Access Query is too complex

Question

 SELECT dbo_job.job_date, 
        dbo_job.job, 
        dbo_job.suffix, 
        dbo_job.item, 
        qry_SumCost.SumMatlPlan, 
        qry_SumCost.SumMatlAct, 
        ([qty_released]*[fixovhd_rate])/[pcs_per_lbr_hr] AS lbr_cost_plan,       
        qry_LaborHours.fixovhd_t_lbr, 
        [fixovhd_t_lbr]/[qty_complete] AS [FG Actual Price]
  FROM  qry_SumCost 
  INNER JOIN (dbo_job 
      INNER JOIN qry_LaborHours 
          ON (dbo_job.job = qry_LaborHours.job) 
          AND (dbo_job.suffix = qry_LaborHours.suffix)) 
       ON (qry_SumCost.job_date = dbo_job.job_date) 
       AND (qry_SumCost.job = dbo_job.job) 
       AND (qry_SumCost.suffix = dbo_job.suffix)
   GROUP BY dbo_job.job_date, 
            dbo_job.job, 
            dbo_job.suffix, 
            dbo_job.item, 
            qry_SumCost.SumMatlPlan, 
            qry_SumCost.SumMatlAct, 
            ([qty_released]*[fixovhd_rate])/[pcs_per_lbr_hr],                
            qry_LaborHours.fixovhd_t_lbr, 
            [fixovhd_t_lbr]/[qty_complete]
     HAVING (((dbo_job.job_date) Between [Forms]![MainForm]![Text2] 
                                        And [Forms]![MainForm]![Text6]) 
              AND ((dbo_job.job) Like "BM0*" 
                  Or (dbo_job.job)     Like "WT0*"));

When I remove the expression [fixovhd_t_lbr]/qty_complete] it works fine, but when I include it it gives me the error "This expression is typed incorrectly, or its is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables." I don't see how this could be considered complex. Both of the elements of that expression are directly from two different tables and are NOT expressions themselves (in case anyone was wondering). I am positive it is due to this because by process of elimination I stumbled upon this being the problem.

@Alan Stallwood thanks for the edit, couldn't figure out how to do that haha... — DarthVoid, Apr 20 '15 at 13:30

score 2 · Accepted Answer · answered Apr 20 '15 at 13:38

Your query doesn't make sense. You are using group by, but have no aggregation functions. This means that the conditions in the having clause can be moved to a where clause and you can use select distinct instead.

So, perhaps this will fix your problem:

 SELECT DISTINCT dbo_job.job_date, 
        dbo_job.job, 
        dbo_job.suffix, 
        dbo_job.item, 
        qry_SumCost.SumMatlPlan, 
        qry_SumCost.SumMatlAct, 
        ([qty_released]*[fixovhd_rate])/[pcs_per_lbr_hr] AS lbr_cost_plan,       
        qry_LaborHours.fixovhd_t_lbr, 
        [fixovhd_t_lbr]/[qty_complete] AS [FG Actual Price]
  FROM  qry_SumCost INNER JOIN
        (dbo_job INNER JOIN
         qry_LaborHours 
         ON (dbo_job.job = qry_LaborHours.job) AND
            (dbo_job.suffix = qry_LaborHours.suffix)
       ) 
       ON (qry_SumCost.job_date = dbo_job.job_date) AND
          (qry_SumCost.job = dbo_job.job) AND
          (qry_SumCost.suffix = dbo_job.suffix)
WHERE (((dbo_job.job_date) Between [Forms]![MainForm]![Text2] And
                                   [Forms]![MainForm]![Text6]) And
        ((dbo_job.job) Like "BM0*" Or (dbo_job.job) Like "WT0*"));

I am getting an error that the expression in my Access Query is too complex

1 Answers1