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: 104
Thanks: 67
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,573
Thanks: 57
Thanked 1,875 Times in 1,825 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.
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,847
Thanks: 55
Thanked 2,183 Times in 2,094 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: 5,696
Thanks: 138
Thanked 1,532 Times in 1,504 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
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,433
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
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: 104
Thanks: 67
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: 104
Thanks: 67
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: 6,928
Thanks: 92
Thanked 1,707 Times in 1,584 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
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
Previously known as ridders : 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: 5,696
Thanks: 138
Thanked 1,532 Times in 1,504 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
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 11:03 PM.


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

Sponsored Links

How to advertise

Media Kit


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