473,320 Members | 1,993 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Delete queries

76
Hello,

I am running a delete query to delete all records but those that I specify. Unfortunately, the ones that I specify are over 300 and typing "x" or "y" or "z" etc. is too large for the criteria box. Is there a better way to do this that will fit in the box?

An example of the values are:
E00008081
E00007975
E00006721
E00012066
E00014463
E00006778

Thanks,

Charlie
Feb 22 '07 #1
8 1696
maxamis4
295 Expert 100+
I would create a module to do it programmatically. I can help you with this but I need you to provide more detail about what you are doing including field names and table names.

Good luck

Hello,

I am running a delete query to delete all records but those that I specify. Unfortunately, the ones that I specify are over 300 and typing "x" or "y" or "z" etc. is too large for the criteria box. Is there a better way to do this that will fit in the box?

An example of the values are:
E00008081
E00007975
E00006721
E00012066
E00014463
E00006778

Thanks,

Charlie
Feb 22 '07 #2
ckpoll2
76
Thanks for your reply. The table name is HoursData and the field that I'm working with is PERN. I need to delete all records where the PERN field is anything but what I specify. There are too many to list all of them unless I can attach an Excel sheet of them, but the ones above are a sample of how they look.

Is this enough or do you need more?

I appreciate your help.
Feb 22 '07 #3
ckpoll2
76
Does anyone have any ideas?
Feb 22 '07 #4
maxamis4
295 Expert 100+
If its that simple forget vb try this:
copy and past this into a query. All you have to do is go to the query view and select sql and paste this into it:

Expand|Select|Wrap|Line Numbers
  1. DELETE HoursData.Pern
  2. FROM HoursData
  3. WHERE (((HoursData.Pern)<>[prompt]));
  4.  
This will delete all items that do not equal the number you are looking for. A word of advice, make a copy of the table you will be testing, and test the query to make sure it works.


good luck
Feb 22 '07 #5
maxamis4
295 Expert 100+
Whoops I forgot about the orginial post, give me a couple and I should have something for you in vb

sorry
Feb 22 '07 #6
maxamis4
295 Expert 100+
Quick question how are you determining which ones you want and which ones you want to keep? Is there a process which identifies this?
Feb 22 '07 #7
ckpoll2
76
Sorry for the delayed response...

The process for identifying the ones to keep and those to delete are not based on a process, they are random numbers that identify employees. There are probably 4000 people that data exists for, but I only need data for my department, around 300 people. I can send you a complete list of the numbers if that would help. I have them in Excel format or I can just post all of them in a post here. What would work best for you?
Feb 23 '07 #8
NeoPa
32,556 Expert Mod 16PB
Charlie,
Post your data into a new (scratch) table in Access or (even easier) link in your Excel workbook as a table. In our example we'll call it tblExcel. Then run the SQL :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [HoursData] LEFT JOIN [tblExcel]
  2.     ON HoursData.PERN=tblEXCEL.PERN
  3. SET HoursData.PERN='DELETEME'
  4. WHERE tblEXCEL.PERN Is Null
Followed by the SQL :
Expand|Select|Wrap|Line Numbers
  1. DELETE
  2. FROM HoursData
  3. WHERE [PERN]='DELETEME'
Feb 26 '07 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: MAB71 | last post by:
I'm running an ISP database in SQL 6.5 which has a table 'calls'. When the new month starts I create a new table with the same fields and move the data of previous month into that table and delete...
2
by: Dalan | last post by:
Having searched the archives regarding a Delete Query, I found nothing specific to my need, although there seems to be a plethora of ideas and suggestions on queries in general. I have used Delete...
13
by: N. Graves | last post by:
Thanks for take time to read my question!! I'm using code that will automatically delete rows of data in a field and of course when you do this Access will prompt you that you are about to...
3
by: John Baker | last post by:
Hi: I have a table with many months of data on it. I am attempting to create a delete transaction, based on matching a date in the table with a date in another table. The match works perfectly...
14
by: Darin | last post by:
I have a table that I want to delete specific records from based on data in other tables. I'm more familiar with Access '97, but am now using 2003, but the database is in 2000 format. In '97, I...
6
by: manning_news | last post by:
Using A2003. I'm attempting to delete all queries in an mdb. I was experimenting with the following coding and discovered that it deletes exactly half the queries each time it's run. If I have...
3
by: Bob Bedford | last post by:
hello I'm looking for some functions or objects allowing to select-insert-update-delete from any table in a mysql database without the need to create a new query every time. Example: ...
17
by: (PeteCresswell) | last post by:
I've got apps where you *really* wouldn't want to delete certain items by accident, but the users just have to have a "Delete" button. My current strategies: Plan A:...
1
by: Matt | last post by:
I am writing a DELETE statement and I want to filter the records using another SELECT statement. My SELECT statement is a GROUP BY query that grabs all social security numbers from the "Data...
1
by: austin1539 | last post by:
-Access 2002 -Windows XP Pro Trying to run a DELETE Query to delete each entry in the table 'Data' where the field 'AssocID' matches the field 'AssocID' in the table 'Queries' and the date is...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.