Combine data from two tables

[foragerr]

Hi, I'm new to this forum, while I did a few quick searches, I apologize if this has been asked before.

I have some data in two tables, and I'm trying to generate a meaningful view of all the data together. To illustrate:

Table 1:

UserType Europe Americas Asia
Admin    10     5        5
PowerUsr 20     10       15
User     20     20       20

Table 2:

UserType Feature1 Feature2
Admin    Yes      Yes
PowerUsr Yes      No
User     No       Yes

The view I'd like to generate would look like this:

Admin         Feature1   Feature2
    Europe    10         10
    Americas  5          5
    Asia      5          5
PowerUsr
    Europe    20         0
    Americas  10         0
    Asia      15         0
User
    Europe    0          20
    Americas  0          20
    Asia      0          20
===================================
Totals        65         80

What would be the simplest way to achieve this?
I've tried using a SQL union on the two tables and then a pivot table on the generated data, but I'm having a hard time achieving the exact format I'm looking for.

I'm willing to write some VBA code, but as a first timer, that'd be significant investment for me. I'm checking to see if I'm not missing something simpler before jumping into that.

Would appreciate any help/pointers. Thanks!

 

[foragerr]

Woah, this forum is busy! quick bump.

Also: I'm using Excel 2007 on WinXP.

 

[Fazza]

The data is not normalised. Best to change that if you have to work with the data. There is an understanding that 'Yes' is wanted and 'No' not: this should really be in another table, I think.

FWIW, here is a solution using the data as posted.

SELECT T3.UserType, T3.Location, IIF(T2.Feature1='Yes',1,0)*T3.HowMany AS [Feature1], IIF(T2.Feature2='Yes',1,0)*T3.HowMany AS [Feature2]
FROM Table2 T2, 
(SELECT UserType, 'Europe' AS [Location], Europe AS [HowMany]
FROM Table1
UNION ALL
SELECT UserType, 'Americas' AS [Location], Americas AS [HowMany]
FROM Table1
UNION ALL
SELECT UserType, 'Asia' AS [Location], Asia AS [HowMany]
FROM Table1) T3
WHERE T2.UserType = T3.UserType

Such as start from ALT-D-P then the external data option, follow the wizard and load the SQL when you can. HTH. regards, Fazza

 

[foragerr]

Thanks for your reply Fazza!
Can you pl. elaborate a little more about "this should really be in another table". What would properly normalized tables look like in this case?

 

[Fazza]

Google has many hits on data normalisation. Such as http://databases.about.com/od/specificproducts/a/normalization.htm

The SQL used normalises table 1.

The current unworkable structure is: usertype, europe, americas, asia

Per the SQL, it changes to: usertype, location, howmany

The present structure adds a field for every location. The modified one just adds records.

Please google for further info.