Create a Master table from 3 other tables

[gmazza76]

Good afternoon,

I am reasonably new to Sql and I am trying to create a Query to update/create a new table "Overall Data" from 2 current Tables.

I am using the code below which works but I then need it to create a table if possible called "All Data" so I can then link it to PowerPivot
Currently in its Query state I cannot do this, so what is the best way to do this?

Select *
from Table1
Union all
select *
from Table2

thanks in advance
Gavin

 

[JonXL]

Thanks @alansidman.
do you or @xenou know how I would get round the issue of where my source file as the week goes on can add extra columns on.
As you can see from the code below Week_44 (as the week has completed) has 28 Fields and Week_45 (linked as a source file) only has 23 I seem to be having an issue as the "All_Data" is expecting a full 28.
Plus I am getting a syntax error at the "Union" point
Is there a way round this or will I have to adjust the SQL day by dady?

INSERT INTO All_data ( DateTag, SessionIDTag, DNISTag, CLITag, LOBusTag, SegTag, ICMTag, CountTag, PrompSTag, AppTag, CardTypeHintTag, AccountTypeHintTag, WildcardHintTag, ReturnCodeTag, CETag, Tag16, Tag17, Tag18, Tag19, Tag20, Tag21, Tag22, Tag23, tag24, tag25, tag26, tag27, tag28 )
SELECT Week_44.Field1, Week_44.Field2, Week_44.Field3, Week_44.Field4, Week_44.Field5, Week_44.Field6, Week_44.Field7, Week_44.Field8, Week_44.Field9, Week_44.Field10, Week_44.Field11, Week_44.Field12, Week_44.Field13, Week_44.Field14, Week_44.Field15, Week_44.Field16, Week_44.Field17, Week_44.Field18,
Week_44.Field19, Week_44.Field20, Week_44.Field21, Week_44.Field22, Week_44.Field23, Week_44.Field24, Week_44.Field25, Week_44.Field26, Week_44.Field27, Week_44.Field28
from Week_44
union all
SELECT Week_45.Field1, Week_45.Field2, Week_45.Field3, Week_45.Field4, Week_45.Field5, Week_45.Field6, Week_45.Field7, Week_45.Field8, Week_45.Field9, Week_45.Field10, Week_45.Field11, Week_45.Field12, Week_45.Field13, Week_45.Field14, Week_45.Field15, Week_45.Field16, Week_45.Field17, Week_45.Field18,
Week_45.Field19, Week_45.Field20, Week_45.Field21, Week_45.Field22, Week_45.Field23
FROM Week_45;

thanks
Gavin

I recalled something similar I assisted with not long ago. Here we built a query from unknown number of fields with Fields.Count and inserted NULL in the query for fields that didn't exist.

cmdExport_Click

I have the following code for exporting data from the dBase to excel.. It works and works great. I am wondering though if there is a way to add a line into the code that when exported, IF A2 is NULL insert "NO ACTIVITY FOR REVIEW FOUND" Private Sub cmdExport_Click() Dim arrCurrentFormInfo...

www.mrexcel.com