Eliminating duplicate records based on multiple criteria...

[ke4peo]

It's me again. Stuck on yet another part I can't seem to figure out the best way to accomplish without multiple steps.

A bit of background -

We run a Work Order Accuracy report. It outputs to Excel format. Contains account number, scheduled date, issue, and sales id. When the report it run it pulls data from the day prior.

I turn around and import this data into an Access table and add a STATUS field (default value of NEW). Agents then use a form to work the records and correct issues. Upon clicking the 'Complete' button on the form, it updates the table and sets the record's STATUS to COMPLETED.

The next day, the report is run again. Due to lag times between the time the order is completed in our database and the time that our IS department updates the report server...we sometimes end up with records that were already completed on the next day's report.

What I'm trying to do is create some form of query that will look at tbl_woa and search for duplicate records based on the following:

Account number
Scheduled date
Sales ID
STATUS

Basically I want it to go 'Ok, here are two records that match account number, schedule date, and sales id. But the status for one says NEW. I need to delete that record.'

I can't figure out a way to do this with queries or the RunSQL command. Any suggestions?

 

[fneily]

If I understand this, you import data into one table. How about create a duplicate table and make it the "work" table. So records come in, populate the worktable and you put in status of New.
You can then create a query connecting the worktable with the completed table on the three fields - Account number, Scheduled date, Sales ID. The output of the query would show these fields and both the status of the worktable and completed table. So you would get any matching records and see a status of New and Completed. Then you can use this info to delete the record.
Then you can use this worktable as the table for the users to work on. Afterwards, you can do an append query to update the completed table with completed records from the worktable.

I think this would be good design because you would have a table of completed records and a table of records still to be looked at.

What do you think?