From: lindasarus on
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
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
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
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
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

 |  Next  |  Last
Pages: 1 2
Prev: Converting a Date
Next: IIF and formatting