how to update a some fields from a query to a table 1

[GreekPatriot]

Hello guys,
Is there a way to update a few fields from a query to a "table"?

I tried the update query but I get violation problems since I only have two fields to update.

I could copy the "table" and named it "table temp". Then delete the "table". Do query calculations on the "table temp". Use Update Query to trasnfer the Data to the "table".

Is there a better (shorter) method?????

 

[GreekPatriot]

I forgot to mention that the delete table will be deleted using the delete query, which does not delete the field headings.

 

[GreekPatriot]

Is there a VB code to save a table as "save as"????
Thanks

 

[GreekPatriot]

They say if its working dont fix it. Ok I have done the procedure I have described above and it is working.

Just wandering though if there is an easy way to update data in a table.

 

[PHV]

Why not post the SQL code you tried ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GreekPatriot]

What I have is a macro on a command button which when pressed does the following:
1) opens the append query: make table "work items temp"
2) opens the delete query: delete "work items"
3) opens the append query: make table "work items"
4) opens the delete query: delete "work items temp"

The sql on my guery is:
SELECT [work items temp].[Package ID], [work items temp].[Work Item ID], [work items temp].Description, [work items temp].[Unit of Measure], [work items temp].[Original Qty], IIf([variance type]="COR",[Original Qty]+[totalQty],[Original Qty]) AS [Client Qty], IIf([variance type]="Qty",[Client Qty]+[totalQty],[Client Qty]) AS [Control Qty], IIf([variance type]="PRO",[Control Qty]+[totalQty],[Control Qty]) AS [Forecast Qty], [work items temp].[Original Mhrs], IIf([variance type]="COR",[Original Mhrs]+[total Mhr],[Original Mhrs]) AS [Client Mhrs], IIf([variance type]="Qty",[Client Mhrs]+[total Mhr],[Client Mhrs]) AS [Control Mhrs], IIf([variance type]="Pro",[Control Mhrs]+[total Mhr],[Control Mhrs]) AS [Forecast Mhrs], [Qty/Mhrs Variances by WP Q].[Variance Type], nz([Total Mhrs],0) AS [Total Mhr], nz([Total Qty],0) AS TotalQty, [work items temp].[Original Start], [work items temp].[Original End], [work items temp].[Client Start], [work items temp].[Client End], [work items temp].[Control Start], [work items temp].[Control End], [work items temp].[Forecast Start], [work items temp].[Forecast End], [work items temp].[Status Method], [work items temp].Reference
FROM [work items temp] LEFT JOIN [Qty/Mhrs Variances by WP Q] ON ([work items temp].[Package ID] = [Qty/Mhrs Variances by WP Q].[Package ID]) AND ([work items temp].[Work Item ID] = [Qty/Mhrs Variances by WP Q].[Work Item ID]);
Cheers

 

[GreekPatriot]

the /b make table "work items" \b is based on the query with the sql I have mentioned above named variance table.

This table would have been ommited if an easier way can be deviced without creating the temporary table "work items temp" mentioned in the sql above. But now things are compicated I think since I went the long way.

The short way would be to use one query to read the "work items" table, and the "Variance transactions" table. Do the calculations on the query and then somehow transfer/update the 6 fields (Client Qty, Control Qty, Forecast Qty, Client Mhrs, Control Mhrs, Forecast Mhrs) back in the "work items" table.

 

[PHV]

A starting point:
UPDATE tblMaster INNER JOIN tblTransac ON join clause
SET tblMaster.SomeField = some calculation
, SET tblMaster.AnotherField = some other calculation
WHERE some conditions

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GreekPatriot]

UPDATE [work items] INNER JOIN [Unscheduled Tasks Q] ON ([work items].[Package ID] = [Unscheduled Tasks Q].[Package ID]) AND ([work items].[Work Item ID] = [Unscheduled Tasks Q].[Work Item ID]) SET [work items].[Client Qty] = [unscheduled tasks Q].ClientQty, [work items].[Control Qty] = [unscheduled tasks Q].ControlQty, [work items].[Forecast Qty] = [unscheduled tasks Q].ForecastQty, [work items].[Client Mhrs] = [unscheduled tasks Q].ClientMhrs, [work items].[Control Mhrs] = [unscheduled tasks Q].Controlmhrs, [work items].[Forecast Mhrs] = [unscheduled tasks Q].Forecastmhrs;

Thanks I think I managed to do it the short way. Cheers PHV