Just getting around

[Jennifer]

Hi there,
Major 1st timer here. Dappling my feet a bit. I have been asked to clean up
some tables for the upcoming year. and i have a very basic question.
How do you delete data in a column without deleting the setting for that
column in the table. In excel i can easily select the 200 entries and hit
delete. I can't seem to just select the data in one column without getting
all the other data in column a:g

So basic i know. Thank you.

 

[Wayne-I-M]

What looks like an excell column in your table is (in access) a field.
So if you have
ID
1stName
Surnname
HairColour
DateOfBirth

In your table the DateOfBirth will be shown in a "column"

To delete this information you could use a delete query. They are quite
simple BUT DO A BACKUP FIRST.

Open a new query bring in the table you want to work on.
Double click the field. In the criteria row insert the criteria - so if you
want to delete all Johns. Put "John" in the criteria row.
If you want to delete everyone older than me (a very young 46) double click
the date of birth field and in the criteria row put
DateDiff("yyyy",[CDDateofBirth],Date())-IIf(Format([CDDateofBirth],"mmdd")>Format(Date(),"mmdd"),1,0)>46
Or if you want to delete people with red hair and 1st name Kelly put both of
these fields in the query and then add details to both criteria.
There are a limitless amount of criteria you can use.

In the query type drop down select delete query then click the big red ! at
the top of the screen - AFTER YOU HAVE DONE A BACKUP (just in case)

Good luck

 

[John Spencer]

WHOA! STOP! CEASE! DESIST!

That method will delete rows of data. You DO NOT want to use a delete query
to clear a column. You want to use an UPDATE query and set the column's
(field's) value to null.

Open a new query
-- Select your table
-- Double click on the name of the column you want to delete all the data
from
-- Select Query: Update from the menu
-- Type NULL into the Update "cell" under the column
-- Select Query: Run from the menu

Alternative is to use the Find and replace directly on the table
-- Put the cursor in the field you want to delete
-- Select Edit: Replace from the menu
-- Enter * in the Find field
-- Leave the Replace field blank
-- Use the buttons to find and replace.

As stated earlier make a copy of your table (or the entire database) before
you do this. That way you can get back to where you were if something goes
wrong.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

What looks like an excell column in your table is (in access) a field.
So if you have
ID
1stName
Surnname
HairColour
DateOfBirth

In your table the DateOfBirth will be shown in a "column"

To delete this information you could use a delete query. They are quite
simple BUT DO A BACKUP FIRST.

Open a new query bring in the table you want to work on.
Double click the field. In the criteria row insert the criteria - so if
you
want to delete all Johns. Put "John" in the criteria row.
If you want to delete everyone older than me (a very young 46) double
click
the date of birth field and in the criteria row put
DateDiff("yyyy",[CDDateofBirth],Date())-IIf(Format([CDDateofBirth],"mmdd")>Format(Date(),"mmdd"),1,0)>46
Or if you want to delete people with red hair and 1st name Kelly put both
of
these fields in the query and then add details to both criteria.
There are a limitless amount of criteria you can use.

In the query type drop down select delete query then click the big red !
at
the top of the screen - AFTER YOU HAVE DONE A BACKUP (just in case)

Good luck
--
Wayne
Manchester, England.

Hi there,
Major 1st timer here. Dappling my feet a bit. I have been asked to clean
up
some tables for the upcoming year. and i have a very basic question.
How do you delete data in a column without deleting the setting for that
column in the table. In excel i can easily select the 200 entries and hit
delete. I can't seem to just select the data in one column without
getting
all the other data in column a:g

So basic i know. Thank you.

 

[Wayne-I-M]

ooops

- You know when some people say that some of the answer are complete rubish

that was one

Sorry

Wasn;t thinking


--
Wayne
Manchester, England.

WHOA! STOP! CEASE! DESIST!

That method will delete rows of data. You DO NOT want to use a delete query
to clear a column. You want to use an UPDATE query and set the column's
(field's) value to null.

Open a new query
-- Select your table
-- Double click on the name of the column you want to delete all the data
from
-- Select Query: Update from the menu
-- Type NULL into the Update "cell" under the column
-- Select Query: Run from the menu

Alternative is to use the Find and replace directly on the table
-- Put the cursor in the field you want to delete
-- Select Edit: Replace from the menu
-- Enter * in the Find field
-- Leave the Replace field blank
-- Use the buttons to find and replace.

As stated earlier make a copy of your table (or the entire database) before
you do this. That way you can get back to where you were if something goes
wrong.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

What looks like an excell column in your table is (in access) a field.
So if you have
ID
1stName
Surnname
HairColour
DateOfBirth

In your table the DateOfBirth will be shown in a "column"

To delete this information you could use a delete query. They are quite
simple BUT DO A BACKUP FIRST.

Open a new query bring in the table you want to work on.
Double click the field. In the criteria row insert the criteria - so if
you
want to delete all Johns. Put "John" in the criteria row.
If you want to delete everyone older than me (a very young 46) double
click
the date of birth field and in the criteria row put
DateDiff("yyyy",[CDDateofBirth],Date())-IIf(Format([CDDateofBirth],"mmdd")>Format(Date(),"mmdd"),1,0)>46
Or if you want to delete people with red hair and 1st name Kelly put both
of
these fields in the query and then add details to both criteria.
There are a limitless amount of criteria you can use.

In the query type drop down select delete query then click the big red !
at
the top of the screen - AFTER YOU HAVE DONE A BACKUP (just in case)

Good luck
--
Wayne
Manchester, England.

Hi there,
Major 1st timer here. Dappling my feet a bit. I have been asked to clean
up
some tables for the upcoming year. and i have a very basic question.
How do you delete data in a column without deleting the setting for that
column in the table. In excel i can easily select the 200 entries and hit
delete. I can't seem to just select the data in one column without
getting
all the other data in column a:g

So basic i know. Thank you.

 

[Jennifer]

Thank you Thank you! I will give it a try.

So what i am getting from both emails is that you don't try and delete data
in the actual tables like i normally would in excel. Why is that? Queries i
thought were for etracting data out based on your perimeters and that
information was filled by the table. So if we are coming into the new year
and the user only wants data in this file for 2008 i thought i went directly
to the table. Hmmm Access is a bit differant than excel. If you have a moment
that is. Otherwise i'm sure i will stumble upon that answer soon enough.
Cheers!
--
Thank you,

Jennifer

WHOA! STOP! CEASE! DESIST!

That method will delete rows of data. You DO NOT want to use a delete query
to clear a column. You want to use an UPDATE query and set the column's
(field's) value to null.

Open a new query
-- Select your table
-- Double click on the name of the column you want to delete all the data
from
-- Select Query: Update from the menu
-- Type NULL into the Update "cell" under the column
-- Select Query: Run from the menu

Alternative is to use the Find and replace directly on the table
-- Put the cursor in the field you want to delete
-- Select Edit: Replace from the menu
-- Enter * in the Find field
-- Leave the Replace field blank
-- Use the buttons to find and replace.

As stated earlier make a copy of your table (or the entire database) before
you do this. That way you can get back to where you were if something goes
wrong.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

What looks like an excell column in your table is (in access) a field.
So if you have
ID
1stName
Surnname
HairColour
DateOfBirth

In your table the DateOfBirth will be shown in a "column"

To delete this information you could use a delete query. They are quite
simple BUT DO A BACKUP FIRST.

Open a new query bring in the table you want to work on.
Double click the field. In the criteria row insert the criteria - so if
you
want to delete all Johns. Put "John" in the criteria row.
If you want to delete everyone older than me (a very young 46) double
click
the date of birth field and in the criteria row put
DateDiff("yyyy",[CDDateofBirth],Date())-IIf(Format([CDDateofBirth],"mmdd")>Format(Date(),"mmdd"),1,0)>46
Or if you want to delete people with red hair and 1st name Kelly put both
of
these fields in the query and then add details to both criteria.
There are a limitless amount of criteria you can use.

In the query type drop down select delete query then click the big red !
at
the top of the screen - AFTER YOU HAVE DONE A BACKUP (just in case)

Good luck
--
Wayne
Manchester, England.

Hi there,
Major 1st timer here. Dappling my feet a bit. I have been asked to clean
up
some tables for the upcoming year. and i have a very basic question.
How do you delete data in a column without deleting the setting for that
column in the table. In excel i can easily select the 200 entries and hit
delete. I can't seem to just select the data in one column without
getting
all the other data in column a:g

So basic i know. Thank you.

 

[John Spencer]

Access is a lot different than Excel. They are both good tools and do have
some overlap in abilities, but each has areas that the other is not good at
doing.

Queries can change data as well as display data. You can use (for instance)
-- a delete query to delete records
-- an update query to change the contents of fields for all or specified
records
-- an insert query to add new records

Select queries allow you to select specific fields and records from a table
or tables to display either in the query datasheet view or better on a form
or printed out in a report.

With a query you can get records from table A and the associated records
from table B to display all in one "record".

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank you Thank you! I will give it a try.

So what i am getting from both emails is that you don't try and delete
data
in the actual tables like i normally would in excel. Why is that? Queries
i
thought were for etracting data out based on your perimeters and that
information was filled by the table. So if we are coming into the new year
and the user only wants data in this file for 2008 i thought i went
directly
to the table. Hmmm Access is a bit differant than excel. If you have a
moment
that is. Otherwise i'm sure i will stumble upon that answer soon enough.
Cheers!
--
Thank you,

Jennifer

WHOA! STOP! CEASE! DESIST!

That method will delete rows of data. You DO NOT want to use a delete
query
to clear a column. You want to use an UPDATE query and set the column's
(field's) value to null.

Open a new query
-- Select your table
-- Double click on the name of the column you want to delete all the data
from
-- Select Query: Update from the menu
-- Type NULL into the Update "cell" under the column
-- Select Query: Run from the menu

Alternative is to use the Find and replace directly on the table
-- Put the cursor in the field you want to delete
-- Select Edit: Replace from the menu
-- Enter * in the Find field
-- Leave the Replace field blank
-- Use the buttons to find and replace.

As stated earlier make a copy of your table (or the entire database)
before
you do this. That way you can get back to where you were if something
goes
wrong.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

What looks like an excell column in your table is (in access) a field.
So if you have
ID
1stName
Surnname
HairColour
DateOfBirth

In your table the DateOfBirth will be shown in a "column"

To delete this information you could use a delete query. They are
quite
simple BUT DO A BACKUP FIRST.

Open a new query bring in the table you want to work on.
Double click the field. In the criteria row insert the criteria - so
if
you
want to delete all Johns. Put "John" in the criteria row.
If you want to delete everyone older than me (a very young 46) double
click
the date of birth field and in the criteria row put
DateDiff("yyyy",[CDDateofBirth],Date())-IIf(Format([CDDateofBirth],"mmdd")>Format(Date(),"mmdd"),1,0)>46
Or if you want to delete people with red hair and 1st name Kelly put
both
of
these fields in the query and then add details to both criteria.
There are a limitless amount of criteria you can use.

In the query type drop down select delete query then click the big red
!
at
the top of the screen - AFTER YOU HAVE DONE A BACKUP (just in case)

Good luck
--
Wayne
Manchester, England.



:

Hi there,
Major 1st timer here. Dappling my feet a bit. I have been asked to
clean
up
some tables for the upcoming year. and i have a very basic question.
How do you delete data in a column without deleting the setting for
that
column in the table. In excel i can easily select the 200 entries and
hit
delete. I can't seem to just select the data in one column without
getting
all the other data in column a:g

So basic i know. Thank you.

 

[Jennifer]

Very clear . . . thank you for your time.
--
Thank you,

Jennifer

Access is a lot different than Excel. They are both good tools and do have
some overlap in abilities, but each has areas that the other is not good at
doing.

Queries can change data as well as display data. You can use (for instance)
-- a delete query to delete records
-- an update query to change the contents of fields for all or specified
records
-- an insert query to add new records

Select queries allow you to select specific fields and records from a table
or tables to display either in the query datasheet view or better on a form
or printed out in a report.

With a query you can get records from table A and the associated records
from table B to display all in one "record".

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank you Thank you! I will give it a try.

So what i am getting from both emails is that you don't try and delete
data
in the actual tables like i normally would in excel. Why is that? Queries
i
thought were for etracting data out based on your perimeters and that
information was filled by the table. So if we are coming into the new year
and the user only wants data in this file for 2008 i thought i went
directly
to the table. Hmmm Access is a bit differant than excel. If you have a
moment
that is. Otherwise i'm sure i will stumble upon that answer soon enough.
Cheers!
--
Thank you,

Jennifer

WHOA! STOP! CEASE! DESIST!

That method will delete rows of data. You DO NOT want to use a delete
query
to clear a column. You want to use an UPDATE query and set the column's
(field's) value to null.

Open a new query
-- Select your table
-- Double click on the name of the column you want to delete all the data
from
-- Select Query: Update from the menu
-- Type NULL into the Update "cell" under the column
-- Select Query: Run from the menu

Alternative is to use the Find and replace directly on the table
-- Put the cursor in the field you want to delete
-- Select Edit: Replace from the menu
-- Enter * in the Find field
-- Leave the Replace field blank
-- Use the buttons to find and replace.

As stated earlier make a copy of your table (or the entire database)
before
you do this. That way you can get back to where you were if something
goes
wrong.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

What looks like an excell column in your table is (in access) a field.
So if you have
ID
1stName
Surnname
HairColour
DateOfBirth

In your table the DateOfBirth will be shown in a "column"

To delete this information you could use a delete query. They are
quite
simple BUT DO A BACKUP FIRST.

Open a new query bring in the table you want to work on.
Double click the field. In the criteria row insert the criteria - so
if
you
want to delete all Johns. Put "John" in the criteria row.
If you want to delete everyone older than me (a very young 46) double
click
the date of birth field and in the criteria row put
DateDiff("yyyy",[CDDateofBirth],Date())-IIf(Format([CDDateofBirth],"mmdd")>Format(Date(),"mmdd"),1,0)>46
Or if you want to delete people with red hair and 1st name Kelly put
both
of
these fields in the query and then add details to both criteria.
There are a limitless amount of criteria you can use.

In the query type drop down select delete query then click the big red
!
at
the top of the screen - AFTER YOU HAVE DONE A BACKUP (just in case)

Good luck
--
Wayne
Manchester, England.



:

Hi there,
Major 1st timer here. Dappling my feet a bit. I have been asked to
clean
up
some tables for the upcoming year. and i have a very basic question.
How do you delete data in a column without deleting the setting for
that
column in the table. In excel i can easily select the 200 entries and
hit
delete. I can't seem to just select the data in one column without
getting
all the other data in column a:g

So basic i know. Thank you.