Delete certain data from a table

[Tor_Fey]

Good Morning;

I have a table which holds personal data (tbl_staffnames), unfortunately it holds details on staff that have left our employment. In this table is a yes/no field called active, what I’d like to do is delete all the data in the following fields:

officer_name
full_name
email_addy
Supervisor_name
unit
surname
dob
address1
address2
address3
address4
address5
postcode
phone
econtact
eaddress1
eaddress2
eaddress3
eaddress4
eaddress5
epostcode
ephone
photo
servicestart
offsecrets
warantheld
idcard
idexpires
payroll_no
group_in
email_addy_work2
email_addy_pers1
email_addy_pers2
phone_confid
phone2
phone2_confid
mobile_pers
mobile_pers_confid
mobile_work
mobile_work_confid
econtact2
ephone2
econtact3
ephone3
credit_card_issued
credit_card_number
picfile
immigration_warrent
vet_level
vet_date
post_office_IDcard
seaterminal_IDcard
seaterminal_IDcard_expires
airport_IDcard
airport_IDcard_expires
GSAT_training
GSAT_expiry_date

I need to create a delete query; that will delete data where the active status is false, but I also need to leave the Computer_Name, and active fields untouched and not delete anything from these fields. Any pointers on how to achieve this would greatly be appreciated.

Kind Regards
Tor Fey

 

[jdraw]

This is all in 1 table (tbl_staffnames)?

 

[arnelgp]

Yiu set all values to Null on your query, except the field you want to retain, with criteria Active=False.

 

[Minty]

You need an update query

UPDATE tbl_staffnames 
SET officer_name = Null,
full_name = Null,
email_addy = Null,
Supervisor_name = Null,
unit = Null,
surname = Null,
dob = Null,
address1 = Null,
address2 = Null,
address3 = Null,
address4 = Null,
address5 = Null,
postcode = Null,
phone = Null,
econtact = Null,
eaddress1 = Null,
eaddress2 = Null,
eaddress3 = Null,
eaddress4 = Null,
eaddress5 = Null,
epostcode = Null,
ephone = Null,
photo = Null,
servicestart = Null,
offsecrets = Null,
warantheld = Null,
idcard = Null,
idexpires = Null,
payroll_no = Null,
group_in = Null,
email_addy_work2 = Null,
email_addy_pers1 = Null,
email_addy_pers2 = Null,
phone_confid = Null,
phone2 = Null,
phone2_confid = Null,
mobile_pers = Null,
mobile_pers_confid = Null,
mobile_work = Null,
mobile_work_confid = Null,
econtact2 = Null,
ephone2 = Null,
econtact3 = Null,
ephone3 = Null,
credit_card_issued = Null,
credit_card_number = Null,
picfile = Null,
immigration_warrent = Null,
vet_level = Null,
vet_date = Null,
post_office_IDcard = Null,
seaterminal_IDcard = Null,
seaterminal_IDcard_expires = Null,
airport_IDcard = Null,
airport_IDcard_expires = Null,
GSAT_training = Null,  
GSAT_expiry_dat = Null

Where Active = False

 

[Pat Hartman]

Why in the world would you blank out the identifying information and leave the row? As long as the record needs to remain (and it should remain for the life of the application), ALL the data you have should also remain untouched.

 

[Tor_Fey]

Thanks Minty/arnelgp; much appreciated

Exactly what i was looking for.

Regards
Tor Fey

 

[Tor_Fey]

Welcome to the world of GDPR In Europe.

It was identified we aren't allowed to keep this data for people who no longer work for us.

Regards
Tor Fey

Why in the world would you blank out the identifying information and leave the row? As long as the record needs to remain (and it should remain for the life of the application), ALL the data you have should also remain untouched.

 

[isladogs]

I think this is a misinterpretation of GDPR though I'm no expert.

Ex-employee information is often needed for purposes such as references etc.
Perhaps some fields could be removed without affecting those, most would be needed.
I've been asked for references more than ten years after employees have left.

 

[Minty]

I would agree with Ridders that someone hasn't interpreted GDPR very well, this is privately gathered employee information.

It's not publicly gathered or available to anyone outside of the organisation that should be sufficient protection.