repco
asked on
Append Multiple Tables with varying names into one
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.
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.
err the last select should be from table4
ASKER
there can be 2 - 30 tables out there.
the number of tables changes?
ASKER
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.
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.
* 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.
* 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.
ASKER
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!!
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!!
you can do this using VBA codes. Is this an option?
ASKER
sure i know vba
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
ah wait n/m the qty on hand had an extra space on the import tables.
ASKER
Thank you!!
ASKER
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
"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
sql="INSERT INTO IMPORT_Master ( [date], [part number], [Pallet])"
sql=sql &" SELECT [" & tablename & "].[date], [" & tablename & "].[Part Number], ["& tablename & "] as Pallet FROM [" & tablename & "]"
ASKER
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
upload a copy of your db with the tables
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
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
ASKER
Form1 is where the code is.
Program.mdb
Program.mdb
where is the IMPORT_Master table?
ASKER
my apologies.
Program.mdb
Program.mdb
ASKER
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
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
you will see it from the message box when the error is generated during the import.
ProgramRev.mdb
ASKER
thank you again!
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.