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

"Ray K" <raykosXXX@optonline.net> wrote in message
news:48b21e77$0$20902$607ed4bc@cv.net...
> 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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Once you have your filtered table the way you like it, right click in the top
left corner square (just before the first column name). It highlights all
the records in the dataset. Left click on copy on the pop up menu.

Create a new table in datasheet view. Right click on the top left corner
square and left click on paste. All the filtered records are in the new
table, but with column names like Field1, Field2, etc.

Chris
Microsoft MVP


Ray K wrote:
>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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

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

O'C via AccessMonster.com wrote:
> Once you have your filtered table the way you like it, right click in the top
> left corner square (just before the first column name). It highlights all
> the records in the dataset. Left click on copy on the pop up menu.
>
> Create a new table in datasheet view. Right click on the top left corner
> square and left click on paste. All the filtered records are in the new
> table, but with column names like Field1, Field2, etc.
>
> Chris
> Microsoft MVP
>
>
> Ray K wrote:
>
>> 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.
>>
>
>

Arvin Meyer [MVP] wrote:
> "Ray K" <raykosXXX@optonline.net> wrote in message
> news:48b21e77$0$20902$607ed4bc@cv.net...
>
>> 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.
>
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

If your dataset is too big to fit in the default datasheet after you right
click to copy the filtered records, open notepad and paste the data there.
Save the file, then import it using the import text wizard. Use tab
delimited and keep the same primary key as the original table, don't let the
wizard add another ID column. It'll import all columns and rows in the text
file so you won't lose any.

Chris
Microsoft MVP


Ray K wrote:
>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.

--
Message posted via http://www.accessmonster.com

I forgot to add that you need to check the check box for first row contains
column names when using the wizard.

Chris
Microsoft MVP


Chris O'C wrote:
>If your dataset is too big to fit in the default datasheet after you right
>click to copy the filtered records, open notepad and paste the data there.
>Save the file, then import it using the import text wizard. Use tab
>delimited and keep the same primary key as the original table, don't let the
>wizard add another ID column. It'll import all columns and rows in the text
>file so you won't lose any.
>
>Chris
>Microsoft MVP
>
>>Chris,
>>
>[quoted text clipped - 5 lines]
>>insert a record, the number of records increases to 22, but as soon as I
>>click on any record, the number reverts to 21.

--
Message posted via http://www.accessmonster.com

You must save the filter as a query.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Ray K" <raykosXXX@optonline.net> wrote in message
news:48b2b720$0$29501$607ed4bc@cv.net...
> Arvin Meyer [MVP] wrote:
>> "Ray K" <raykosXXX@optonline.net> wrote in message
>> news:48b21e77$0$20902$607ed4bc@cv.net...
>>
>>> 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.
>>
> 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] wrote:
> 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

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

Ray K wrote:
> Arvin Meyer [MVP] wrote:
>> 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
>
>