?
Solved

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

Posted on 2011-10-31
16
Medium Priority
?
327 Views
Last Modified: 2012-05-12
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.
 
0
Comment
Question by:sherman6789
  • 8
  • 4
  • 3
  • +1
16 Comments
 
LVL 18

Expert Comment

by:lludden
ID: 37057135
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 600 total points
ID: 37057179
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
 

Author Comment

by:sherman6789
ID: 37057192
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sherman6789
ID: 37057204
Thank you.  You were responding while I was typing the new question.  I will try it now.  Thanks.
0
 

Author Comment

by:sherman6789
ID: 37057241
Do I click "Create" than select: Query Wizard, Query Design or Macros?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 37057254
Query Design
0
 

Author Comment

by:sherman6789
ID: 37057304
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
 
LVL 18

Assisted Solution

by:lludden
lludden earned 600 total points
ID: 37057336
In Access 2010, choose Create on menu, then Query Design, then right click on the query and choose SQL View
0
 

Author Comment

by:sherman6789
ID: 37057365
Do you know how to find the commands in Microsoft Access Version 7?  We don't have version 2010.
0
 
LVL 18

Expert Comment

by:lludden
ID: 37057414
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
 
LVL 77

Expert Comment

by:peter57r
ID: 37057488
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
 

Author Comment

by:sherman6789
ID: 37057682
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
 
LVL 77

Expert Comment

by:peter57r
ID: 37057851
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
 
LVL 1

Accepted Solution

by:
MzLiberty22 earned 800 total points
ID: 37064572
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
 

Author Comment

by:sherman6789
ID: 37064752
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
 

Author Closing Comment

by:sherman6789
ID: 37064827
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question