We need to add all records from 1 table to the end of another table.

I have two tables in a MS Access DB. Table A which is the main table has 15 fields.  The second Table B currently has 10 fields. The first 10 fields are identical in size, order, heading, form, etc. in both tables.  To end with only one table, we would like Table B to be appended to the bottome of Table A.  (Duplicates are OK). When completed, the information +for the last 5 fields will be added manually. The autonumber field in Table A we want to keep.  When the records from Table B are added to Table A, we want the sutonumber to assign numbers to them as if they were manually entered.  

I think that the command to do this is:
                                     Insert into table A select * from Table B
Is this correct?  If so, where would I place this command and start it?  I am using Access Version 7 in Windows.  Also, what should I do with the autonumber field in Table B?  Should both tables be open when this process is done?   Note: The autonumbers in Table A are already referenced on reports, queries, etc. and we do not want the original numbers in Table A to change, if possible.

My biggest problem is finding where to place the processing code and how to run it.

Any assistance you can give will be greately appreciated.
 
sherman6789Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lluddenCommented:
INSERT INTO Table1 (Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9)
SELECT Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9 FROM Table2

Exclude the autonumber field from the list, but include all of the others.
0
peter57rCommented:
You just create a query using the query designer.
Start by creating a select query from table2 and then change the Query Type to Append and it will ask you for the name of the target table.
0
sherman6789Author Commented:
Thank you lludden,

That was a very fast response.  Where do I put this command and how to I make it begin?
Also I have another Access file with the same problem.  However, it has approximately 65 fields in Table A.  Do I need to list each field separately as in your example or can I use the "*" to indicate that all fields will be used?  Remember that all fields for the two tables are in the same order, type and size.  Table A does have additional fields that are not in table B.  Do I need to add those field name to the end of Table B to make sure both have the exact number of fields?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sherman6789Author Commented:
Thank you.  You were responding while I was typing the new question.  I will try it now.  Thanks.
0
sherman6789Author Commented:
Do I click "Create" than select: Query Wizard, Query Design or Macros?
0
peter57rCommented:
Query Design
0
sherman6789Author Commented:
Thanks for your help.  I think that I see one of my problems.  I do no see a button called "Query Designer".  This may be hidden.  I will play around the ribbon area and see if I can find how to make the Query Designer button appear.
0
lluddenCommented:
In Access 2010, choose Create on menu, then Query Design, then right click on the query and choose SQL View
0
sherman6789Author Commented:
Do you know how to find the commands in Microsoft Access Version 7?  We don't have version 2010.
0
lluddenCommented:
Access 7 aka Access for Windows 95 is pretty old.  I have an access 2000, and it is
Window->Unhide
Objects->Queries
New->Design View
View->SQL View
0
peter57rCommented:
DO you mean Access 7 or Access 2007?
You refer to the ribbon so I assumed A2007/2010.

On A2007 the query design button is as in your face as it gets- I can't see how you can miss it.
Capture2.JPG
0
sherman6789Author Commented:
Thanks  peter57r.  I am sorry Budden, I mislead you by accident.  We are using Miscrosoft Access version 2007.  The helpdesk here frequently refers to t as Version7.

When I clicked the "Query Design" button, a "Show Table" box appeared.  When I selected the table and clicked "add" another box appeared showing all of the fields in the table.  I didn't know where to go from there and figured that I should be looking for a botton called "Querty Designer".  I expected a box which would allow me to enter the commands that Budden gave me.  I do see a button called "Builder".  MAybe I am suppose to use the "Expression Builder".  

Thanks for your assistance.
 
0
peter57rCommented:
It's not really the role of EE to teach you the basics of how to use Access.

Look at one of the million tutorials on the net for that.

example..
http://www.youtube.com/watch?v=bydTZEg23K4

0
MzLiberty22Commented:
Great you are halfway there. What you are looking at is the query designer.

1. Make sure you have a backup of your database.
2. Now, from where you saw all of the fields in the table, select the ones you want to append and drag them to the grid at the bottom (or double-click and they will appear on the grid). If you want the whole table, drag the * down instead, but for this I would select each field.
3. Righ-click in the top section of the query designer and select Query type > append query.
4. Select the table to append to.
5. In the grid, at the bottom, make sure each field is going to the right place.
6. Save your query and run.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sherman6789Author Commented:
Thanks for all of your help.  It worked very well and I can use the information on other databases.  

I have been working with Experts-Exchange for several years on verious program types.  I do not expect EE to spend time training me basic Database, Spreadsheets, etc. I did get the information that I needed and it is working very well.  Thank you.

I am increasing the points to the max of 500 because I need to give credit to several experts who have helped me greatly on this project.
0
sherman6789Author Commented:
I also clicked the RUN button to make the process append to the table.  The instructions were great and very helpful.  Thank you.  -WRS
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.