Hi All,
I've messed up a report query i had designed using design view. it was working fine and then i added another query instead of a table into it to read from. the error query i added in originally has since been deleted but now my report query is not working, I cant open it in design view, only SQL as i get an error to say ' Microsoft Access can't represent the join expression [Query - Final report header information].[project number] = [Table - Hours and budget Report info].[project number] in design view'
I know this is because i have deleted the header information query but i cannot fix it in SQL, Can anybody offer some help as to what i need to do? i've copied the SQL in also
Thank you!
Code:
SELECT [Table - Hours and budget Report info].[Project Number], [Table - Hours and budget Report info].Dept, Budgets.Hours AS [Budget Hours], Changes.[Project Change Hours] AS [Approved Changes], [Budget Hours]+[Approved Changes] AS [Current Budget], Sum([Table - Hours and budget Report info].[Spend to Date]) AS [Spend to Date], Sum([Table - Hours and budget Report info].[Hours to go]) AS [Hours to go], [Spend to Date]+[Hours to go] AS [Forecast Final], [Forecast Final]-[Current Budget] AS Variance, Budgets.[Project Budget] AS [Rev Budget], Changes.[Project Change Value] AS [Rev App Changes], [Rev Budget]+[Rev App Changes] AS [Current Rev Budget], Sum([Table - Hours and budget Report info].[Revenue ETC]) AS [Rev ETC], Sum([Table - Hours and budget Report info].[Revenue Spend to Date]) AS [Rev Spend to Date], [Rev Spend to Date]+[Rev ETC] AS [Forecast Final Rev], [Forecast Final Rev]-[Current Rev Budget] AS [Rev Variance], [Rev Budget]/[Budget Hours] AS [Orig Budget], [Current Rev Budget]/[Current Budget] AS [R Current Budget], [Rev Spend to Date]/[Spend to Date] AS JTD, [Rev ETC]/[Hours to go] AS [To Go], [Forecast Final Rev]/[Forecast Final] AS [R Forecast Final], [Query - Final Report Header Information].[Project Manager], [Query - Final Report Header Information].[Project Name], [Query - Final Report Header Information].[Client Name], [Query - Final Report Header Information].[Project Number]FROM [Query - Final Report Header Information] RIGHT JOIN (([Table - Hours and budget Report info] LEFT JOIN Budgets ON ([Table - Hours and budget Report info].Dept = Budgets.Department) AND ([Table - Hours and budget Report info].[Project Number] = Budgets.[Project Number])) LEFT JOIN Changes ON ([Table - Hours and budget Report info].Dept = Changes.Department) AND ([Table - Hours and budget Report info].[Project Number] = Changes.[Project Number])) ON [Query - Final Report Header Information].[Project Number] = [Table - Hours and budget Report info].[Project Number]
GROUP BY [Table - Hours and budget Report info].[Project Number], [Table - Hours and budget Report info].Dept, Budgets.Hours, Changes.[Project Change Hours], Budgets.[Project Budget], Changes.[Project Change Value], [Query - Final Report Header Information].[Project Manager], [Query - Final Report Header Information].[Project Name], [Query - Final Report Header Information].[Client Name], [Query - Final Report Header Information].[Project Number];