Saving filtered tables

[Ray K]

I'm trying to do what should be a simple thing, but has become very
frustrating: save a filtered table as a new table.

I start with a table of about 200 records (of names and addresses) and I
filter (by selection) roughly 2/3 of the records out of the table. I
want to save the filtered table so I can use it with the label wizard to
make mailing labels.

I've tried saving the filtered table using the Save As command; that
doesn't work (the filter is lost and the entire 200 records are
present). Same thing happens if I try saving the abridged table as a
query; the filter is again lost.

What am I missing?

BTW, I'm using Access 2002 with W2K.

Thanks,

Ray

 

[Arvin Meyer [MVP]]

I'm trying to do what should be a simple thing, but has become very
frustrating: save a filtered table as a new table.

I start with a table of about 200 records (of names and addresses) and I
filter (by selection) roughly 2/3 of the records out of the table. I want
to save the filtered table so I can use it with the label wizard to make
mailing labels.

I've tried saving the filtered table using the Save As command; that
doesn't work (the filter is lost and the entire 200 records are present).
Same thing happens if I try saving the abridged table as a query; the
filter is again lost.

What am I missing?

What you are missing is a query. You aren't supposed to be manipulating the
data in the table if you want to maintain data integrity. Instead do all
your filtering and sorting with a query. If you save the query, you should
not be losing anything.

 

[Ray K]

Chris,

My filtered table is 39 records (24 fields). I can copy them as you
said. When I create a new table by using "Create table by entering
data", the default opening table is 10 fields wide by 21 records. So if
I immediately paste, I lose the last 18 records and rightmost 14 fields.
I can insert extra columns (fields), but not records. If I hit Ctrl-+ to
insert a record, the number of records increases to 22, but as soon as I
click on any record, the number reverts to 21.

Ray

 

[Ray K]

What you are missing is a query. You aren't supposed to be manipulating the
data in the table if you want to maintain data integrity. Instead do all
your filtering and sorting with a query. If you save the query, you should
not be losing anything.

Arvin,

I used "Create query using wizard," selected the complete table (149
records) as the source, and selected all the fields for the new query.
Now I have a duplicate of my original table as a query. If I filter (by
selection) so just the desired 39 records remain, save, and then reopen
the query, all 149 records are still there.

The only way for me to make this work is to actually delete - not merely
filter out - the records that I don't want from the query and save it.
Then I can proceed with the label wizard.

Ray

 

[Arvin Meyer [MVP]]

You must save the filter as a query.

 

[Ray K]

You must save the filter as a query.

Thanks, I'm just learning about queries. But I'm stumped on what should
be a simple problem. I want to keep records that meet certain criteria
in two fields:

- Field1's cells can contain one of two possibilities: a blank (not a
space or zero) or a capital Y.
- Field2's cells can contain one of three possibilities: a blank (not a
space or zero), a capital Y, or capital SS.

I want to keep just those records that contain a Y in Field1 and a blank
(that is, not Y or not SS) in Field2.

I go to the design view of a new query, and in the Field1criteria I
enter y (or =Y or "Y"). Returning the the datasheet view, I see the
records with a Y in Field 1, and also those records that have a Y or SS
in Field2. The problem is I don't know how to enter the criteria in
Field2 to pick only the records with a blank. I've tried entering =""and
"". But that doesn't work; all the records disappear.

If I put no criteria in Field1, and put the Field2 criteria either as
<>"" I get just the records with Y or SS in Field2, as expected. Based
on that test, a pair of double-quotes seems to be the way of specifying
a blank. As mentioned above, if I try simply putting in Field2 criteria
as "" or =''", to retrieve just the records with blanks in Field2, all
the records disappear. So there is an inconsistency in the way Access
responds to a pair of double-quotes, depending on whether I want to
include or exclude records with blanks in Field2.

Thanks for the help.

Ray

 

[John Spencer]

Try using IS NULL as the criteria in the query under field 2. When you "SEE"
blank the data can be a zero-length string, a bunch of spaces (not normal in
Access but possible), or Null. Null is very roughly equivalent to nothing.

To filter for Null values you need to use one of two operators.
-- Is Null (Find only records where the field contains Null)
or
-- Is Not Null (Find records where the field contains any value)

So it sound as if you need criteria of IS NULL for Field 2.

Field: Field2
Table: YourTable
Criteria: Is Null

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County