Update and Append Query with duplicates

[j4rmmbll]

I have a table (tblIMD) that is updated every two weeks downloaded from another source and imported into access.

Using a create table query I created another table (tblRPA) with some of the same fields filtering on vacant positions, and added additional fields to track recruitment data.

Now at the end of every pay period when I import the new tblIMD I want to update tblRPA with changed data i.e. a vacancy is filled, and append any new vacancies. I think I might have to do two queries, one update and one append.

For example I am starting with four vacancies when first downloaded (PP1)
The next download (PP2) one of the vacancies is filled V. Jones and it is marked Complete, Update Actual Strength and append one new vacancy V. Whitmer. The others would all stay the same.
Two weeks later (PP3), for the next download...Two vacancies actual strength is updated V. Lewis and V. Hayes...and they are marked complete, the rest would stay the same.
Two weeks later (PP4), next download...Because V. Jones (duplicate Para/Line) was marked complete and it has become vacant again, it would be appended to the RPA table. There should never be a PARA/LINE appended unless all previous instances of that PARA/LINE has been marked complete. Two lines have the Actual Strength updated V. Davis and V. Whitmer

PP1
402B 04 402B/04 1 0 V. LEWIS
406A 02 406A/02 1 0 V. JONES
410 11 410/11 1 0 V. DAVIS
412A 06 412A/06 1 0 V. HAYES

PP2
402B 04 402B/04 1 0 V. LEWIS NO CHANGE
406A 02 406A/02 1 1 V. JONES UPDATE (MARKED COMPLETE)
410 11 410/11 1 0 V. DAVIS NO CHANGE
412A 06 412A/06 1 0 V. HAYES NO CHANGE
402B 05 402B/05 1 0 V. WHITMER APPEND

PP3
402B 04 402B/04 1 1 V. LEWIS UPDATE (MARKED COMPLETE)
406A 02 406A/02 1 1 V. JONES NO CHANGE
410 11 410/11 1 0 V. DAVIS NO CHANGE


412A 06 412A/06 1 1 V. HAYES UPDATE (MARKED COMPLETE)
402B 05 402B/05 1 0 V. WHITMER NO CHANGE

PP4
402B 04 402B/04 1 1 V. LEWIS NO CHANGE
406A 02 406A/02 1 0 V. JONES APPEND
410 11 410/11 1 1 V. DAVIS UPDATE (MARKED COMPLETE)
412A 06 412A/06 1 1 V. HAYES NO CHANGE
402B 05 402B/05 1 1 V. WHITMER UPDATE (MARKED COMPLETE)

One of the fields in both tables is PARA/LINE. I have a yes/no field in tblRPA called Completed.

Over time there will be multiple identical PARA/LINE but should only be one that does not have the Completed field marked as No. In essence the tblRPA becomes a historical record of recruitment actions. There would never be a duplicate unless the old para/line was marked Complete,

Can this be done? How would I write these queries?

[Perceptus]

Yes it can be done. The main question is how far along into design are you? Attach a small db

[Micron]

While I have never done so, I've seen examples of where the source table was outer joined to the target table for an Update query. Thus if the 2 tables both contained the PK value (being the FK in the target table, of course) what happened was an update. Otherwise, what happened was an append. The 'All records" side of the linking dialog applies to the source table. Do try this on copies of things, not the originals.

Of course, uploading a db copy as suggested is always an option.

EDIT: forgot to mention that you can always prevent exact duplicates of a record by using a compound index, or compound primary key using as many table fields that are required to ensure uniqueness.

[j4rmmbll]

EDIT - I can't get it to upload. I went to Manage Attachments, clicked Add Files, browsed for my file, and clicked upload. I get the message TestRPA.accdb: Upload of file failed. I tried both .accdb and .zip. Got the same message. The .zip file is 2,696 KB.

*****************************

Here is what I have started, but now is the time to make any changes that I need to make. I appreciate any help.

The IMD Complete Extract Table is downloaded from another program every two weeks.

Thanks.

[Micron]

I sent a pm to a moderator as I can't suggest anything. The process you describe and file size don't seem to be an issue.
You could always use the Contact Us link at the bottom next time you have a similar issue.

[Micron]

apparently the upload limit depends on the file type. May be useful to you: https://www.accessforums.net/faq.php...b3_attachments
Make sure you compacted your db before zipping it.