Append query with criteria

[robin a]

Hi,
I have an append query that has to add a record to a table [Join_tbl] if the field [ReqUNID] does not exist (there are other fields added as well with their own criteria) OR If the [ReqUID] already exists but another field [TaskStatus] has changed, then overwrite the [TaskStatus] field only. This is the code I have that only adds a record if the [ReqUID] doesn't exist.

INSERT INTO Join_tbl
   ( ReqUNID, 
      TaskTeam, 
      TaskAssignee, 
      taskStatus )
SELECT dbo_vwAssignments.ReqUNID, 
   dbo_vwAssignments.TaskTeam, 
   dbo_vwAssignments.TaskAssignee, 
   dbo_vwAssignments.taskStatus
FROM dbo_vwAssignments
WHERE (
   ((dbo_vwAssignments.TaskTeam)
      ="chp-vm")
    AND ((dbo_vwAssignments.taskStatus)
           ="acknowledged" 
         Or (dbo_vwAssignments.taskStatus)
              ="new" 
         Or (dbo_vwAssignments.taskStatus)
              ="pending" 
         Or (dbo_vwAssignments.taskStatus) Is Null));

Thank you in advance for your help

[Rabbit]

You will need two querys. An append query and an update query. I don't think you can do it with just one query in Access.

[robin a]

thank you, i did get to that point after i asked for help. You are quick, (Rabbit). I built the queries but for some reason my update query makes the data in the field to be updated blank. This is my update query:

UPDATE Temp_Join_tbl
   INNER JOIN Join_tbl 
      ON Temp_Join_tbl.ReqUNID 
         = Join_tbl.ReqUNID 
SET Join_tbl.ReqUNID 
      = [Temp_Join_tbl].[ReqUNID], 
   Join_tbl.Status 
      = [Temp_Join_tbl].[Status]
WHERE (
   ((Join_tbl.ReqUNID)
         =[Join_tbl].[ReqUNID])
);

I'm trying to update the Join_tbl.[status] with the value in Temp_Join_tbl.[Status] WHERE Temp_Join_tbl.ReqUNID = Join_tbl.ReqUNID SET Join_tbl.ReqUNID

Any suggestion?

[robin a]

sorry, i since removed the ReqUID field from the query and it works fine, but I don't understand why.

[NeoPa]

Rabbit:
You will need two querys. An append query and an update query. I don't think you can do it with just one query in Access.

Until quite recently I would have agreed with that. It turns out however, that an UPDATE query will act as an APPEND query in Access (Jet) when no match existing record is found.

[NeoPa]

Robin:
sorry, i since removed the ReqUID field from the query and it works fine, but I don't understand why.

If you were trying to set an AutoNumber value from another table then it would fail. AutoNumber values can only ever be set explicitly within an APPEND action. When you removed the code to set that the query could work.

[zmbd]

Post #3 Codeblock-Line 10: The WHERE clause with the SQL refers to the same table and same field; However, this is not as indicated in the sentence following wherein that statement refers to the fields between the two tables.

If the [ReqUID] is an autonumber it should have erred, not cleared fields.

[NeoPa]

Z:
If the [ReqUID] is an autonumber it should have erred, not cleared fields.

Good spot. I didn't see (overlooked) that detail :-(

So was the spot of the error in the OP's WHERE clause. Sharp today Z :-)