Access 2000 extracting data from tables

[Ernie]

I know this one because I've been dealing with the same
thing for the past month (taking data from several very
large tables and putting it into many more smaller tables).

Two ways:
1) create a new blank database and give it a name.
2) in your original database create as many queries as you
want to create new tables. In these queries, select just
the columns that you want to export, make sure that you
maintain the same key structure as the original table
(this is also a good time to re-define your keys if you're
feeling adventurous).
3) set the column properties and formats as needed for the
data that you are going to be putting in your new tables.
4) change the query to a "Make table query" and give your
new table a name THEN click the option for "another
database" and type in the path and db name that you
created in #1 above. Click ok. (note that a make table
query will default all your fields to "Text" with no
formatting so you need to set that first (see#3).
4) click run
5) verify that your table was created correctly.

The second method is identical to the first except that
you create your queries in the new db and use File / Get
External Data / Link Tables with your old db.

Note also that running a make table query will erase a
table with the same name so you can only do this once on
each table that you want to keep. Change to append queries
if all you want to do is add data to the new tables.

If you need to filter any of your data, such as not
importing "null" values or selecting only certain values
from the columns you are using, make doubly sure that you
get all the rows that you need in your new table. Updating
later is a pain. (It took me two days to figure out why my
new table was missing 170,000+ rows and another day to
figure out how to get them in there.)


If I'm wrong, may an MVP whip me with a wet noodle.


HTH :>

-----Original Message-----
1) I'm re-designing a database and adding more tables to

decrease the number of fields on an original table. How
can I extract the data from tableA and insert it into
tableB?

2) If I were to start from scratch how can I extract data

from another DB into the new DB?

 

[Ernie again]

If you want to leave the new tables in the original db,
then in your make table query click on "current database"
in the box where you set the name of your table.

 

[Ernie]

When you change a select query to a make-table query, you
get a box in which you have to type the name of your new
table. In this same box are two option buttons, the first
says "current database", the second says "another
database". When you click on the second option box, the
line for the path and name of your new db becomes un-
greyed and you can type in or browse to the db where you
want to put your new table.

After clicking on "OK" to save the new setting, click
on "Run" and your new table will be populated with the
records according to the criteria in your query.

Lookup "Action Queries" in F1-Help for some additional
info.

-----Original Message-----
Ernie,
what do you mean by "option for another database"? I

believe it was in our first reply. Also, will this
automatically extract the data from those fields? Thanks,
you've been helpful.

 

[Ernie]

" >Thanks for all your help...I'm new to Access "

Me too

Make sure that your form is bound (source control) to the
correct table or query from that table. #Name usually
means that the field from which you are trying to extract
data does not exist in the table / query in which your
form is looking.

If this doesn't help, repost your problem in the "Forms"
or "Forms programming" forum(s). Someone there should be
able to help.

-----Original Message-----
Okay...I have my new tables populated. Everything looks

good! My latest problem is when creating the new forms,
I'm getting the #Name? error in the text boxes. I've
tried several different things to prevent things but no
matter what I do, I cannot get the txtboxes to populate
with the fields in the new tables. Any suggestions?