Append & Update records from queries

[dreamsoul620 via AccessMonster.com]

Hi,
I have several tables that my query is pulling from. Each night, the data in
these tables will be updated. These tables only contain 7 days at a time. I
have a query (EARLY_OUT_FINAL) set up that will append the data I need into
another table for permanent storage and use. I have set up a multiple index
in my table (EARLY_LEAVE_POINTS) to prevent duplicates. This works great.
My problem occurs when the data in the imported tables changes. For instance,
I could pull in data for a specific employee on the 23rd that states they
left early on the 21st. It may be proven that the timeclock malfunctioned
and the time will be reset on the 24th. So when I pull this data back in, I
want the record to delete or update (if the amount of time changes). When I
try to create an update query, it states the recordset is nonupdateable.
Can anyone give me any ideas on this?
Also, there are two fields in queries that place a certain value in the table
(they do not pull from any table or query). Could this be part of the
problem?

 

[Michel Walsh]

Hi,


With Jet, to illustrate the case of an Append-Update query, we can use a
list of unit prices, in table Inventory, that has to be updated with values
in table NewPrices. Sure, new item can appear, from time to time, in
NewPrices, so they have to be appended to Inventory.

To do that in just one query, Jet allows to use:


UPDATE NewPrices LEFT JOIN Inventory
ON Inventory.ItemID = NewPrices.ItemID
SET Inventory.ItemID = NewPrices.ItemID,
Inventory.UnitPrice = NewPrices.UnitPrice



And indeed, that update existing records, AND append new ones.


That does not work with MS SQL Server.



Hoping it may help,
Vanderghast, Access MVP