update cells from table 2 for all blanks in table 1

[scorpy]

Hi guys,

I am in a complicated situation where i was given 50 tables with 45 column in each table and told to merge them into a single table. Each table has over 10,000 records and merge has to be done based on the field S#NO. I have attached a sample with two tables in which the non blank fields from both table should be merged into a new table.

Is there any way to accomplish the merge?

[June7]

What do you mean by 'merge'? Do you mean you want to UPDATE the data from 49 tables into Table1? Do you want to normalize data structure?

Table1 has data in records 1 thru 4 and Table2 has data in records 6 thru 8. Can more than one table have data in the same S#NO?

Show example of the result you want to achieve.

A UNION query might get you what you want.


Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention.

[scorpy]

By means of merge i meant i have to update the data from all tables to be made into a single table.(only data should be merged and "blank" should not replace the "cell which previously had data")

Data in these tables will be just a Yes, so there is no problem in overwriting the data. More than one table may have data for same S#No.

I never used Union Query before and i have attached the desired output table with this post.

Thanks for your time.

[June7]

So if table 5 and table 17 both have data for the same S#No, the data is the same in both records for all fields? If one has Y in Commercial, they both do? Oh well, I think not important.

Example UNION query:

SELECT * FROM table1
UNION SELECT * FROM table2
UNION SELECT * FROM table3
....;

Up to 50 SELECT lines allowed. There is no wizard or builder for UNION, must type into SQL View window.

UNION will not allow duplicate records (UNION ALL will). http://www.w3schools.com/sql/sql_union.asp

The UNION query can be used as data source for another query to refine the data. Next query would be an aggregate:

SELECT [S#No], Max(Commercial) AS MaxCom, {do Max on each field} FROM UnionQuery GROUP BY [S#No];

Now this dataset should be the merged set you want. Run a MAKE TABLE sql action to create native table.


How often will this merge need to take place? Is this a one-time event?


Alternatively, run 50 UPDATE sql actions.