R
Rob Parker
I have a query which has suddenly started giving this error. It is
based on two other queries, each of which run OK. The query contains
4 left joins (I'm wanting to return a complete set of records from the
left query), and the left query has no nulls in any field.
The SQL of the query is:
SELECT qryStaffEffortFYWBSDivPeriod.FY,
qryStaffEffortFYWBSDivPeriod.WBS, qryStaffEffortFYWBSDivPeriod.DivID,
qryStaffEffortFYWBSDivPeriod.Period, Sum(qryStaffEffortOverhead.Hours)
AS TotalHours, Sum(qryStaffEffortOverhead.CostOH) AS TotalCostOH
FROM qryStaffEffortFYWBSDivPeriod LEFT JOIN qryStaffEffortOverhead ON
(qryStaffEffortFYWBSDivPeriod.Period = qryStaffEffortOverhead.Period)
AND (qryStaffEffortFYWBSDivPeriod.DivID =
qryStaffEffortOverhead.DivID) AND (qryStaffEffortFYWBSDivPeriod.WBS =
qryStaffEffortOverhead.WBS) AND (qryStaffEffortFYWBSDivPeriod.FY =
qryStaffEffortOverhead.FY)
GROUP BY qryStaffEffortFYWBSDivPeriod.FY,
qryStaffEffortFYWBSDivPeriod.WBS, qryStaffEffortFYWBSDivPeriod.DivID,
qryStaffEffortFYWBSDivPeriod.Period;
Removing the joins one by one shows that the problem is caused by the
join on the Period field. The query on the right does not have
records for every period. But that shouldn't matter - I'm using the
standard technique for returning a complete set of records from the
left table; the LEFT JOIN should take care of that. For what it's
worth, there are no records in the right query with data in three of
the join fields, and Null in the Period field.
What's the problem here?
TIA,
Rob
based on two other queries, each of which run OK. The query contains
4 left joins (I'm wanting to return a complete set of records from the
left query), and the left query has no nulls in any field.
The SQL of the query is:
SELECT qryStaffEffortFYWBSDivPeriod.FY,
qryStaffEffortFYWBSDivPeriod.WBS, qryStaffEffortFYWBSDivPeriod.DivID,
qryStaffEffortFYWBSDivPeriod.Period, Sum(qryStaffEffortOverhead.Hours)
AS TotalHours, Sum(qryStaffEffortOverhead.CostOH) AS TotalCostOH
FROM qryStaffEffortFYWBSDivPeriod LEFT JOIN qryStaffEffortOverhead ON
(qryStaffEffortFYWBSDivPeriod.Period = qryStaffEffortOverhead.Period)
AND (qryStaffEffortFYWBSDivPeriod.DivID =
qryStaffEffortOverhead.DivID) AND (qryStaffEffortFYWBSDivPeriod.WBS =
qryStaffEffortOverhead.WBS) AND (qryStaffEffortFYWBSDivPeriod.FY =
qryStaffEffortOverhead.FY)
GROUP BY qryStaffEffortFYWBSDivPeriod.FY,
qryStaffEffortFYWBSDivPeriod.WBS, qryStaffEffortFYWBSDivPeriod.DivID,
qryStaffEffortFYWBSDivPeriod.Period;
Removing the joins one by one shows that the problem is caused by the
join on the Period field. The query on the right does not have
records for every period. But that shouldn't matter - I'm using the
standard technique for returning a complete set of records from the
left table; the LEFT JOIN should take care of that. For what it's
worth, there are no records in the right query with data in three of
the join fields, and Null in the Period field.
What's the problem here?
TIA,
Rob