CODEWITHSUNDEEP
ms-access
user3496218
user3496218

Reputation: 207

removing table from MS Access Query

I have a broken Access query and the reason it is broken is that one of the tables it is referencing doesn't exist so I can't see the query in design view - problem is I normally work in SQL Server and some of the formatting is bizarre to me - I need to take out all references to 'RMS_Import' table - any ideas?

Thanks!

SELECT Temp_LockedList.DEPARTMENT, PO_EmployeeName.Employee, PO_EmployeeName.BU, PO_EmployeeName.[PO Type], Temp_LockedList.STOCK_CATEGORY, Temp_LockedList.PO_NUMBER, Temp_LockedList.MATERIAL, Temp_LockedList.DESCRIPTION, Temp_LockedList.PO_ISSUE_DATE, Date()-[PO_ISSUE_DATE] AS [Days Since Issue], Temp_LockedList.PO_REQ_SHIP_DATE, IIf([LT] Is Null,IIf([dbo_articlenumbers].[LeadTime] Is Null,[rms_info].[leadtime],[dbo_articlenumbers].[LeadTime]),[LT]) AS [Prod LT], Temp_LockedList.PO_ORDERED_QUANTITY, Temp_LockedList.PO_BALANCE_QUANTITY, Temp_LockedList.Color, Temp_LockedList.ORIGINAL_SEASON, Temp_LockedList.CURRENT_SEASON, Temp_LockedList.LAUNCH_DATE, Temp_LockedList.WORKING_NUMBER, Temp_LockedList.IB_LOCK, Temp_LockedList.ORDER_PRIORITY_INDICATOR, Temp_LockedList.RANGE_TYPE_DESC, IIf([ActivationStatus] Is Null,[Lifecycle1] & "/" & [LO_Track1],[ActivationStatus]) AS [Activation Status / Prod Stat / Dev- LO], IIf([SignoffDate] Is Null,[RMS_Info].[Buy Ready],[SignoffDate]) AS [Buy Ready Date/ Sign off Date], RMS_Import.long_descr, RMS_Import.ret_from, RMS_Import.ret_to, IIf([MinOfCreateDate] Is Null,Date(),[MinOfCreateDate]) AS [First Shown on rpt], IIf(IsNull([LockedList - 2].[US PO #]),Null,"x") AS [In prior rpt]
FROM ((((((Temp_LockedList LEFT JOIN dbo_ArticleNumbers ON Temp_LockedList.MATERIAL = dbo_ArticleNumbers.ArticleNumber) LEFT JOIN PO_EmployeeName ON (Temp_LockedList.PO_NUMBER = PO_EmployeeName.PO_NUMBER) AND (Temp_LockedList.PO_ITEM_NUMBER = PO_EmployeeName.PO_ITEM_NUMBER) AND (Temp_LockedList.MATERIAL = PO_EmployeeName.MATERIAL)) LEFT JOIN RMS_Info ON Temp_LockedList.MATERIAL = RMS_Info.Article) LEFT JOIN [LockedList - 2] ON (Temp_LockedList.PO_NUMBER = [LockedList - 2].[US PO #]) AND (Temp_LockedList.MATERIAL = [LockedList - 2].Article)) LEFT JOIN [LockedList - 4] ON (Temp_LockedList.PO_NUMBER = [LockedList - 4].[US PO #]) AND (Temp_LockedList.MATERIAL = [LockedList - 4].Article)) LEFT JOIN RMS_Import ON Temp_LockedList.MATERIAL = RMS_Import.artnr) LEFT JOIN exceptions ON Temp_LockedList.PO_NUMBER = exceptions.[SAP PO NUMBER]
ORDER BY Temp_LockedList.DEPARTMENT, Temp_LockedList.PO_NUMBER, Temp_LockedList.MATERIAL;

Upvotes: 0

Views: 21

Answers (1)

EkriirkE
EkriirkE

Reputation: 2304

A quick and dirty GUI way would be to just make a table RMS_Import edit the query, use the editor to remove it from the query, save the query and then delete the RMS_Import table

Manually you should be able to just remove the text LEFT JOIN RMS_Import ON Temp_LockedList.MATERIAL = RMS_Import.artnr from the query string - it's flanked by parenthesis so it's easy to single out

In both cases you will have unreferenced columns that will prompt for user input each time until you remove them from the query editor columns

Upvotes: 1

Related Questions