finding duplicate and unmatched records from new table for updates in master table

[confusionguy]

I have some inquires regarding my dbase of finding closed and new transactions.
I have a table (Orders) in MS Access which act as a master file which lists all the records of the orders to be processed.
At certain times of the day, a report will be generated from my company's system and this will includes the latest orders status.

New Orders are those that are not inside my Master file (Table - Orders) in Access but in the Report generated which I can export it to a Table (Latest) in Access.
Closed Orders are those that are inside in Master file (Table - Orders) in Access but not in the generated Report exported to a Table (Latest).
Existing Orders are those inside both Master File and the Report with comments of the orders in the Master File. Hence the need to delete those in the Report.


Qns 1: Do I export the Report generated from my company's system to a new table as Latest or to my existing Master File (Orders) and use a duplicate query to identify those existing orders? I have to be mindful that these action queries can be stored under 1 Macro for better performance.

Qns 2: How do I use a query to find the closed orders? Does a unmatched query helps? For these closed orders, I have to indicate in a Status Field in my Master File (Orders) as closed. How do I allow auto entry in that Field?

Qns 3: Say I do not export the Report to my existing Master File, how do I identify the New Orders? An unmatched query? If so, how do I do that in SQL language?

Do note that if I export the Report to a new table as Latest, there is no relationship defined between my Master File (Orders) and the Table-Latest.

I appreciate those Access Experts to help me with this qns.
Thanks a lot in advance.

[hermhart]

If I understand you correctly, I don't see any advantage in having the Latest table. I'm answer your 3 questions as one. Why not export directly to your Orders table? You didn't suggest any format incompatibilities. You could have a field in the Orders table that indicates the status -- like New, InProcess, Closed. New orders coming in could automatically be defaulted to New. Then maybe you would want the OnCurrent event to change that status to InProcess. I don't see why you would want auto-entry to flag your Closed status. At some point a human operator or some other event will have to trigger a flag to show an order is Closed.