Results 1 to 5 of 5
  1. #1
    Tia2021 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12

    Query Error due to missing table

    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];

  2. #2
    JoeM is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    3,904
    Did you just replace one table with a query?
    Do they both use the exact same field names?

    If so, you can simply replace all the references of the old table which no longer exists with the new query name.
    You can do this by copy/pasting the text out of SQL View to Microsoft Word, doing your Find/Replace there, and then copy/pasting it back into SQL View.

  3. #3
    Tia2021 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12
    Hi,

    No i need it out of the query altogether, there is no table to replace it

  4. #4
    Tia2021 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12
    HI JoeM

    Your answer actually did provided the solution! I just replaced the deleted query with a table that did contain the same field names, then i was able to open it in design view and remove the table just added... Thank you for outside the box thinking!!

  5. #5
    JoeM is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome.
    Glad it helped you figure out a solution.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 05-31-2019, 10:48 PM
  2. Replies: 2
    Last Post: 06-06-2016, 03:43 AM
  3. Replies: 2
    Last Post: 09-10-2014, 11:30 AM
  4. Replies: 3
    Last Post: 03-05-2013, 11:17 AM
  5. Replies: 6
    Last Post: 05-30-2012, 12:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums