Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Append Multiple Tables with varying names into one

Posted on 2009-06-30
24
Medium Priority
?
441 Views
Last Modified: 2012-05-07
i have many tables that i've imported from an excel sheet wih tabs. I already did the code so that each tab has its own table name. i want to be able to consolidate all those tables into one.

i have auto named the tables BC1, BC2, BC3, etc. There can be 2 tables or there can be 20 tables imported.

question is how can i append all those tabels into one master? Each table does have column headings but one of them might contain an extra column.

Please let me know if you need more information.
0
Comment
Question by:repco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 8
  • 3
24 Comments
 
LVL 2

Expert Comment

by:Jambyte
ID: 24746624
INSERT INTO Table3
SELECT Field1, Field2 from Table1
UNION ALL
SELECT Field1, Field2 from Table2
UNION ALL
Select Field1, '' as Field2 from Table3


the last select doesn't have a second column but needs a blank one for the union to work... hope that makes sense.
0
 
LVL 2

Expert Comment

by:Jambyte
ID: 24746628
err the last select should be from table4
0
 

Author Comment

by:repco
ID: 24746648
there can be 2 - 30 tables out there.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 2

Expert Comment

by:Jambyte
ID: 24746675
the number of tables changes?
0
 

Author Comment

by:repco
ID: 24746701
yes, plus the code you gave me does not work anyway.
there can be 2 to 30 tables. BC1, BC2, BC3, BC4, etc. i want those consolidated into the IMPORT_MASTER table.

Table BC2, might have an extra column than the BC1, or BC3 table.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24747084
* number of table changes
* Number of column is not constant

Are the names of columns always the same (except for the Extra columns)?
Are the names of Extra columns consistent? does not change?

what is the structure of the IMPORT_MASTER table? post the name of fields and Data type of each field.
0
 

Author Comment

by:repco
ID: 24747149
thank you for your response Capricorn1.

1. The names of the columns are all the same on all BC1, BC2 tables. one of the tables just contains one extra column. I have created a "IMPORT_Master" table which has all columns, including the extra.
2. The names of the extra column is always the same.

i.e.
Table BC1
Part | Date | Code | Comment

Table BC2
Part | Date | Comment

Table IMPORT_Master
Part | Date | Code | Comment

i want to be able to append tables BC1 > BC(number of tables existing under "BC) it can be 3 to 20 tables. BC1, BC2...BC20. into the IMPORT_Master table.

thanks so much!!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24747210
you can do this using VBA codes. Is this an option?
0
 

Author Comment

by:repco
ID: 24747563
sure i know vba
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 24747698
place this codes in a module.


sub mergeTables()
dim td as Dao.tabledef, db as dao.database
dim tableName as string, sql as string
set db=currentdb()
'to clear the IMPORT_Master table before importing new records

db.execute "delete * from IMPORT_Master"

for each td in db.Tabledefs
    if left(td.name,2)="BC" then
    tableName=td.name
    sql="Insert into [IMPORT_Master]"
    sql=sql & " select * from ["& tablename &"]"
    db.execute sql
    end if
next

db.close
end sub
0
 

Author Comment

by:repco
ID: 24747856
im gettin error message
THE INSERT INTO statement contains the following uknown field anme 'Qty On Hand '. Make sure you typed the name correctly and try the operation again.

Sorry i should of also included that as one of the fileld names above.
i do have Qty on Hand field on the IMPORT_Master table set.
0
 

Author Comment

by:repco
ID: 24747888
ah wait n/m the qty on hand had an extra space on the import tables.
0
 

Author Closing Comment

by:repco
ID: 31598394
Thank you!!
0
 

Author Comment

by:repco
ID: 24749056
capricorn1 if i didn't want to use SELECT * but instead each filed name how would that work?  i have

"INSERT INTO IMPORT_Master ( [date], [part number], [Pallet)"
SELECT [" & tablename & "].date,  [" & tablename & "].[Part Number], ["& tablename & "]"
FROM [" & tablename & "]"

reason why is because i need to add in the tablename to the "Pallet Field"
i keep getting Too few parameters
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24749297


sql="INSERT INTO IMPORT_Master ( [date], [part number], [Pallet])"
sql=sql &" SELECT [" & tablename & "].[date],  [" & tablename & "].[Part Number], ["& tablename & "] as Pallet FROM [" & tablename & "]"
0
 

Author Comment

by:repco
ID: 24749333
i copied and pasted your formula but i still keep getting too few parameters. Expected 1. and it highlights db.execute sql on the debugger
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24749732
upload a copy of your db with the tables
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24749756
use this


sub mergeTables()
dim td as Dao.tabledef, db as dao.database
dim tableName as string, sql as string
set db=currentdb()
'to clear the IMPORT_Master table before importing new records

db.execute "delete * from IMPORT_Master"

for each td in db.Tabledefs
    if left(td.name,2)="BC" then
    tableName=td.name
    sql="Insert into [IMPORT_Master]"
    sql=sql & " select * from ["& tablename &"]"
    db.execute sql

     db.execute "update [IMPORT_Master] set [pallet]='"& tablename &"' where pallet is null"

    end if
next

db.close
end sub
0
 

Author Comment

by:repco
ID: 24749799
Form1 is where the code is.
Program.mdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24749832
where is the IMPORT_Master table?
0
 

Author Comment

by:repco
ID: 24749876
my apologies.
Program.mdb
0
 

Author Comment

by:repco
ID: 24749888
im basically trying to import everything from the S PA tables into the IMPORT_Master. i know above we used only a few fields but that was for testing. i could of screwed up there. sorry if i did
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24750086
some data from the one of the tables is not of the same data type..

you will see it from the message box when the error is generated during the import.
ProgramRev.mdb
0
 

Author Comment

by:repco
ID: 24754425
thank you again!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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