Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-07-2018, 02:50 AM   #1
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 109
Thanks: 83
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Question Delete certain data from a table

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 Id 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

Tor_Fey is offline   Reply With Quote
Old 06-07-2018, 03:14 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,785
Thanks: 68
Thanked 1,944 Times in 1,892 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Delete certain data from a table

This is all in 1 table (tbl_staffnames)?
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 06-07-2018, 05:40 AM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,994
Thanks: 56
Thanked 2,247 Times in 2,156 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Delete certain data from a table

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Tor_Fey (06-10-2018)
Old 06-07-2018, 05:53 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,163
Thanks: 146
Thanked 1,658 Times in 1,630 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Delete certain data from a table

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
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Tor_Fey (06-10-2018)
Old 06-07-2018, 11:14 AM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,555
Thanks: 13
Thanked 1,452 Times in 1,384 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Delete certain data from a table

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 06-10-2018, 10:30 PM   #6
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 109
Thanks: 83
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Re: Delete certain data from a table

Thanks Minty/arnelgp; much appreciated

Exactly what i was looking for.

Regards
Tor Fey
Tor_Fey is offline   Reply With Quote
Old 06-10-2018, 10:31 PM   #7
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 109
Thanks: 83
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Re: Delete certain data from a table

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

Quote:
Originally Posted by Pat Hartman View Post
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 is offline   Reply With Quote
Old 06-10-2018, 11:12 PM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,921
Thanks: 105
Thanked 2,390 Times in 2,197 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Delete certain data from a table

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-11-2018, 01:46 AM   #9
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,163
Thanks: 146
Thanked 1,658 Times in 1,630 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Delete certain data from a table

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.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Reply

Tags
data , delete , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete table data syedadnan Tables 3 09-06-2016 09:21 AM
how to trasfer where query to table and delete data from old table mbmbmbmb Queries 2 04-02-2013 08:39 AM
delete data in a table Mindzeye General 3 03-13-2013 12:31 PM
Question Transfer row data and Delete row from table after data entered in a certain field BPBP General 3 05-17-2009 02:52 PM
Need to move data from table a to table b and delete it kiask2343 Queries 4 09-20-2006 04:08 PM




All times are GMT -8. The time now is 09:58 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World