kevluck373
asked on
delete multiple records in access
I don’t use access that much but was asked by the con ed department at the college if it is possible for me to change something on the included database.
This doesn’t seem too difficult, and may figure it out spending more time playing w/ it, but thought I’d ask to make sure I get all the steps done & not leave something out.
When you open the database you’ll see the main form, and on that form you’ll see contract. It seems to me contract has 13 records in it, but the department wants the 13 records in the contract deleted.
The department wants to leave the contracts on the main form as is but again get rid of all the records. The contract part of the main form will still be there and have the ability to make new contracts; just want to current records gone.
Is there a way to delete all the records at once? I’d think there’s a way to delete all 13 records instead of doing 1 record at a time.
The department tells me they have more than 1 database to get rid of more than 1 record in and wanted to know how.
I honestly don’t know if there’s any security on this database?
Thanks
Sustainable-Ag-2013.accdb
This doesn’t seem too difficult, and may figure it out spending more time playing w/ it, but thought I’d ask to make sure I get all the steps done & not leave something out.
When you open the database you’ll see the main form, and on that form you’ll see contract. It seems to me contract has 13 records in it, but the department wants the 13 records in the contract deleted.
The department wants to leave the contracts on the main form as is but again get rid of all the records. The contract part of the main form will still be there and have the ability to make new contracts; just want to current records gone.
Is there a way to delete all the records at once? I’d think there’s a way to delete all 13 records instead of doing 1 record at a time.
The department tells me they have more than 1 database to get rid of more than 1 record in and wanted to know how.
I honestly don’t know if there’s any security on this database?
Thanks
Sustainable-Ag-2013.accdb
The 13 records are the records in the table called tblCourses.
The table courses has a column called contractNumber.
I would assume they want you to remove contractNumber from each of the courses....
To do this... create a new query... right click the tab on the new query... and select SQL view...
Then delete anything in that query if it exists... and paste in the following:
update tblcourses set contractnumber = null;
click the red exclamation point to run it... and it will remove the contracts from each course.
Note: this is not deleting records... but removing the contracts from the courses... You need to verify if that is what you are really trying to do before proceeding. You can always make a copy of the db first and try it ... to verify that you are getting the results you desire.
The table courses has a column called contractNumber.
I would assume they want you to remove contractNumber from each of the courses....
To do this... create a new query... right click the tab on the new query... and select SQL view...
Then delete anything in that query if it exists... and paste in the following:
update tblcourses set contractnumber = null;
click the red exclamation point to run it... and it will remove the contracts from each course.
Note: this is not deleting records... but removing the contracts from the courses... You need to verify if that is what you are really trying to do before proceeding. You can always make a copy of the db first and try it ... to verify that you are getting the results you desire.
ASKER
Sorry I know I'm not explaining this too well because I don't know much technical layout of the database.
When I try the query a Windows comes up that is screen shot 1. I'm not sure what I need to select if anything.
I hit close on the box that comes up, copy & paste, run the query, then see you are about to update 13 rows.
I save the query when it asks & save the database too, but when I re open the modified database the 13 records are still there; so it tells me on the frmmainsearch
1.jpg
mainfrm.jpg
When I try the query a Windows comes up that is screen shot 1. I'm not sure what I need to select if anything.
I hit close on the box that comes up, copy & paste, run the query, then see you are about to update 13 rows.
I save the query when it asks & save the database too, but when I re open the modified database the 13 records are still there; so it tells me on the frmmainsearch
1.jpg
mainfrm.jpg
ASKER
looking at table contract #s there are 500 hundred and some records. what exactly are the 13 records composed of not sure, but how to delete the 1 of 13 on the main form is where I'm stuck.
ASKER
The frmnewcourse2 seems to have the 13 records, and if I delete the records individually from there, save the database, open the database the mainfrm then shows record 1 of 1, which is what I think they want.
But being I had to delete each record individually is there a way to delete all 13 at once?
But being I had to delete each record individually is there a way to delete all 13 at once?
ASKER
I suppose it'd be a query to allow me to delete all 13 records at once but not sure what would be in the query.
Step 1 : Go to Create Tab
Step 2 : Click on Query Design
Step 3 : Hit Close Button
Step 4 : In upper right hand corner select SQL View
Step 5: Paste in main Window the following :
update tblcourses set contractnumber = null;
At this point... it should look like attached screenshot..
Step 6: Hit Red Exclamation point to run the update... and when asked if you are sure... say yes.
Access-Screenshot.jpg
Step 2 : Click on Query Design
Step 3 : Hit Close Button
Step 4 : In upper right hand corner select SQL View
Step 5: Paste in main Window the following :
update tblcourses set contractnumber = null;
At this point... it should look like attached screenshot..
Step 6: Hit Red Exclamation point to run the update... and when asked if you are sure... say yes.
Access-Screenshot.jpg
ASKER
I've included the screen shots of running the query, and it seems I get the messages I should, run the query & get the message you want to update 13 records.
I say yes, go back to main form and still lists 1 of 13. I click the ave icon and asked to save the query, which I do, and then save a second time to save the database itself; however, when I open the database again I still see 1 of 13, but the query is still there and saved.
Maybe somehow I'm not in SQL view?
Thanks
querymessage.jpg
runquery.jpg
I say yes, go back to main form and still lists 1 of 13. I click the ave icon and asked to save the query, which I do, and then save a second time to save the database itself; however, when I open the database again I still see 1 of 13, but the query is still there and saved.
Maybe somehow I'm not in SQL view?
Thanks
querymessage.jpg
runquery.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Surprisingly when I was messing w/ the database today I tried what you mentioned, and as you said the main form comes up blank.
When I blank the form it says delete records instead of update when I do the query.
As far as why the main form becomes blank that's where I'm stuck. Unless the main form involves the tblcourses somehow?
When I blank the form it says delete records instead of update when I do the query.
As far as why the main form becomes blank that's where I'm stuck. Unless the main form involves the tblcourses somehow?
ASKER
Sorry I didn't read your total response. I'm trying to enter something in newforms2 but apparently not sure of what specifics the entries have to have.
This is the screen I say when trying to make a bogus record
notinlist.jpg
This is the screen I say when trying to make a bogus record
notinlist.jpg
When the main form first displays it says... get me a list of all the courses... and by the way... include info from all the instructors for each course.
You had 13 courses in tblCourses... so that is why you had 13 records to display in your main form.
when you delete all 13 courses.. there is nothing for the main form to display... which is accurate.
In order to get a course (or courses) for the main form to display after this... then you have to add courses. The main form is NOT set up to be able to add courses... so you cannot do it from there. Based on the names of your forms... I figured you would add a new course via frmEnterNewCourse2.
When I tried that... and hit save... it worked...
Once you create a new course, then you can go back to main form... again ... which is just a query on existing data... it will pull the course data from tblCourses and instructor data etc.... and display it on the form. In this case, after I entered a single course via fmEnterNewCourse2... it displayed correctly on the main form with a single record.
You had 13 courses in tblCourses... so that is why you had 13 records to display in your main form.
when you delete all 13 courses.. there is nothing for the main form to display... which is accurate.
In order to get a course (or courses) for the main form to display after this... then you have to add courses. The main form is NOT set up to be able to add courses... so you cannot do it from there. Based on the names of your forms... I figured you would add a new course via frmEnterNewCourse2.
When I tried that... and hit save... it worked...
Once you create a new course, then you can go back to main form... again ... which is just a query on existing data... it will pull the course data from tblCourses and instructor data etc.... and display it on the form. In this case, after I entered a single course via fmEnterNewCourse2... it displayed correctly on the main form with a single record.
In answer to your last question... do not try to enter any data when there is a dropdown... you should use that... that will make sure you only select valid values.
ASKER
Come to find out I only had to add a course #, save, re open database & see 1 of 1 record when I open the database again.
I can't thank you enough for your time & patience w/ me for this. Hopefully for the other databases they want to do this to will be the same steps to do as they want.
I can't thank you enough for your time & patience w/ me for this. Hopefully for the other databases they want to do this to will be the same steps to do as they want.
ASKER
Easy to work w/
So there are no contract records as such so I don't have any idea what it is you are expecting to delete.