left join query is not working
in [Queries]
|
Prev: Converting a Date
Next: IIF and formatting
From: lindasarus on 19 Mar 2010 15:29 I have created a temp table that store ms project plans in an excel format. Each week, I import the updated plans into the temp table and then I then run an update query that updates my master table with the lastest information in the temp table - the join query The problem is that not all the updates are being included in the master. What options do I have? I would like to update the master with the lastest imported records but it doesnt always happen. Here's the query UPDATE [Temp Table] LEFT JOIN [Master Transfer Table] ON ([Temp Table].ID= [Master Transfer Table].ID) AND ([Temp Table].Job_ID=[Master Transfer Table]. Job_ID) SET [Master Transfer Table].ID = [Temp Table].[ID], [Master Transfer Table].Job_ID = [Temp Table].[Job_ID], [Master Transfer Table].Client_ID = [Temp Table].[Client_ID], [Master Transfer Table].Protocol = [Temp Table]. [Protocol], [Master Transfer Table].Notes = [Temp Table].[Notes], [Master Transfer Table].Date_Complete = [Temp Table].[Date_Complete], [Master Transfer Table].Task_Name = [Temp Table].[Task_Name], [Master Transfer Table]. KD = [Temp Table].[KD], [Master Transfer Table].Duration = [Temp Table]. [Duration], [Master Transfer Table].Start_Date = [Temp Table].[Start_Date], [Master Transfer Table].Finish_Date = [Temp Table].[Finish_Date], [Master Transfer Table].Predecessors = [Temp Table].[Predecessors], [Master Transfer Table].Resource_Names = [Temp Table].[Resource_Names], [Master Transfer Table] .Baseline_Start = [Temp Table].[Baseline_Start], [Master Transfer Table]. Baseline_Finish = [Temp Table].[Baseline_Finish], [Master Transfer Table]. Delay_Reasons = [Temp Table].[Delay_Reasons], [Master Transfer Table]. Customer_Code = [Temp Table].[Customer_Code];
From: KARL DEWEY on 19 Mar 2010 16:35 Try adding this -- WHERE [Master Transfer Table].ID = [Temp Table].[ID] OR [Master Transfer Table].ID Is Null; -- Build a little, test a little. "lindasarus" wrote: > I have created a temp table that store ms project plans in an excel format. > Each week, I import the updated plans into the temp table and then I then > run an update query that updates my master table with the lastest information > in the temp table - the join query The problem is that not all the updates > are being included in the master. What options do I have? I would like to > update the master with the lastest imported records but it doesnt always > happen. > > Here's the query > > UPDATE [Temp Table] LEFT JOIN [Master Transfer Table] ON ([Temp Table].ID= > [Master Transfer Table].ID) AND ([Temp Table].Job_ID=[Master Transfer Table]. > Job_ID) SET [Master Transfer Table].ID = [Temp Table].[ID], [Master Transfer > Table].Job_ID = [Temp Table].[Job_ID], [Master Transfer Table].Client_ID = > [Temp Table].[Client_ID], [Master Transfer Table].Protocol = [Temp Table]. > [Protocol], [Master Transfer Table].Notes = [Temp Table].[Notes], [Master > Transfer Table].Date_Complete = [Temp Table].[Date_Complete], [Master > Transfer Table].Task_Name = [Temp Table].[Task_Name], [Master Transfer Table]. > KD = [Temp Table].[KD], [Master Transfer Table].Duration = [Temp Table]. > [Duration], [Master Transfer Table].Start_Date = [Temp Table].[Start_Date], > [Master Transfer Table].Finish_Date = [Temp Table].[Finish_Date], [Master > Transfer Table].Predecessors = [Temp Table].[Predecessors], [Master Transfer > Table].Resource_Names = [Temp Table].[Resource_Names], [Master Transfer Table] > .Baseline_Start = [Temp Table].[Baseline_Start], [Master Transfer Table]. > Baseline_Finish = [Temp Table].[Baseline_Finish], [Master Transfer Table]. > Delay_Reasons = [Temp Table].[Delay_Reasons], [Master Transfer Table]. > Customer_Code = [Temp Table].[Customer_Code]; > > . >
From: John Spencer on 20 Mar 2010 08:55 I don't see any reason for that to fail. Is there a pattern to the records that don't get updated or added? Is this failing to update certain fields? Or certain records? Or is it not adding certain records? UPDATE [Temp Table] LEFT JOIN [Master Transfer Table] ON ([Temp Table].ID=[Master Transfer Table].ID) AND ([Temp Table].Job_ID=[Master Transfer Table].Job_ID) SET [Master Transfer Table].ID = [Temp Table].[ID] , [Master Transfer Table].Job_ID = [Temp Table].[Job_ID] , [Master Transfer Table].Client_ID =[Temp Table].[Client_ID] , [Master Transfer Table].Protocol = [Temp Table].[Protocol] , [Master Transfer Table].Notes = [Temp Table].[Notes] , [Master Transfer Table].Date_Complete = [Temp Table].[Date_Complete] , [Master Transfer Table].Task_Name = [Temp Table].[Task_Name] , [Master Transfer Table].KD = [Temp Table].[KD] , [Master Transfer Table].Duration = [Temp Table].[Duration] , [Master Transfer Table].Start_Date = [Temp Table].[Start_Date] , [Master Transfer Table].Finish_Date = [Temp Table].[Finish_Date] , [Master Transfer Table].Predecessors = [Temp Table].[Predecessors] , [Master Transfer Table].Resource_Names = [Temp Table].[Resource_Names] , [Master Transfer Table].Baseline_Start = [Temp Table].[Baseline_Start] , [Master Transfer Table].Baseline_Finish = [Temp Table].[Baseline_Finish] , [Master Transfer Table].Delay_Reasons = [Temp Table].[Delay_Reasons] , [Master Transfer Table].Customer_Code = [Temp Table].[Customer_Code]; John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County KARL DEWEY wrote: > Try adding this -- > WHERE [Master Transfer Table].ID = [Temp Table].[ID] OR [Master Transfer > Table].ID Is Null; >
From: lindasarus via AccessMonster.com on 22 Mar 2010 14:50 Karl I'm being requested to enter a parameter value on the Master Transfer table. Im not sure why. Linda KARL DEWEY wrote: >Try adding this -- >WHERE [Master Transfer Table].ID = [Temp Table].[ID] OR [Master Transfer >Table].ID Is Null; > >> I have created a temp table that store ms project plans in an excel format. >> Each week, I import the updated plans into the temp table and then I then >[quoted text clipped - 25 lines] >> >> . -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
From: lindasarus via AccessMonster.com on 22 Mar 2010 15:02
John; The date fields are not being updated or added if there is a new one. Linda John Spencer wrote: >I don't see any reason for that to fail. Is there a pattern to the records >that don't get updated or added? > >Is this failing to update certain fields? Or certain records? Or is it not >adding certain records? > >UPDATE [Temp Table] LEFT JOIN [Master Transfer Table] >ON ([Temp Table].ID=[Master Transfer Table].ID) > AND ([Temp Table].Job_ID=[Master Transfer Table].Job_ID) >SET [Master Transfer Table].ID = [Temp Table].[ID] >, [Master Transfer Table].Job_ID = [Temp Table].[Job_ID] >, [Master Transfer Table].Client_ID =[Temp Table].[Client_ID] >, [Master Transfer Table].Protocol = [Temp Table].[Protocol] >, [Master Transfer Table].Notes = [Temp Table].[Notes] >, [Master Transfer Table].Date_Complete = [Temp Table].[Date_Complete] >, [Master Transfer Table].Task_Name = [Temp Table].[Task_Name] >, [Master Transfer Table].KD = [Temp Table].[KD] >, [Master Transfer Table].Duration = [Temp Table].[Duration] >, [Master Transfer Table].Start_Date = [Temp Table].[Start_Date] >, [Master Transfer Table].Finish_Date = [Temp Table].[Finish_Date] >, [Master Transfer Table].Predecessors = [Temp Table].[Predecessors] >, [Master Transfer Table].Resource_Names = [Temp Table].[Resource_Names] >, [Master Transfer Table].Baseline_Start = [Temp Table].[Baseline_Start] >, [Master Transfer Table].Baseline_Finish = [Temp Table].[Baseline_Finish] >, [Master Transfer Table].Delay_Reasons = [Temp Table].[Delay_Reasons] >, [Master Transfer Table].Customer_Code = [Temp Table].[Customer_Code]; > >John Spencer >Access MVP 2002-2005, 2007-2010 >The Hilltop Institute >University of Maryland Baltimore County > >> Try adding this -- >> WHERE [Master Transfer Table].ID = [Temp Table].[ID] OR [Master Transfer >> Table].ID Is Null; -- Message posted via http://www.accessmonster.com |