Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 12-07-2018, 12:13 AM
Fish Actuary's Avatar
Fish Actuary Fish Actuary is offline
Member
Non-Actuary
 
Join Date: Jun 2006
Location: Australia
Posts: 11,351
Default Database log file?

I'm not much of a database person, so perhaps these are some really dumb questions...

At work, we have an Access database that has multiple people entering data into it (which seems like really bad practice to me, and worries me a lot...). Is there some way of automatically logging who's changed what recently?

If this functionality doesn't exist in Access, does it perhaps exist in a different database program?

Is there some way of semi-automatically <not sure if this is the right way of calling it> maintaining the data in two separate states. Basically, older entries that have passed a QA/QC process and newer entries that still need to undergo a QA/QC process?
Reply With Quote
  #2  
Old 12-07-2018, 12:20 AM
Colonel Smoothie's Avatar
Colonel Smoothie Colonel Smoothie is offline
Member
CAS
 
Join Date: Sep 2010
College: Jamba Juice University
Favorite beer: AO Amber Ale
Posts: 48,115
Default

I can't say for access but real databases like sql server ought to have the feature. As for QA you might want to create a separate table that contains QA checks as a transaction. You can join the main table to it to check which entries have undergone a check.
__________________
Recommended Readings for the EL Actuary || Recommended Readings for the EB Actuary

Quote:
Originally Posted by Wigmeister General View Post
Don't you even think about sending me your resume. I'll turn it into an origami boulder and return it to you.
Reply With Quote
  #3  
Old 12-07-2018, 12:32 PM
oedipus rex's Avatar
oedipus rex oedipus rex is offline
Member
SOA AAA
 
Join Date: Nov 2002
Favorite beer: too many to list here
Posts: 15,356
Default

if you are that worried about access control to this data, you should probably discuss with IT what can be done to maintain data integrity and design a robust solution for this data. this way they can also put some controls around the process, which really is required if you read the data quality asop closely. alternatively you can make the database read only and password protect it, that way if someone needs to make a change they need to go through you first.
__________________
Life can only be understood backwards; but it must be lived forwards. --S.K.
Reply With Quote
  #4  
Old 12-07-2018, 05:20 PM
Fish Actuary's Avatar
Fish Actuary Fish Actuary is offline
Member
Non-Actuary
 
Join Date: Jun 2006
Location: Australia
Posts: 11,351
Default

Quote:
Originally Posted by oedipus rex View Post
if you are that worried about access control to this data, you should probably discuss with IT what can be done to maintain data integrity and design a robust solution for this data. this way they can also put some controls around the process, which really is required if you read the data quality asop closely. alternatively you can make the database read only and password protect it, that way if someone needs to make a change they need to go through you first.
The problem is we have multiple groups of technicians entering data directly through various forms and then other technicians updating fields as they complete various tasks.

On top of that we're currently going through some staff turnover at the moment for a few different reasons including the person who was up until recently in overall charge of our group and who may or may not return to the position (some health issues). I'm a bit worried about people maliciously trying to damage the database, but am also worried about inadvertent errors given that I've already identified numerous errors.

I'm not familiar with the data quality asop, but note that this isn't actuarial work. Do you have a link? It could be useful reading. If I end up getting control of the database I may look at the SQL server option. We're having some broader division wide discussions about data management and software/code migrations as our SAS users are rapidly retiring and the new hires are more R or python users. It may be that I can push to a broader shift to SQL server...
Reply With Quote
  #5  
Old 12-10-2018, 03:00 PM
oedipus rex's Avatar
oedipus rex oedipus rex is offline
Member
SOA AAA
 
Join Date: Nov 2002
Favorite beer: too many to list here
Posts: 15,356
Default

I would keep a daily backup then, and create some comparison queries to compare the backup database to the current database by linking. I think you can implement some kind of logging feature, but this would require you to build a front end and write some VBA code that carefully logs each and every update (unless there is something simple I am not aware of). That would take some time. As for the ASOP, it clearly isn't relevant in your case, but here is a link: http://www.actuarialstandardsboard.o...sop023_141.pdf
__________________
Life can only be understood backwards; but it must be lived forwards. --S.K.
Reply With Quote
  #6  
Old 12-10-2018, 05:07 PM
Fish Actuary's Avatar
Fish Actuary Fish Actuary is offline
Member
Non-Actuary
 
Join Date: Jun 2006
Location: Australia
Posts: 11,351
Default

Quote:
Originally Posted by oedipus rex View Post
I would keep a daily backup then, and create some comparison queries to compare the backup database to the current database by linking. I think you can implement some kind of logging feature, but this would require you to build a front end and write some VBA code that carefully logs each and every update (unless there is something simple I am not aware of). That would take some time. As for the ASOP, it clearly isn't relevant in your case, but here is a link: http://www.actuarialstandardsboard.o...sop023_141.pdf
Given our annual work cycle and the rate at which new data is added I suspect weekly backups should be sufficient.

In searching, I found some VBA code for tracking, but I think the better solution is going to be SQL Express server in the longer term.

The joy of migrating technologies over time...

We're building a new piece of data acquisition software and reportedly the programmer is being asked to make it backwards compatible with data that we used to store (input?) in a database using fixed-width files/punch cards.

I'd argue the cleaner solution is to keep the fields and migrate the old data to a new format and use modern practices for the new data.
Reply With Quote
  #7  
Old 12-10-2018, 08:51 PM
Incredible Hulctuary's Avatar
Incredible Hulctuary Incredible Hulctuary is offline
Member
Non-Actuary
 
Join Date: Jan 2002
Posts: 25,332
Default

Quote:
Originally Posted by Fish Actuary View Post
If this functionality doesn't exist in Access, does it perhaps exist in a different database program?
Serious databases like Oracle and SQL Server have various types of built-in auditing, which you (or more likely, a DBA) can switch on for the particular tables and other structures you want to track. You can also write triggers to log details in ways that the built-in auditing won't do.

Any crude hack you try to push on top of Access will be more work and less effective than switching to a proper database with that functionality.
__________________
The forest was shrinking, but the trees kept voting for the axe because its handle was made from wood, and they thought it was one of them.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 06:36 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.32070 seconds with 11 queries