Two queries work fine, but joined together get error

[yorkshirepudding]

Hi

I am in the process of building a reporting tool in Access, pulling together data from another database and from a web database (via emailed reports). It is mostly going fine with queries working as supposed to. However, when I try and combine two moderately complex queries, I get the following error and I can't work out why:

"The Microsoft Jet database engine does not recognise "ProjectMilestones.ProjectID" as a valid field name or expression"

The first query produces a list of projects and their status and stage. The status is determined by the latest milestone that has been achieved and the milestones table is linked to the status table which in turn is linked to the stage table. I have marked in blue where the error seems to be, as if I alias this table as something else, the error message changes to the alias. This query runs fine on its own and I have joined with other queries with no problem.

SELECT ProjectMilestones.ProjectID, Status.StatusName, Stage.StageNumber, Stage.StageName
FROM ProjectMilestones, Milestones, Status, Stage, Projects
WHERE (((ProjectMilestones.ProjectID)=[Projects].[ProjectID]) 
AND ((ProjectMilestones.ProjectMilestoneID) In (SELECT TOP 1 ProjectMilestonesDuplicate.ProjectMilestoneID
FROM ProjectMilestones AS ProjectMilestonesDuplicate, Milestones AS MilestonesDuplicate
WHERE ProjectMilestonesDuplicate.ProjectID = [COLOR=royalblue]ProjectMilestones.ProjectID[/COLOR]
AND ProjectMilestonesDuplicate.MilestoneID = MilestonesDuplicate.MilestoneID
ORDER BY ProjectMilestonesDuplicate.ActualDate DESC, MilestonesDuplicate.MilestoneOrder DESC, ProjectMilestonesDuplicate.ProjectMilestoneID DESC)) 
AND ((ProjectMilestones.ActualDate) Is Not Null) 
AND ((ProjectMilestones.MilestoneID)=[Milestones].[MilestoneID]) 
AND ((Milestones.StatusID)=[Status].[StatusID]) 
AND ((Status.StageID)=[Stage].[StageID]))
ORDER BY ProjectMilestones.ProjectID;

The second query pulls together lots of stats on irregularities, corrections and errors (all mistakes done by projects in what they claim, but discovered in different ways so treated differently). This query runs fine on its own, or fine when joined with just the Projects table:

SELECT Projects.ProjectID, Q3_ClaimErrorsConcluded.CountClaimErrorsConcluded, 
Nz([Q3_CorrectionAllConcluded].[CountCorrectionsConcluded],0) AS CountCorrectionsConcluded, 
Nz([Q3_CorrectionAllConcluded].[IrregularExpenditureGBP],0) AS CorrectionConcludedExpenditureGBP, 
Nz(Q3_CorrectionAllConcluded.IrregularGrantGBP,0) AS CorrectionConcludedGrantGBP, 
Nz(Q3_CorrectionAllUnconcluded.CountCorrectionsUnconcluded,0) AS CountCorrectionsUnconcluded, 
Nz(Q3_CorrectionAllUnconcluded.IrregularExpenditureGBP,0) AS CorrectionUnconcludedExpenditureGBP, 
Nz(Q3_CorrectionAllUnconcluded.IrregularGrantGBP,0) AS CorrectionUnconcludedGrantGBP, 
Nz(Q3_CorrectionAllUnopened.CountCorrectionsUnopened,0) AS CountCorrectionsUnopened, 
Nz(Q3_IrregConcluded.CountIrregularityConcluded,0) AS CountIrregularityConcluded, 
Nz(Q3_IrregConcluded.IrregularExpenditureGBP,0) AS IrregConcludedExpenditureGBP, 
Nz(Q3_IrregConcluded.IrregularGrantGBP,0) AS IrregConcludedGrantGBP, 
Nz(Q3_IrregUnconcluded.CountIrregularityUnconcluded,0) AS CountIrregularityUnconcluded, 
Nz(Q3_IrregUnconcluded.IrregularExpenditureGBP,0) AS IrregUnconcludedExpenditureGBP, 
Nz(Q3_IrregUnconcluded.IrregularGrantGBP,0) AS IrregUnconcludedGrantGBP, 
Nz(Q3_IrregUnopened.CountIrregularityUnopened,0) AS CountIrregularityUnopened, 
Nz(Q3_ErrorNFUnopened.CountClaimErrors,0) AS CountErrorNFUnopened, 
Nz(Q3_ErrorNFUnconcluded.CountClaimErrors,0) AS CountErrorNFUnconcluded, 
Nz(Q3_ErrorFUnopened.CountErrorsFinancial,0) AS CountErrorFUnopened, 
Nz(Q3_ErrorF0Unconcluded.CountClaimErrors,0) AS CountErrorF0Unconcluded, 
Nz(Q3_ErrorFUnconcluded.CountErrorsFinancial,0) AS CountErrorFUnconcluded, 
Nz(Q3_ErrorFUnconcluded.SumOfIrregularExpenditureGBP,0) AS ErrorFUnconcludedExpenditureGBP, 
Nz(Q3_ErrorFUnconcluded.SumOfIrregularGrantGBP,0) AS ErrorFUnconcludedGrantGBP, 
Nz(Q3_ErrorFConcluded.CountErrorsFinancial,0) AS CountErrorFConcluded, 
Nz(Q3_ErrorFConcluded.IrregularExpenditureGBP,0) AS ErrorFConcludedExpenditureGBP, 
Nz(Q3_ErrorFConcluded.IrregularGrantGBP,0) AS ErrorFConcludedGrantGBP
FROM (((((((((((((Projects LEFT JOIN Q3_CorrectionAllConcluded ON Projects.ProjectID = Q3_CorrectionAllConcluded.ProjectID) 
LEFT JOIN Q3_CorrectionAllUnconcluded ON Projects.ProjectID = Q3_CorrectionAllUnconcluded.ProjectID) 
LEFT JOIN Q3_CorrectionAllUnopened ON Projects.ProjectID = Q3_CorrectionAllUnopened.ProjectID) 
LEFT JOIN Q3_IrregConcluded ON Projects.ProjectID = Q3_IrregConcluded.ProjectID) 
LEFT JOIN Q3_IrregUnconcluded ON Projects.ProjectID = Q3_IrregUnconcluded.ProjectID) 
LEFT JOIN Q3_IrregUnopened ON Projects.ProjectID = Q3_IrregUnopened.ProjectID) 
LEFT JOIN Q3_ErrorNFUnopened ON Projects.ProjectID = Q3_ErrorNFUnopened.ProjectID) 
LEFT JOIN Q3_ErrorNFUnconcluded ON Projects.ProjectID = Q3_ErrorNFUnconcluded.ProjectID) 
LEFT JOIN Q3_ErrorF0Unconcluded ON Projects.ProjectID = Q3_ErrorF0Unconcluded.ProjectID) 
LEFT JOIN Q3_ErrorFUnopened ON Projects.ProjectID = Q3_ErrorFUnopened.ProjectID) 
LEFT JOIN Q3_ErrorFUnconcluded ON Projects.ProjectID = Q3_ErrorFUnconcluded.ProjectID) 
LEFT JOIN Q3_ErrorFConcluded ON Projects.ProjectID = Q3_ErrorFConcluded.ProjectID) 
LEFT JOIN Q3_CapRevExpTotal ON Projects.ProjectID = Q3_CapRevExpTotal.ProjectID) 
LEFT JOIN Q3_ClaimErrorsConcluded ON Projects.ProjectID = Q3_ClaimErrorsConcluded.ProjectID;

I am trying to join them together with the Project's table so I can summarise, projects with a status greater than 3 (means the project is live), but any variation on the query below, just produces the same error:

SELECT Projects.Applicant, Sum(Q3_SummaryIrregularitiesCorrectionsErrorsA.CountClaimErrorsConcluded) AS SumOfCountClaimErrorsConcluded, 
Sum(Q3_SummaryIrregularitiesCorrectionsErrorsA.CountCorrectionsConcluded) AS SumOfCountCorrectionsConcluded
FROM (Projects LEFT JOIN Q3_SummaryIrregularitiesCorrectionsErrorsA ON Projects.ProjectID = Q3_SummaryIrregularitiesCorrectionsErrorsA.ProjectID) 
LEFT JOIN Q3_StatusStage ON Projects.ProjectID = Q3_StatusStage.ProjectID
WHERE (((Q3_StatusStage.StageNumber)>3))
GROUP BY Projects.Applicant;

Can anyone spot what I am doing wrong?

Thanks
yorkie

 

[yorkshirepudding]

Hi

As no-one had any quick solutions, I went back to basics and thought about how I could do this purely on simple queries with no sub queries. I have now done that and it works. For reference and for the benefit of anyone coming across this by google with a similar issue, here is what I did:

Q3_StatusStage1

SELECT ProjectMilestones.ProjectID, Max(ProjectMilestones.ActualDate) AS MaxOfActualDate
FROM Milestones RIGHT JOIN ProjectMilestones 
ON Milestones.MilestoneID = ProjectMilestones.MilestoneID
GROUP BY ProjectMilestones.ProjectID
HAVING (((Max(ProjectMilestones.ActualDate)) Is Not Null))
ORDER BY ProjectMilestones.ProjectID;

Q3_StatusStage2

SELECT ProjectMilestones.ProjectID, ProjectMilestones.ActualDate, 
Max(Milestones.MilestoneOrder) AS MaxOfMilestoneOrder
FROM Q3_StatusStage1 INNER JOIN (Milestones RIGHT JOIN ProjectMilestones 
ON Milestones.MilestoneID = ProjectMilestones.MilestoneID) 
ON Q3_StatusStage1.ProjectID = ProjectMilestones.ProjectID
WHERE (((ProjectMilestones.ActualDate)=[Q3_StatusStage1].[MaxOfActualDate]))
GROUP BY ProjectMilestones.ProjectID, ProjectMilestones.ActualDate
HAVING (((ProjectMilestones.ActualDate) Is Not Null))
ORDER BY ProjectMilestones.ProjectID;

Q3_StatusStage2a

SELECT Q3_StatusStage2.ProjectID, Q3_StatusStage2.ActualDate,
 Q3_StatusStage2.MaxOfMilestoneOrder AS MilestoneOrder,
 [Q3_StatusStage2]![ProjectID] & "-" & [Q3_StatusStage2]![ActualDate] 
& "-" & [Q3_StatusStage2]![MaxOfMilestoneOrder] AS CompositeKey
FROM Q3_StatusStage2;

Q3_StatusStage2b

SELECT ProjectMilestones.ProjectMilestoneID, ProjectMilestones.ProjectID, 
ProjectMilestones.ActualDate, Milestones.MilestoneOrder, 
[ProjectMilestones]![ProjectID] & "-" & [ProjectMilestones]![ActualDate] &
 "-" & [Milestones]![MilestoneOrder] AS CompositeKey
FROM Milestones RIGHT JOIN ProjectMilestones 
ON Milestones.MilestoneID = ProjectMilestones.MilestoneID
WHERE (((ProjectMilestones.ActualDate) Is Not Null));

Q3_StatusStage3

SELECT Q3_StatusStage2a.ProjectID, Q3_StatusStage2a.ActualDate, 
Q3_StatusStage2a.MilestoneOrder, Q3_StatusStage2b.ProjectMilestoneID
FROM Q3_StatusStage2a LEFT JOIN Q3_StatusStage2b 
ON Q3_StatusStage2a.CompositeKey = Q3_StatusStage2b.CompositeKey;

Q3_StatusStage4

SELECT Q3_StatusStage3.ProjectID, Q3_StatusStage3.ActualDate, 
Q3_StatusStage3.MilestoneOrder, Status.StatusName, 
Stage.StageNumber, Stage.StageName
FROM Stage RIGHT JOIN (Status RIGHT JOIN (Milestones RIGHT JOIN (Q3_StatusStage3 
LEFT JOIN ProjectMilestones 
ON Q3_StatusStage3.ProjectMilestoneID = ProjectMilestones.ProjectMilestoneID) 
ON Milestones.MilestoneID = ProjectMilestones.MilestoneID) 
ON Status.StatusID = Milestones.StatusID) 
ON Stage.StageID = Status.StageID;

Q3_StatusStage

SELECT Projects.ProjectID, Q3_StatusStage4.StatusName,
 Q3_StatusStage4.StageNumber, Q3_StatusStage4.StageName
FROM Projects LEFT JOIN Q3_StatusStage4 
ON Projects.ProjectID = Q3_StatusStage4.ProjectID;

I can now breath a sigh of relief and finish for the day