Delete certain data from a table (1 Viewer)

T

Tor_Fey

Registered User.
Local time
Today, 11:49
Joined
Feb 8, 2013
Messages
121
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:

Code: 
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

jdraw

Super Moderator
Staff member
Local time
Today, 06:49
Joined
Jan 23, 2006
Messages
15,380
This is all in 1 table (tbl_staffnames)?
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:49
Joined
May 7, 2009
Messages
19,226
Yiu set all values to Null on your query, except the field you want to retain, with criteria Active=False.
 
Minty

Minty

AWF VIP
Local time
Today, 11:49
Joined
Jul 26, 2013
Messages
10,367
You need an update query
Code: 
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
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:49
Joined
Feb 19, 2002
Messages
43,196
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.
 
T

Tor_Fey

Registered User.
Local time
Today, 11:49
Joined
Feb 8, 2013
Messages
121
Thanks Minty/arnelgp; much appreciated

Exactly what i was looking for.

Regards
Tor Fey
 
T

Tor_Fey

Registered User.
Local time
Today, 11:49
Joined
Feb 8, 2013
Messages
121
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

Pat Hartman said:
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.
Click to expand...
 
isladogs

isladogs

MVP / VIP
Local time
Today, 11:49
Joined
Jan 14, 2017
Messages
18,207
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

Minty

AWF VIP
Local time
Today, 11:49
Joined
Jul 26, 2013
Messages
10,367
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.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom