Delete some records

[Linda RQ]

Hi Everyone,

The database was mad in Access 97 then converted to 2000. I am running it
in 2003 but haven't converted it yet. The only thing I want to do is to
delete the records in the Number field of a table. I read all the
instructions on the microsoft site about append queries but they have a
bunch of other things added, like delete records for the last 90 days...I
took what I thought I needed but my problem is that my query is deleting all
the records in my table. I use the query grid but here is the sql. I
created a query based on my client table. I added Number to my query grid
and in the delete row, when I go to run the query it says it's going to
delete 4200 rows. I just want the records in the Number column deleted.
How can I do this?

DELETE Clients.Number
FROM Clients;


Thanks,
Linda

 

[Guest]

When you delete records it removes ALL fields at once. Maybe what you want
to do is Update the field. Search that on the microsoft site.

 

[Guest]

Your terminology is confused. there are no records in a field. A table has
records. A record has fields. Fields contain data.
Delete means you want to delete a record or records.
Update means you want to change the value in a field or fields in a record
or records.
Append means you want to Add new records to a table.

So, I believe you want to change the value of a field in your table. Since
you use the word delete, I am assuming you mean you want to Update the value
of the field to a Null value.

The field will not go away, but it will no longer have any vaule.

To do this, create a new query in the query builder. Select Update as the
query type. Select the field you want to update. Type Null in the Update To
row of the field's column. Run the query. Now that field in all the records
in the table will be Null.

It is possible you are saying you actually want to remove the field from the
table and no longer use it, that is done in design view. Open table in
design view and delete the field name from the list. The field will no
longer be a part of the table and all the data in that field will be lost.

One other thing - Number is a very bad name for a field.

 

[Linda RQ]

Yes, you are right and thanks for explaining that in a way I finally
understand....you must be a teacher. I want to remove all the records from
my Number Field (it's really not called Number I just disguised the name so
I didn't get yelled at for having a certain type of number in my
database)<g> I learned about the "Do not name list" in these groups.

I don't want to delete the field yet because obviously I still have a
million things to learn in access and I know that deleting a field can cause
issues in forms and reports that I don't want right now. I just want to get
these numbers out of our database.

I was so happy when I saw you gave me some instructions but....alas, I still
can't get the darn numbers deleted. I tried out the microsoft site again
and looked up append query as Karl suggested but it seems like they are
talking about 2 tables in the query. I tried several variations of things
but the numbers are still there. At this point, I would have had them all
deleted one by one but it's time I learned how to do this.

This is what I did below. I created an append query based on my clients
table but there is no where to "Type Null in the Update To row" of the
field's column. The only options in my query grid are Field, Table, Total,
Sort, Appent To, Criteria, or. I added my number field to the query grid
but can't see the "Update To row" I typed Null in that row but that didn't
do anything. I also tried typing Null in the column next to the Number
column but got an error.

INSERT INTO Clients ( Number )
SELECT Clients.Number
FROM Clients
GROUP BY Clients.Number;

INSERT INTO Clients ( [Null] )
SELECT Clients.Number
FROM Clients
GROUP BY Clients.Number;

Sorry to be so dense but I think I almost have it?

Linda

 

[gls858]

Yes, you are right and thanks for explaining that in a way I finally
understand....you must be a teacher. I want to remove all the records from
my Number Field (it's really not called Number I just disguised the name so
I didn't get yelled at for having a certain type of number in my
database)<g> I learned about the "Do not name list" in these groups.

snip<

I interpret this to mean that you have autonumber as your client ID.
Is that correct? If so I think you SOL. An autonumber cannot be updated.

gls858

 

[Guest]

Not a teacher, not even a very good student

What you want is not an apppend. Remember, Append adds records to a table.
What you want is an update query. Update modifies data.

As another poster said, if this is an autonumber type field, you can't do
this.

Assuming it is not an autonumber field, Let's start from the top and follow
these instructions exactly as written.

Create a new query.

Select the table that has the field you want to update

Put the name of the field in the Field: row of the first column of the
query builder grid.

Run the query, since the default query type is a Select query, you will see
a listing of the value in that field for every record in the table.

Go back to design mode.

From the Query Type icon or by right clickin in the area of the query
builder where the table layout is shown, Select Update Query.

Now you will see a new row added to the query builder, Update To:

In that row, type NULL (exactly as shown)

Run the query.

Go back to design view and select Select Query.

You will see the Update To: row disappear.

run the query again. Now you will see that every record is blank.

Yes, you are right and thanks for explaining that in a way I finally
understand....you must be a teacher. I want to remove all the records from
my Number Field (it's really not called Number I just disguised the name so
I didn't get yelled at for having a certain type of number in my
database)<g> I learned about the "Do not name list" in these groups.

I don't want to delete the field yet because obviously I still have a
million things to learn in access and I know that deleting a field can cause
issues in forms and reports that I don't want right now. I just want to get
these numbers out of our database.

I was so happy when I saw you gave me some instructions but....alas, I still
can't get the darn numbers deleted. I tried out the microsoft site again
and looked up append query as Karl suggested but it seems like they are
talking about 2 tables in the query. I tried several variations of things
but the numbers are still there. At this point, I would have had them all
deleted one by one but it's time I learned how to do this.

This is what I did below. I created an append query based on my clients
table but there is no where to "Type Null in the Update To row" of the
field's column. The only options in my query grid are Field, Table, Total,
Sort, Appent To, Criteria, or. I added my number field to the query grid
but can't see the "Update To row" I typed Null in that row but that didn't
do anything. I also tried typing Null in the column next to the Number
column but got an error.

INSERT INTO Clients ( Number )
SELECT Clients.Number
FROM Clients
GROUP BY Clients.Number;

INSERT INTO Clients ( [Null] )
SELECT Clients.Number
FROM Clients
GROUP BY Clients.Number;

Sorry to be so dense but I think I almost have it?

Linda

Your terminology is confused. there are no records in a field. A table
has
records. A record has fields. Fields contain data.
Delete means you want to delete a record or records.
Update means you want to change the value in a field or fields in a record
or records.
Append means you want to Add new records to a table.

So, I believe you want to change the value of a field in your table.
Since
you use the word delete, I am assuming you mean you want to Update the
value
of the field to a Null value.

The field will not go away, but it will no longer have any vaule.

To do this, create a new query in the query builder. Select Update as the
query type. Select the field you want to update. Type Null in the Update
To
row of the field's column. Run the query. Now that field in all the
records
in the table will be Null.

It is possible you are saying you actually want to remove the field from
the
table and no longer use it, that is done in design view. Open table in
design view and delete the field name from the list. The field will no
longer be a part of the table and all the data in that field will be lost.

One other thing - Number is a very bad name for a field.

 

[Linda RQ]

The client ID is an autonumber but that's not the number I am trying to
delete.

My table is like this

ClientID_PK AutoNumber
ClientFName
ClientLName
ClientNumber, I want to delete all of these numbers for all my records but I
want to keep the field name intact for now.

Linda

 

[Linda RQ]

Good Grief! I don't know why I selected Append and didn't notice Update!
That was easy. I knew it was easy so I couldn't figure out why it was so
hard. Append, Update, Delete. I think that is a lot for me to learn in one
day... I need to go take a nap now.

Well, you are a very patient teacher and I thank you. I have about 20 tasks
that we will be using these queries for now that I know how to use them.

Linda

Not a teacher, not even a very good student

What you want is not an apppend. Remember, Append adds records to a
table.
What you want is an update query. Update modifies data.

As another poster said, if this is an autonumber type field, you can't do
this.

Assuming it is not an autonumber field, Let's start from the top and
follow
these instructions exactly as written.

Create a new query.

Select the table that has the field you want to update

Put the name of the field in the Field: row of the first column of the
query builder grid.

Run the query, since the default query type is a Select query, you will
see
a listing of the value in that field for every record in the table.

Go back to design mode.

From the Query Type icon or by right clickin in the area of the query
builder where the table layout is shown, Select Update Query.

Now you will see a new row added to the query builder, Update To:

In that row, type NULL (exactly as shown)

Run the query.

Go back to design view and select Select Query.

You will see the Update To: row disappear.

run the query again. Now you will see that every record is blank.

Yes, you are right and thanks for explaining that in a way I finally
understand....you must be a teacher. I want to remove all the records
from
my Number Field (it's really not called Number I just disguised the name
so
I didn't get yelled at for having a certain type of number in my
database)<g> I learned about the "Do not name list" in these groups.

I don't want to delete the field yet because obviously I still have a
million things to learn in access and I know that deleting a field can
cause
issues in forms and reports that I don't want right now. I just want to
get
these numbers out of our database.

I was so happy when I saw you gave me some instructions but....alas, I
still
can't get the darn numbers deleted. I tried out the microsoft site again
and looked up append query as Karl suggested but it seems like they are
talking about 2 tables in the query. I tried several variations of
things
but the numbers are still there. At this point, I would have had them
all
deleted one by one but it's time I learned how to do this.

This is what I did below. I created an append query based on my clients
table but there is no where to "Type Null in the Update To row" of the
field's column. The only options in my query grid are Field, Table,
Total,
Sort, Appent To, Criteria, or. I added my number field to the query grid
but can't see the "Update To row" I typed Null in that row but that
didn't
do anything. I also tried typing Null in the column next to the Number
column but got an error.

INSERT INTO Clients ( Number )
SELECT Clients.Number
FROM Clients
GROUP BY Clients.Number;

INSERT INTO Clients ( [Null] )
SELECT Clients.Number
FROM Clients
GROUP BY Clients.Number;

Sorry to be so dense but I think I almost have it?

Linda

Your terminology is confused. there are no records in a field. A
table
has
records. A record has fields. Fields contain data.
Delete means you want to delete a record or records.
Update means you want to change the value in a field or fields in a
record
or records.
Append means you want to Add new records to a table.

So, I believe you want to change the value of a field in your table.
Since
you use the word delete, I am assuming you mean you want to Update the
value
of the field to a Null value.

The field will not go away, but it will no longer have any vaule.

To do this, create a new query in the query builder. Select Update as
the
query type. Select the field you want to update. Type Null in the
Update
To
row of the field's column. Run the query. Now that field in all the
records
in the table will be Null.

It is possible you are saying you actually want to remove the field
from
the
table and no longer use it, that is done in design view. Open table in
design view and delete the field name from the list. The field will no
longer be a part of the table and all the data in that field will be
lost.

One other thing - Number is a very bad name for a field.

:

Hi Everyone,

The database was mad in Access 97 then converted to 2000. I am
running
it
in 2003 but haven't converted it yet. The only thing I want to do is
to
delete the records in the Number field of a table. I read all the
instructions on the microsoft site about append queries but they have
a
bunch of other things added, like delete records for the last 90
days...I
took what I thought I needed but my problem is that my query is
deleting
all
the records in my table. I use the query grid but here is the sql. I
created a query based on my client table. I added Number to my query
grid
and in the delete row, when I go to run the query it says it's going
to
delete 4200 rows. I just want the records in the Number column
deleted.
How can I do this?

DELETE Clients.Number
FROM Clients;


Thanks,
Linda