Linking superseded records?

[NewShoes]

I
As far as the Vacancy History goes, I think you are dealing with Positions. If a Position is filled, there will be an associated incumbent.
If a Position is not filled, then that Position could be "logically terminated" (EndDate/deleted from available Positions) and 0,1 or more Positions added, to "make use of existing funding". These Positions would get new PositionNo, Title(perhaps), Grade (perhaps) and PositionRef. In effect, the old Position ceases to exist, and new Position(s) are created.

Thanks. This makes sense, however, if I wanted to run a query to show me the history of vacancies (i.e. a sequential list of vacancies that have superseded each other), what would I have to do?

-NS

 

[Galaxiom]

I haven't read the whole thread but I don't think I would use a separate history table. The archived records are identical in structure to the main record so might as well be in the same table.

There are a number of ways to connect to the superceded records.

Include a field to hold the key to the original record. The history is extracted with a self join on that field and the key.

Include a table of the position and relate the records to that table.

 

[NewShoes]

I haven't read the whole thread but I don't think I would use a separate history table. The archived records are identical in structure to the main record so might as well be in the same table.

There are a number of ways to connect to the superceded records.

Include a field to hold the key to the original record. The history is extracted with a self join on that field and the key.

Include a table of the position and relate the records to that table.

Thanks for your input on this Galaxiom.

I do see what you're saying but I don't think I can do it in the same table. The reason being that 2 or more vacancies could be superseded by just one vacancy (of a higher pay grade).

What you say is interesting though. Would the field to hold the original key have to be manually updated by the user (perhaps in a combo box)?

Also, I'm a little confused about the "include a table of the position" sentence. Could you please explain a little more?

many thanks,
-ns

 

[Galaxiom]

If one position can supercede multiple positions (or vice versa) then use a join table. This would have fields for superceded and superceding position IDs and a record for each combination.

By "include a table of the position" I am sure I knew what I meant at the time but I can't remember now.

 

[jdraw]

I have adjusted the original mdb. There is test data and some Adverts and Vacancies. There are queries to show different things including
ShowSupersededList and SupercessorPositions which show Suoerseded Positions.

I hope it's helpful.

 

[NewShoes]

Thanks Galaxiom. I'm a little unsure if this would work (I assume Join Table and Junction Table are the same thing?). If a vacancy was superseded more than twice, the 3rd record would hold the value of 2nd (as that's the one that's now being superseded), not the original so it still wouldn't allow me to create a query that links all the way back to the original vacancy (thus showing where a given vacancy originated from).

Hope that makes some kind of sense!

EDIT: just saw your reply jDraw...thanks, I'll take a look!

-NS

 

[jdraw]

I am attaching another version with new relationships and tables. There is test data.
The change to relationships shows Positions, Vacancies, Advert and AdvertisedVacancies.

I hope it's useful. Feedback is welcome.