Linking superseded records?

[NewShoes]

Hi,

I'm having a little trouble with this one. I basically need a little nudge in the right direction of how I could possibly achieve the following..

I am in the first stages are creating a database. It will hold records in one of the tables that at some point may be superseded by newer records. However, I also need a way to link the new record to the superseded one so I can create a record history. I also need to account for the fact that the record could be superseded multiple times!

Any help in this area woould be great!
-NS

 

[jdraw]

Can you give an example of exactly what would be superseded?
Is there a primary key for your "records"? Is it a name or attribute that would be overwritten?


in very general terms (but depends on what can change)

TblCurrentInfo

Id
Name
ModelNo
Desc...

TblHistoryInfo
HistId (pk)
id (value of Id in tblCurrentInfo)
SupersededDate

(id + SupersededDate) form a compound unique index

Just a few thoughts

 

[NewShoes]

Thanks for that JDraw. This seems like the basis of a good solution.

How would one normally going about ensuring that the ID in TblHistoryInfo is automatically set to the value in tblCurrentInfo?

Thanks!
-NS

 

[jdraw]

When you are making a revision to TblCurrentInfo, you would insert the relevant Id and the CurrentDate(if appropriate) into the TblHistoryInfo.

Do you have some sample records that would/could be superseded?

 

[NewShoes]

Thanks jDraw, I'm not sure this is exactly what I need. I will try an explain better...

I am basically logging and tracking vacancies. Sometimes, a vacany will be advertised but not filled and will be readvertised as another with different details. What I need is a way to link this new vacancy to the one it has replaced. Aslo keep in mind that the vacancy may change many times until it is filled. It is also possible that the vacancy will be split into 2, for example a manager role could be split into 2 junior assistant roles.

Any further help on this would be great!
-NS

 

[NewShoes]

Sorry for the bump - any help at all on this would be great!
Many thanks,
-NS

 

[NewShoes]

Hey, still struggling a little with this. Can anyone offer any suggestions?

thanks,
-NS

 

[jdraw]

Just saw this post...again
What version of Access? (I have 2003) What have you got at this point? Do you have tables and relationships?
Do you have a list of "business rules"?

I see Vacancies, Advertisement, Role, Position??, Transactions_History

 

[NewShoes]

Thanks jDraw, I'm using Access 2010.

I currently have the following tables:
tbl_Vacancies (VacID is the PK. This table contains the Vacancy Reference, title, grade, advert date, selection date etc)

tbl_VacancyHist (VacHistID is the PK and VacID is the FK from. I also have a notes field so the user can write a short note about why it was superseded.

I also have some other lookup tables but the 2 above are the main ones.

From my knowledge, I seem to be just missing a common identifier that would let me "tie" the absences together. Does this make any sense?

Many thanks for your help!
-NS

 

[jdraw]

Do Vacancies relate to a position within an Organization?
Can you describe the overall business in 3 or 4 sentences -- may help understand your specific situation.

 

[NewShoes]

Yes, this is all for one organisation. A vacancy will become available due to new funding or someone leaving. This will then be advertised and hopefully recruited to. I should point out that 1 or more vacancies can be advertised with the same reference number. For example, REF123 may be a vacancy for 3 junior assistants. 2 Might be recruited but 1 will still be left. This could then be readvertised as REF125.

At the moment, I can create the vacancies and, if needed, I can link the previous vacancies that have been superseded to the new one. This gives a history but only for the one vacancy that has preceded it. If the second vacancy fails to be recruited to, it will be readvertised again with yet another REF number. Essentially, what I believe I need is a way to link back through all the changes and get to the originatign record.

Hope this helps give a little background as to why I am finding this such a challenge!
-NS

 

[jdraw]

Have other things at the moment, will return later today/tonight. Can you post a copy of your tables and relationships?

 

[NewShoes]

Thanks jDraw. I have attached a screenshot of my current tables. I have a few more lookup tables (for example, line manager) but have left these off for the sake of neatness.



Any help would be great! Thanks for your time.

-NS

 

[jdraw]

Do you deal with Vacancy or Positions --- eg a Position becomes Vacant. Do you refer to the Vacancy by a separate Vacancy Id or do you/should you be referring to PositionId which just happens to be vacant at this time?

With Adverts, the closing dates, interview dates etc relate to the individual positions, right? Are these adverts all for one media or are there multiple outlets for this info?

 

[NewShoes]

I think I see what you are saying. There should perhaps be an Advert table to which I can attached sepoerate vacancies (as One Advert, i.e. REF123, can have have many vacancies). However, I'm still struggling with the tracking superseded part of this. Any ideas?

Thanks
-NS

 

[jdraw]

Can you describe the Superseded process with an example? Details will help, I hope.

 

[NewShoes]

I will try!

Basically, a vacancy will either be filled or not. If it's filled than that fine. If it's not filled then one of 2 things might happen. One, it could be readvertised meaning it will have a new REF number, closing date etc etc. Second thin that may happen is the funding for that vacancy might be used elsewhere, for example a senior manager vacancy might get made into 2 junior assistant roles. This would mean the 2 new records would be entered into the DB for the assisnt roles and the original senior management role would have an end date entered.

It is linking these new roles to the superseded ones that I am having difficulty with.

Thanks,
-NS

 

[jdraw]

I have to go out but I am attaching a zip of a 2003 mdb.
I have just started to add some data ,but would like you to see if it's meaningful.
There is a form to Document the database which reviews tables and field defintions and updates a table called data_dictionary.

I have identified tables in the relationships. I have not dealt with the History thing because I'm not sure what it is the history of..
Can a position be renamed/retitled etc or is it the fact that it has been in several adverts?

I see Positions which may be Filled (Incumbents) or not filled (Vacant).
See my query to show Vacant positions. I think you would run that and add necessary fields for (staffing actions - closing date, interview dates...) Once you create a current vacancies table by using the Vacant position query and additional fields, you would create a new Advert, then update the current vacancies( the ones to be included in the advert) with the AdvertId.

Must go for now.

 

[NewShoes]

Thanks, I'll take a look!

 

[jdraw]

I started with Organization, Positions, Employees where the company is "structured" into
Organizations, each Organization has a number of Positions. Each position has an Id, Title, Grade.... A filled Position has an Incumbent (employee). By linking Incumbents and Positions, it's possible to get the Unfilled(Vacant) Positions at any time.

So Vacancy is an unfilled Position on a certain Date. When you advertise a Vacancy, you add info describing the Vacant position,related Dates for responses, interviews and relate it to the Advert.

This is the basis of what i've given, and any or all of it could be some sort of misinterpretation.
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.