Hi again, Sara - I've been up to my a** in alligators, which is about par for the course in south Louisiana. (Actually, just another job crisis - which is par for the course with U.S. Dept. of Defense jobs.)
My original post was based on the idea that you are having trouble keeping track of your data sources. On reading the exchanges among yourself and other contributors, I retreat to my original question:
Does your database activity represent an accurate model of every step you would have performed if this were being done entirely using paper forms and records?
My thought is that when you try to do something and it confuses you, there are two possible reasons for that confusion:
1. The process you implemented does not match the data flow of reality (in this case, of your business)
2. A variant of 1, really: You didn't originally understand the data flow of your business so COULDN'T design the Access transactions to match it.
For instance, you have been agonizing over whether there is any significant difference between a person from outside your company applying for a position and one from inside your company applying for a position different from the one they currently hold. My answer is, it depends on whether that difference would have existed in the days of paper record keeping.
My comments about "unposting" relate to a situation we have in one of our personnel databases here. It is a military database, so has some standards that might differ from the ones you are applying. Here, we talk about persons and positions being cross-linked using a "sparse" one-to-one relationship. In practise, this means a "one-to-{sometimes zero, sometimes one}" relationship. I.e., the "all records from X and matching records from Y" are defined BOTH ways. This is ORACLE for us, so the relationship is always expressed in SQL with explicit OUTER JOIN cases in the required direction.
In our case, if we list persons by SSN or alphabetically, we can join to the table that shows details about their current assignment, if they have one. But if we list jobs by military job code, we can show every person who has such jobs, if the job slot is filled. The connection between the two is symmetrical. So when we put someone into a new job, we first have to take that person OUT of the old job if they had one.
I cannot tell which one you are struggling with from long distance but I still see some confusion. Since I cannot see the specific problem, there is no way I can give specific advice. However, I stand by the idea that you should be able to exactly model a business data flow using Access, at worst with a few MINOR workarounds.
For instance, the recent posts on the need for and uses of a junction table. You are right that a junction table is how you implement a many-to-many relationship. If you allow a person to post for many positions, and if each position has many persons posting for it, then you will definitely need a junction table to track the postings.
