Prev: Converting a Date
Next: IIF and formatting
From: John Spencer on 23 Mar 2010 08:47
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

lindasarus via AccessMonster.com wrote:
> 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
From: lindasarus via AccessMonster.com on 31 Mar 2010 16:48
John;

I created the insert statement and no records get inserted into the master
table. This is really confusing.

Linda

lindasarus wrote:
>John;
>
>The date fields are not being updated or added if there is a new one.
>
>Linda
>
>>I don't see any reason for that to fail. Is there a pattern to the records
>>that don't get updated or added?
>[quoted text clipped - 31 lines]
>>> WHERE [Master Transfer Table].ID = [Temp Table].[ID] OR [Master Transfer
>>> Table].ID Is Null;

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1

From: John W. Vinson on 31 Mar 2010 21:49
On Wed, 31 Mar 2010 20:48:01 GMT, "lindasarus via AccessMonster.com"
<u58853(a)uwe> wrote:

>John;
>
>I created the insert statement and no records get inserted into the master
>table. This is really confusing.

Please post the complete SQL of your append query and indicate how you're
calling it (if from VBA code, please post the code).
--

John W. Vinson [MVP]
From: John Spencer on 1 Apr 2010 09:26
This may be a dumb question, but did you execute the insert query by selecting
Query:Run from the menu or did you simply switch to datasheet view. If you
simply switch to datasheet view the query does NOT execute and therefore does
NOT update any records.

If you did execute the query and no records were added then perhaps there were
not records to add. If you write the query as a SELECT Query (just remove the
INSERT INTO .... line(s), do you show any records in the result?

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

lindasarus via AccessMonster.com wrote:
> John;
>
> I created the insert statement and no records get inserted into the master
> table. This is really confusing.
>
> Linda
>
> lindasarus wrote:
>> John;
>>
>> The date fields are not being updated or added if there is a new one.
>>
>> Linda
>>
>>> I don't see any reason for that to fail. Is there a pattern to the records
>>> that don't get updated or added?
>> [quoted text clipped - 31 lines]
>>>> WHERE [Master Transfer Table].ID = [Temp Table].[ID] OR [Master Transfer
>>>> Table].ID Is Null;
>
First  |  Prev  | 
Pages: 1 2
Prev: Converting a Date
Next: IIF and formatting