What's the best way to implement an update/add if not found procedure?

[Colin Warburton]

I'm working with a normalised dbase and use a set of temporary files to store information about customers and their orders before committing to the permament versions of the files.
Existing orders can be amended, new orders can be added.
I'm building the routine(s) to copy the temporary files to the permanent omes and wondered what's the best way to compose the order handling routine which needs to update the order if present and add it if not.
I can do this quite easily in VBA, but I've seen many posts which state that SQL is quicker than recordset handling and I would expect, involves less code.
SQL, I'm not so proficient with:-
I'd like to be able to run a query which rattles through the temp orders (header) file and attempts to update related entries on the orders file, if the order number is not found, execute an insert to add it. Ideally, the temp order lines and order lines files could be managed at the same time, rather than separately.
I can post the file designs if necessary, but the solution should be generic as I'm certain I'll be using the technique elsewhere.

Thanks in advance...

[ADezii]

Essentially, two different Queries (UPDATE and APPEND) can perform this task for you using SQL only, with no VBA involved whatsoever.

1. The first Query (UPDATE) will Join tblTempOrders and tblOders via the Primary Key of tblTempOrders (OrderID) and the Foreign Key of tblOrders (OrderID). This would involve a 1 to MANY Relationship between the Tables, and would return all Orders for which a Temp Order exists. To Update a Field(s) would then be a simple matter. To illustrate this point, and based partially on Tables in the Northwind Database, the following SQL Statement will push ahead all Order Dates in the tblOrders Table for which a corresponding Order exists in tblTempOrders +10 days:
   

   UPDATE tbTempOrders INNER JOIN tblOrders ON tbTempOrders.OrderID = tblOrders.OrderID 
   SET tblOrders.OrderDate = tblOrders.OrderDate+10
   WHERE (((tblOrders.OrderDate) Is Not Null));

2. The second Query (APPEND), designed a little differently, will find all Orders in tblTempOrders which do not exist in tblOrders based again on the OrderID Field. It will then Append these Records into tblOrders. This Query makes use of what is called a LEFT JOIN.
   

   INSERT INTO tblOrders ( CustomerID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, OrderID )
   SELECT tbTempOrders.CustomerID, tbTempOrders.OrderDate, tbTempOrders.RequiredDate,
    tbTempOrders.ShippedDate, tbTempOrders.ShipVia, tbTempOrders.Freight, tbTempOrders.OrderID
   FROM tbTempOrders LEFT JOIN tblOrders ON tbTempOrders.OrderID = tblOrders.OrderID
   WHERE (((tblOrders.OrderID) Is Null));

P.S. - I will be the first to tell you that SQL is not my strong point. I'm sure that other Experts/Moderators/Members will chime in and provide you with a better insight into the matter.

[Colin Warburton]

Ok, thanks for the response ADezii. This takes me in a completely different direction - I'd got locked into testing for record not found before updating/ inserting or thinking about record counts. This looks far easier.
Just so I understand you correctly:
I need to pick a mandatory attribute on the target file and test for not null for the update and null for the insert - the left join ensures that all records on the source file are included, the right side will be null where there isn't a corresponding record.
The only doubt I have is that you referred to a 1 to many relationship between temp orders and orders. It's not quite like that. I'll explain a bit more and let me know if that alters the solution:-
There's a 1 to Many between temp orders and temp order lines
There's a 1 to Many between orders and order lines
There's a 1 to 1 between temp orders and orders
There's a 1 to 1 between temp order lines and order lines
The idea is that order manipulation is done in the temp files and when the user completes, the data is copied (or added if there's new orders) to their 'live' counterparts. The temp files are then cleared down ready for next use.

[ADezii]

When you say Temp Orders and Orders 'Files', what exactly are you referring to?

[Colin Warburton]

'Tables' - a consequence of my background :-)

[ADezii]

A Picture is Worth a Thousand Words, especially in this case. Download the Attachment, based on the Sample Northwind Database, and note the following:

1. Relationship between tblTempOrders and tblOrders.
2. qryOrdersAppend - Design View, SQL View, presence of LEFT JOIN, Execute it (3 records should be Appended to tblOrders).
3. qryOrdersUpdate - Design View, SQL View, Execute it - the Order Date of all Related Records in tblOrders will be pushed 'ahead' 10 Days.
4. Hope this all helps.

[Colin Warburton]

...and a thousand words are indeed saved. I'm now certain I can go forward with this solution.

Thank you ufor your time.

[ADezii]

You are quite welcome.

[Colin Warburton]

Well, many chores, child-handling, child-moaning, pestering and thankfully, putting to bed... later, I finally get back to the important things in life - this dbase app.
I've piloted the two queries in the query designer and (you'll already know this of course), they work. I had to take into account a related table for the append query first.
Now all I have to do is to copy the SQLs into their vba procs and spend the next four years staring at assorted apostrophes, double quotes, ampersands, underscores and commas until they works there too!

Job's a good 'un - consider yourself well and truly thanked!

[ADezii]

Good luck and keep in touch...