using update query instead of append and delete query

[greekpatriott]

Is there a way to make this work????? It works if I make an append to and delete query and run them sequentially through a macro. Is it possible to make this sql work using an update query???
Otherwise is it possible to use a module to update a table?? Cheers

UPDATE [Control Plan] RIGHT JOIN [Control plan Q final] ON ([Control Plan].[Control Month] = [Control plan Q final].Date) AND ([Control Plan].[Control Mhrs] = [Control plan Q final].[Control Mhrs]) AND ([Control Plan].[Work Item ID] = [Control plan Q final].[Work Item ID]) AND ([Control Plan].[Package ID] = [Control plan Q final].[Package ID]) AND ([Control Plan].[Hierarchy Position] = [Control plan Q final].[Hierarchy Position]) SET [Control Plan].[Hierarchy Position] = [control plan Q final].[hierarchy Position], [Control Plan].[Package ID] = [control plan Q final].[package id], [Control Plan].[Work Item ID] = [control plan Q final].[work item id], [Control Plan].[Control Month] = [control plan Q final].[date], [Control Plan].[Control Mhrs] = [control plan Q final].[control Mhrs];


Cheers

 

[Golom]

Don't think so. Outer joins are usually not updatable.

This is sort of a "Seinfeld" (i.e. its about nothing!) update query. It updates those fields that already match in the "ON" clause which means that they already have the values to which you are setting them. The only ones that don't have those values are records where [Control Plan] has NULLs in the fields and that indicates a missing record. UPDATE doesn't insert new records.

What are you really trying to accomplish?

 

[greekpatriott]

The basic idea is:
1)Have a form to input data in a table.
2)Have a query to read from that table and perform some calculations.
3)Have another table which I update the latest data from that query.

I hope that this helps. Cheers Golom.