left join query is not working

L

lindasarus

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

KARL DEWEY

Try adding this --
WHERE [Master Transfer Table].ID = [Temp Table].[ID] OR [Master Transfer
Table].ID Is Null;
 
J

John Spencer

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
 
L

lindasarus via AccessMonster.com

Karl

I'm being requested to enter a parameter value on the Master Transfer table.
Im not sure why.

Linda

KARL said:
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
Click to expand...
[quoted text clipped - 25 lines]
.
Click to expand...
Click to expand...
 
L

lindasarus via AccessMonster.com

John;

The date fields are not being updated or added if there is a new one.

Linda

John said:
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;
Click to expand...
Click to expand...
 
J

John Spencer

I'm sorry, but I cannot see any reason that would happen. I would expect an
error message of some type if the fields were of a different type - that is
Temp table had a text field and you were trying to populate a datetime field
in the Master Transfer Table.

Hopefully someone else will have an idea of why this is failing.

AS an experiment you might try using an append query to see if that works.

INSERT INTO [Master Transfer Table] (<<LIST OF FIELDS>>)
SELECT <<LIST OF Temp Table FIELDS>>
FROM [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)
WHERE [Master Transfer Table].ID Is Null

If that works, you know that the problem is in the UPDATE query and can try
trouble shooting that further.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John;

The date fields are not being updated or added if there is a new one.

Linda

John said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update query in SQL 2
Insert query with left joins results in record is deleted message 0
SQL Upsizing Problem with Left Join Queries 19
Left join not working 5
Update Query is not Working / Updateting 1
have query based from a left join, now need to exclude records.... 3
Not In (sort of) query 1
Query with Relationships not working 1

Top