# access 2003 import data from one table to another



## chapmoreno (Apr 6, 2007)

Hi!

I have a database with 10 different tables for 10 different classes. I want to create one table that has the information for all 10 classes. The fields are the same (first, last name, etc.) Do I use query? Import data? Simple question, but not so simple answer for me..

I'm a newbie...please use small words and speak slowly..


----------



## gurutech (Apr 23, 2004)

Yes, I would use a query to accomplish this. Use the wizard, and it will allow you to choose what fields from each of the existing tables you want to import.


----------



## chapmoreno (Apr 6, 2007)

Guru,

Thanks, but it is still beyond me. I want all of the names in the name fields of the 10 tables to be gathered in the name field of one table..as I play with it, i come up with 10 different fields in one query...

I'll keep on playing with it, but if you know of any samples for what I'm trying to do, or any tutorials, pls send them my way..

Thanks again for the assist...

Mike


----------



## slurpee55 (Oct 20, 2004)

Or you copy everything to a new table - copy one of your existing tables, paste it as structure only and name is something like tbl_alldata. Then make an append query and select one of the 10 tables and when it requests what table to append to, choose the new all data table. Repeat this until you have appended the data from all 10 tables. This way you will not mess up any of your original tables. (I'm rather anal about losing data).
Oh, and welcome to the forum!


----------



## chapmoreno (Apr 6, 2007)

Slurpee...

thanks for the welcome...but pls remember...small words and slowly..

make a new table -> I can do that...new datasheet or design view?

copy a table -> I can do that...under the database window (where you have the selections of tables, queries, etc.) right click, copy, and then...

paste into the new as structure only...um hmm..duh...please elaborate to a guy who makes Homer look like a genius...

append query...um hmmm..please elaborate more...

remember, if I get this done any year soon, I will mail you one extremely tasty doughnut..

 

mike


----------



## slurpee55 (Oct 20, 2004)

LOL!
Well, in the objects - tables, you have your 10 tables. highlight and copy one of them. Click back into the objects - tables and right-click, then go to Paste and left-click. You will get a Paste Table as pop-up. In the blank area, type in the name you want (I'll call it alldata). In the paste options, select Structure Only. You now have a new blank table (alldata) (open it up - it will be like one of the 10, but with no data in it).
Now click on the Options-Queries. Click on "Create Query in Design View." You will get a pop-up showing your tables, queries, etc. in different tabs. Highlight one of the tables ("Table 1" I will call it) and click the Add button. Click the Close button. 
You will see Table 1 listed at the top and all the fields listed (first, last name, etc.). At the very top is an asterisk (*), which is a wildcard that stands for all fields. Double-click on the * and you will see in the row below something like Table1.*.
At the top of the page, to the right of File, Edit, etc. is Query. Click on that and go down to Append Query. Left-click on that. You will then get a pop-up called Append with a filed saying Append To - click on the down arrow and select your new table (alldata or whatever you called it). Click OK. Now your column that says Table1.* says Append To alldata.*.
Finally, in the command bar at the top - icons for View, Save, etc. you will see an icon that is a red ! (the run command icon). Left-click on that once. You will get a pop-up message saying You are about to append (fill in the number) rows (followed by warnings about being unable to undo). Click Yes.
If you go back to the tables and look at your alldata table, you will see it has the data in it that you just appended.
Now you can do one of two things, depending on what you understand so far. You could close the query (don't bother to save) and repeat from the beginning, only with Table2 next and so on. Another, faster alternative, would be to, within the query, right-click on the table (in design view still) and left-click on Remove table. Going back to the top, next to the run (!) icon is an icon that looks like a spreadsheet/table with a + sign next to it. Click on that and the Show Table pop-up appears, listing all your tables (you saw it when you first created the query, remember?). Select the next table (Table2) and repeat the steps as above. After that, you just repeat until you have appended the data from all 10 tables into alldata.


----------



## slurpee55 (Oct 20, 2004)

Oh, and I love chocolate doughnuts!


----------



## OBP (Mar 8, 2005)

Mike, if you are really stuck post a Zipped copy of the database as an attachment on here.


----------



## chapmoreno (Apr 6, 2007)

Gentlemen, (and gentleladies if you happen to be...)

Thank you very much for your assistance!! I just got home and will try slurpee's suggestion. Slurpee, got your pm, and for some reason (perhaps because I am a newbie...) it won't let me respond to you...but thanks again for the offer..
OBP, thank you for the offer as well..

I will put my two good brain cells to work tomorrow and see if I can figger this thing out..
but you folks have certainly pointed me in the right direction...

Slurpee, if this works, I'm going to have to find a bigger envelope...this doughnut won't fit in a standard #10...doh!!

I will check back in with the gang tomorrow and let you know how it went...

Mike


----------



## chapmoreno (Apr 6, 2007)

Slurpee and the rest of the gang:

IT WORKED!! Woohoo!! (Little happy dance...) I have completed phase one of my plan for world domination..

Now, to learn how to make a switchboard that will:
allow users to print rosters.
allow users to search for particular individuals
allow users to access a database to create their own "to do list" for class preparation.

I may have bitten off more than I can chew, but I am persistent...

Slurpee...need help with ideas on how to get a doughnut into an envelope..
the frosting keeps falling off..

Mike


----------



## OBP (Mar 8, 2005)

Mike, I have attached a database that has a Main Menu Form (which works just like a switchboard) and the database has an advanced Search Form and Query.
You can "Import" the "Main Menu" straight in to your Database and then just modify the words on the Buttons and Visual Basic Code that opens the various forms etc. If you need any help understanding the Search Form's Control and how the Query works just ask.
I am not sure about your last requirement of "allow users to access a database to create their own "to do list" for class preparation." 
Is this another database or just another table in your current database?


----------



## chapmoreno (Apr 6, 2007)

Tony,

I ought to read ALL of my email before responding...

Again, thanks for the example..I'll have a look at play with it a bit..

Thank you again for your assistance. It is greatly appreciated, and assures me yet again of the kindness of people in these forums!

Mike


----------



## OBP (Mar 8, 2005)

Mike, you can "Link" to a Table (to create their own "to do list" for class preparation) in another Database.


----------

