Deleting info from one table retaining it in another

[Spruk_77]

Hey all,

I am setting up my database as such,

Table1 will contain personnel information such as Social security number and Name

Table2 will contain card information such as card number, date of issue, date of experation

Table3 will be set up to have social security number and card number

In relationships I am linking Social from Table1 To Table3 and Card number from Table2 to table3

I am doing this becuase each person may be issued more than one card depending on how long they work for my company, but each card is only valid for one year. I need to keep a log of who had what card after even after they leave.

I would like to be able to delete their information from Table1 when they leave and have their social and name stay in Table3, so far I am able to get the social to stay becuase of the primary key, but I am unable to get the lastname to stay becuase I can not make it a primary key, way too many Smith's where I work. Thanks for any help

 

[KenHigg]

Could you put a status field in table 1. Say, Status = Active or Inactive?

ken

 

[Spruk_77]

I wish I could, but not everyone gets a card and I would like to keep my records as small as possible. I think things could be messy if I just moved them to an inactive status in stead of outright deleting them.

 

[KenHigg]

Then could you do a history log type table?

ken

 

[Spruk_77]

That sounds great. Not sure how I would need to do it.

 

[KenHigg]

Do a table with name, ss#, etc, and card info. Then have an additional field(s) that contain info such as what each transaction record represents. You could do a transtype field that you store say, 'issue new card' or 'collect card from user'. Then, each time you issue a card, etc., in your db, append a record to this table with the appropriate data.

Make sense?
ken

 

[Spruk_77]

Thanks, I think I got it solves a big problem and it works well, just have to tweak the critera so it only adds the one record. I tried a basic test without critera and it kept doubling the info each time, but that should be easy to figure out to add only one record. Thanks again

 

[KenHigg]

The trick is to keep your db tight - having only one way to add/edit/delete stuff. Cool - glad it works. If you don't mind could you post a watered down version when you get it working, I need some ideas on how to implement a version of this method myself...

ken

 

[Spruk_77]

I will give ya that info as soon as I get the basics set up and test it. I think I am going to cut Table3 as there is no need for it. I "borrowed" the idea from a key control database I saw but there is no need to use it here, until I add in the key control database wich should be easy since the work has all ready been done on it.