Update-Insert query

[Sportman1975]

Update-Insert macro

Dear All,

I'm trying to find a solution for days now and I need (a lot of) help from some real specialist.

I getting a daily file htm-file which I import with a macro (TransferText macro). The problem is that the current file (let say "old") contains a lot of the same data as the new file ("new"). Now Access add all data from new in the old file. But the old file should be update with the new file

Old file & new file contain the following data:
Column 1: Name item
Column 2: Number item
Column 3: Invoice

Is it possible to make a macro which import this daily list and:
1) if invoice is in old list and new list ---> column 4: Status=Open
2) if invoice is in new list and not in old list ---> add line in old list
3) if invoice is in old list and not in new list ----> column 4: Status=Closed

If need more information: do not hesitate to ask me.

All help is very appreciated.......

 

[Poppa Smurf]

Attached is a solution.

I have three queries, a macro and module for code.
Tables - I have used two tables tbl_invoices_old for your current data and tbl_invocies_new for your download. Note, in your status field of your old table you will need a default value of New or Open for the queries to work.

You will need to set your Primary Key etc. in your old table so that you will not have duplicate entries.

Queries - these queries wil lupdate the old table.
qry_update_old_to_closed will update the Status field in the old to Closed for invoices that do not appear in the download.

qry_update_old_tpen will update the Status field in the old to Open for invoices that appear in the old and the download.

qry_append_new_to_old will add records from the download where the invoice number in the new is not in the old

Macro
macro_run_code is a macro to run code that will the code for the queries.

Module
mod_run_queries is code to run the queries. This module is required as there is not action in the macro to suppress warning messages when you run queries.

Edit
Forgot to attach zip file

 

[Sportman1975]

First thanks a lot for your great help Poppa Smurf!

Your query works great, but there's a next problem
Column 4 contains the price of the item. The prices can change by day so the item price must be changed in the latest price. How can I adjust your macro's to arrange this?

Thanks in advance.

 

[Poppa Smurf]

Please post a copy of your database with some sample data.

Is the item price included in your invoice, if so why do you need to change it?

 

[Sportman1975]

Hereby the requested attachment

 

[Sportman1975]

Tnx Poppa Smyrf for the great help!!!!