[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

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
0
kevluck373
Asked:
kevluck373
  • 9
  • 5
1 Solution
 
peter57rCommented:
The database has no 'contract' object.  All there is is a course object which has a contract number as one of its fields (although the contract numbers on the course records have nothing in common with the contents of a table called tblContractNumbers.)

So there are no contract records as such so I don't have any idea what it is you are expecting to delete.
0
 
Ken ButtersCommented:
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.
0
 
kevluck373Author Commented:
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kevluck373Author Commented:
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.
0
 
kevluck373Author Commented:
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?
0
 
kevluck373Author Commented:
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.
0
 
Ken ButtersCommented:
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
0
 
kevluck373Author Commented:
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
0
 
Ken ButtersCommented:
You did it right....

ok... so looking at the frmMainSearch... it is populated by the following query:

SELECT tblCourses.CrsID, tblCourses.PrgmCode, tblCourses.CourseNumber, tblCourses.ContractNumber, tblCourses.ClassTitle, tblCourses.StartDate, tblCourses.EndDate, tblCourses.[10%Date], tblCourses.Term, tblCourses.ClassHours, tblCourses.CEUs, tblCourses.Weeks, tblCourses.NoClassDays, tblCourses.SpecInstructions, tblCourses.Certification, tblCourses.Location, tblCourses.Building, tblCourses.Room, tblCourses.InstructorDatatelID, tblCourses.InstructorName, tblCourses.MinToStart, tblCourses.MaxToEnroll, tblCourses.[Section#], tblCourses.BudgetCode, tblCourses.SupervisorID, tblCourses.DateEntered, tblCourses.DateRevised, tblCourses.EndCoursePay, tblCourses.InsuranceReq, tblCourses.Fees, tblCourses.Revised, tblCourses.TotalMiles, tblCourses.MileageRate, tblInstructor.InstructorName, tblInstructor.InstructorDatatelID, tblInstructor.City, tblInstructor.State, tblInstructor.ZipCode, tblInstructor.InsID, tblInstructor.InstructorName, tblInstructor.InstructorName, tblInstructor.InstructorName, tblCourses.InstructorDatatelID FROM tblCourses LEFT JOIN tblInstructor ON tblCourses.InstructorDatatelID=tblInstructor.InstructorDatatelID; 

Open in new window

the key part is the "From"

FROM tblCourses LEFT JOIN tblInstructor ON tblCourses.InstructorDatatelID=tblInstructor.InstructorDatatelID;

so for that form ... it is pulling the data from the tblCourses.... even though we set the contract number to NULL.

I tried deleting all the records in the table tblcourses.... and when I did that... the frmMainSearch is completely blank.... that may be want you want.

Easiest way to do that is:

Step 1 : Double Click on the table tblCourses...
Step 2 : Highlight all the rows
Step 3 : Right click and select delete record
Step 4 : Confirm that is what you want to do.

Follow-up note.... after above steps... I opened the frmEnterNewCourse2.... filled in some bogus data as a test...and hit "Save and Close" Button.

closed that form

re-opened frmMainSearch... and it had the one new record I entered via frmEnterNewCourse2.
0
 
kevluck373Author Commented:
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?
0
 
kevluck373Author Commented:
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
0
 
Ken ButtersCommented:
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.
0
 
Ken ButtersCommented:
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.
0
 
kevluck373Author Commented:
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.
0
 
kevluck373Author Commented:
Easy to work w/
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now