UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Delete Records From Multiple Tables, Access 2016    
 
   
Felixx_Bodhran
post Feb 26 2018, 11:50 AM
Post#1



Posts: 27
Joined: 12-February 18
From: UK


Hi,

I hope you guys can help, I've been doing lots of google searches but nothing has worked, I've tried so many I've forgotten what they were.

I have created the following tables:

tblEmployees
EmployeeID
FirstName
LastName
StartDate
DepartmentFKID
OfficeFKID

tblDepartments
DepartmentID
Department

tblCourses
CourseID
Course

tblOffices
OfficeID
Office

jtblCourseEmployee
CourseFKID
EmployeeFKID
CourseDeadlineDate

I have the queries I need and have started on the forms. I have created forms for adding and amending records but now I want to have the ability to delete records. The problem is I don't know how to go about it as a person will have 9 courses assigned to them that they are due to complete or have completed. Please can you advise the best way to delete a person and their assigned courses? If a query or form can do this i'd like to be able to search for the person's name and should there be two of the same name to choose which one to remove.

Thanks
Go to the top of the page
 
theDBguy
post Feb 26 2018, 12:00 PM
Post#2


Access Wiki and Forums Moderator
Posts: 72,370
Joined: 19-June 07
From: SunnySandyEggo


Hi Felixx,

Welcome to UtterAccess!
welcome2UA.gif

What you want is not really hard. I recommend using a Form to initiate the Delete process. You could place a Textbox for the user enter the name and you can use a Listbox to display all matching names.

The user can then select the one to delete from the list and a delete button can perform the delete operation using the ID of the selected record.

You will have to delete the child records first before deleting the parent record unless you have Cascade Delete set up in the table relationship.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Larry Larsen
post Feb 26 2018, 12:26 PM
Post#3


UA Editor + Utterly Certified
Posts: 24,169
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

As has been suggested a simple and easy way of deleting a record (both parent & child records)..

Please may I ask "why" go to lengths to "delete" data that may be used as historical data from the database..

There are options that can be used to apply an "inactive" status of the record/records..
(Archive data..)

It always seems difficult to understand why developers go to great lengths in creating RDB's, where by the aim is to capture data.

Then to allow users/admin's to delete it... compute.gif iconfused.gif
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
zaxbat
post Feb 26 2018, 01:13 PM
Post#4



Posts: 932
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Right.....seems nobody deletes anything anymore....memory is cheap. And like Larry said....just need a Yes/No column/field for active/inactive and use a filter to normally only show the Active records. That's fairly typical. Though Access does not display a 'delete' button, if you want to delete something.....just right click then pick delete. Alternately, you can highlight the record in a datasheet and hit the 'delete' button on your keyboard. Though, this capability may be disabled in the properties window for the form/subform holding the table. And true that....if the record has children they have to be deleted first unless you set up the link to cascade downward and delete everything. Pretty scary, that!

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
projecttoday
post Feb 26 2018, 08:20 PM
Post#5


UtterAccess VIP
Posts: 9,685
Joined: 10-February 04
From: South Charleston, WV


Add a delete date field to tblCourseEmployee.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
Felixx_Bodhran
post Feb 27 2018, 12:08 PM
Post#6



Posts: 27
Joined: 12-February 18
From: UK


Hi all

Thanks for your replies. I need to provide an easy way to delete records just in case a person leaves as there is then no legitimate interest in keeping the information, especially with GDPR coming soon.

DBguy thank you very much for your suggestion it works perfectly.
Go to the top of the page
 
theDBguy
post Feb 27 2018, 12:49 PM
Post#7


Access Wiki and Forums Moderator
Posts: 72,370
Joined: 19-June 07
From: SunnySandyEggo


Hi Felixx,

You're welcome. We're all happy to assist. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th June 2018 - 02:32 PM