Delete records containing certain info

[Guest]

Is it possible to delete whole records (rows) in a query or table that
contains certain "text" in a specified field?
Thanks
George

 

[Duane Hookom]

That's kinda how delete queries work. You can set criteria using "Like " to
match text within a field.

 

[Guest]

Duane,
I am fairly new to access and dont know how to set up delete queries. Can
you give explain in more detail pls?
Thanks
George

 

[Duane Hookom]

Create a query that selects the records that you want to delete. Then switch
it to a delete query (find this in the menus) and make sure you have the "*"
in the query grid. You should always view the datasheet prior to running a
delete query so you can confirm the appropriate records will be deleted.

 

[Guest]

I tried what you said and it worked as a select query but I get an error
after changing it to a Delete query and executing.

The error reads: "cannot delete specified table"

I am trying to delete all records in one table that have the same ref number
in a second table.

The table which contains the reocrds to be deleted has a many to one
relationship with the "criteria" table.

What am I doing wrong?

Thanks

 

[Guest]

I am also wondering how to delete records in a query. My situtaion is that I
have appended my table to another file, and now I need to delete all records
in my "description" field of my query that begin with the letter S. I am new
to using the queries so can you please explain it to me in a very easy way to
understand?

 

[Smartin]

Hi dawnab,

First, let's clarify your statement. You can't update a query, but you
can update a table.

Next let's clarify your question: Do you want to (a) delete all records
where the "description" field starts with "S", or (b) "delete" the
description field where it begins with "S"?

These are a bit different - (a) will remove not only the description
field, but everything else in the row. In fact, it will remove the
entire row. (b) will "blank out" only the description field.

So,

(a)
DELETE FROM MyTable
WHERE Description LIKE "S*";

(b)
UPDATE MyTable
SET Description = Null
WHERE Desciption LIKE "S*";

HTH

 

[Guest]

I am still trying to delete these and it is not working. I did what you said
but I must be doing something wrong because it is not allowing me to do it.
I am doing an assignment and it states
using a query, delete all records in the Item table where the description
starts with the letter S

 

[John Vinson]

I am still trying to delete these and it is not working. I did what you said
but I must be doing something wrong because it is not allowing me to do it.
I am doing an assignment and it states
using a query, delete all records in the Item table where the description
starts with the letter S

Than you're doing it wrong.

Since you don't say what you're doing, it's more than a bit difficult
to see what you're doing wrong!

One thing to note: A Query has no independent existance. Deleting a
record from a Query (or using a Delete Query, which sounds more
relevant to this question) deletes data - permanently and irreversibly
- from the Table upon which the query is based. MAKE BACKUPS!

John W. Vinson[MVP]

 

[Guest]

I don't really understand what I am trying to do. The question on my
assignment just states for me to delete the records in the field that begin
with the letter S. I just need know how to delete all of the records that
start with S. I did what the other guy told me but I didn't do it right.
Where am I writing the stuff that he told me to write? What I did was opened
a query, double clicked the asterick, then wrote in the criteria box DELETE
FROM MyTable
WHERE Description LIKE "S*"; I run it and it says that there is an error

 

[John Vinson]

I don't really understand what I am trying to do. The question on my
assignment just states for me to delete the records in the field that begin
with the letter S. I just need know how to delete all of the records that
start with S. I did what the other guy told me but I didn't do it right.
Where am I writing the stuff that he told me to write? What I did was opened
a query, double clicked the asterick, then wrote in the criteria box DELETE
FROM MyTable
WHERE Description LIKE "S*"; I run it and it says that there is an error

A Criterion is what you're searching for.

The query that you wrote will find all records where the field that
you chose contains the text string

DELETE FROM MyTable WHERE DESCRIPTION LIKE "S*";

I really doubt that any records in your table contain that field!

I would suggest that you read your course material which should cover
how to create a Query. First create a query that just *finds* records
where the Description field starts with S (Smartin gave you the SQL
view of a query which does just that; if you don't know what SQL is -
read your notes, or press the magic F1 key and type SQL in the search
box).

Then - again using your course material, or the Help - find out how to
create a Delete query and what a Delete query does.

We WON'T give you your answers. Smartin knows how to do this; I know
how to do this; lots of folks here do. The point of the course is so
that *YOU* will know how to do it - by doing it, and by learning the
techniques that will let you find out how to do other things.


John W. Vinson[MVP]

 

[Guest]

I am not asking you to do my work for me. That's not why I am here. I am
supposed to be learning how to seek out help on the internet through
microsoft. It does not say in my book how to do this, I am supposed to be
finding this answer over the internet. I appreciate you guys trying to help
me out.

 

[Guest]

I guess you were right, I didn't have a field that began with the letter S I
already did it and didnt know it and was trying to do it again. thanx

 

[Smartin]

I am not asking you to do my work for me. That's not why I am here. I am
supposed to be learning how to seek out help on the internet through
microsoft. It does not say in my book how to do this, I am supposed to be
finding this answer over the internet. I appreciate you guys trying to help
me out.

Ho harm no foul, dawnab. Quite honestly I didn't realize you were
working on an assignment. You were mixing up some terminology so my
offering was meant to steer you, clarify your question and provide
solutions for what I perceived to be the two most likely scenarios.

There are a lot of great books... I think "SQL Queries for Mere Mortals"
is another good one. Let it be said too there are innumerable resources
in this and related newsgroups as well.

Best of luck, and if we can be of any more help please don't be afraid
to ask!

 

[Guest]

thank you, i did get my assignment done and got an 80% on it. Thanx for the
help really appreciate it. What you said did work, when I checked the
database i didn't realize that the record was gone and kept trying to do it.
thanx again