part

R

rob p

Hi. I have a table with 180,000 entries. I can filter on field so I only
see 1,500 of them. Is there a way to save / cut / ?? to get the 1,500 into a
separate table I can work with?
(I can filter the big table via query for reports but I would like a little
table with just the 1,500)

thanks.
 
T

tina

write a SELECT query to pull the specific records you want. then turn the
SELECT query into a Make-Table action query, and run it.

(btw, this sort of thing is fine when you're doing "quick and dirty" data
analysis for yourself in Access; but in an end-user database, it usually
better to stick with querying the data in the "real" table, rather than
dumping data into smaller tables - too much potential for ending up with
obsolete and/or inaccurate data across multiple identical tables.)

hth
 
P

Pat Hartman\(MVP\)

There is no need to actually copy the data to a new table. Access allows
you to use queries pretty much wherever you can use tables. So use the
select query as the RecordSource of a form or report or export it if that is
what you need to do. You can even use queries inside queries.
 
M

mnature

It is always scary to see a phrase like "a separate table I can work with"
because that implies you are working directly with tables to modify your
data. You should never ever work with data directly through tables, unless
you are troubleshooting a problem. You can use a query to look at a subset
of a table, and even make it look similar to the table it is based on.
Moreover, use of a query will ensure that the information in the table is
current and correct. You could even use the same query over and over by
simply changing the filter on one or more fields.

Always let the database do as much of the work as possible.
 
R

rob p

Thanks for all the suggestions in this and the other two posts.

Actually, with the table, I've never worked with it - rather I worked with
queries that ran forms and reports. The total amount of disk space taken and
resources used to open this table are more the concern.

A much smaller table would be easier to work with and move around. I should
have been clearer to begin with. Anyway, all posts are getting me to that
direction.

rob
 
J

John Vinson

Actually, with the table, I've never worked with it - rather I worked with
queries that ran forms and reports. The total amount of disk space taken and
resources used to open this table are more the concern.

A much smaller table would be easier to work with and move around. I should
have been clearer to begin with. Anyway, all posts are getting me to that
direction.
Click to expand...

If your table is properly indexed and your queries are built
correctly, you'll find that there is plenty of "room to move around".

If you store the data redundantly - once in the "big" table (and do
note that 10,000,000 rows is a "big" table; 100,000 is quite modest)
and then store all the same data in a bunch of smaller tables, the
*database* will be that much more bloated and inefficient. You'll have
200,000 rows of data stored in multiple tables, with all the system
table overhead required for each additional table.

If you must do this, create a separate throwaway database for the
extracts, and don't even think about updating the data in the
extracted subtables; keeping them in synch with the master table will
be an utter nightmare.

John W. Vinson[MVP]
 
Top