My SQL will not work in one of my queries. I keep getting "must contain 1 table or query". Bit I do list the table... Any ideas what is wrong with the SQL below?


INSERT into xxxxx (Project, costType, jobCharge, ResourceDesc, [upper/lower], Cat, SumOfhours, SumOfcost, SumOfoverhead) SELECT projectData.Project, projectData.costType, wbsData.jobCharge, resourceCodes.ResourceDesc, resourceCodes.[upper/lower], IIf([costtype]="eac",IIf([fiscaldate]="200809","CM ETC","na"),IIf([costtype]="actual",IIf([fiscaldate]="200809","CM Act","na"),"na")) AS Cat, Sum(projectData.hours) AS SumOfhours, Sum(projectData.cost) AS SumOfcost, Sum(projectData.overhead) AS SumOfoverheadFROM ((projectData LEFT JOIN resourceCodes ON projectData.resourceCode = resourceCodes.ResourceCode) LEFT JOIN ProgramDetails ON projectData.Project = ProgramDetails.ProjectNumber) LEFT JOIN wbsData ON (projectData.Project = wbsData.Job) AND (projectData.WBS = wbsData.wbsCode)GROUP BY projectData.Project, projectData.costType, wbsData.jobCharge, resourceCodes.ResourceDesc, resourceCodes.[upper/lower], IIf([costtype]="eac",IIf([fiscaldate]="200809","CM ETC","na"),IIf([costtype]="actual",IIf([fiscaldate]="200809","
CM Act","na"),"na"))HAVING (((IIf([costtype]="eac",IIf([fiscaldate]="200809","CM ETC","na"),IIf([costtype]="actual",IIf([fiscaldate]="200809","CM Act","na"),"na")))<>"na"))

There's a space missing here SumOfoverheadFROM.

Looks alright now:

INSERT
INTO xxxxx
(
Project,
costType,
jobCharge,
ResourceDesc,
[upper/lower],
Cat,
SumOfhours,
SumOfcost,
SumOfoverhead
)
SELECT projectData.Project,
projectData.costType,
wbsData.jobCharge,
resourceCodes.ResourceDesc,
resourceCodes.[upper/lower],
IIF([costtype] ="eac",
IIF([fiscaldate] ="200809","CM ETC","na"),
IIF([costtype] ="actual",
IIF([fiscaldate]="200809","CM Act","na"),"na")) AS Cat,
SUM(projectData.hours) AS SumOfhours,
SUM(projectData.cost) AS SumOfcost,
SUM(projectData.overhead) AS SumOfoverheadFROM ((projectData
LEFT JOIN resourceCodes
ON projectData.resourceCode = resourceCodes.ResourceCode)
LEFT JOIN ProgramDetails
ON projectData.Project = ProgramDetails.ProjectNumber)
LEFT JOIN wbsData
ON (projectData.Project = wbsData.Job)
AND (projectData.WBS = wbsData.wbsCode)
GROUP BY projectData.Project,
projectData.costType,
wbsData.jobCharge,
resourceCodes.ResourceDesc,
resourceCodes.[upper/lower],
IIF([costtype] ="eac",
IIF([fiscaldate] ="200809","CM ETC","na"),
IIF([costtype] ="actual",
IIF([fiscaldate]="200809"," CM Act","na"),"na"))
HAVING (((
IIF([costtype] ="eac",
IIF([fiscaldate] ="200809","CM ETC","na"),
IIF([costtype] ="actual",
IIF([fiscaldate]="200809","CM Act","na"),"na")))<>"na")BTW what's that Cat doing in your xxxxx :D?

Thanks for the help. The cat is in there to keep me company when I get lost inside the MAZE of SQL.