MS Query Help

gilligan73

New Member
Joined
Jul 16, 2014
Messages
8
Hello all,

I seem to be having a problem with MS Query, as I am used to MS SQL database but am not allowed to use it. I am trying to join two queries, based on the country name being the same. I actually got it work once but I can't seem to see what I am doing wrong.

I am using Excel 2010:

The sheet names are theData and ConcreteData:

select a.CountryName, Sum(a.FinalAmount) as Total
from
(SELECT [TheData$].CountryName as Country, [TheData$].FinalAmount as amt
FROM [TheData$] [TheData$]
union all
SELECT [ConcreteData$].Country as Country, [ConcreteData$].TotalKV as amt
FROM [ConcreteData$] [ConcreteData$]
) a
GROUP BY a.Country

I get "Could not add the table 'SELECT'" as an error message.

Many thanks in advance,
Dave
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
I only have Excel 2007 so for me this will work. You can see if it works the same in 2010:

select country, sum(amt) as Total
from
(SELECT `ConcreteData$`.Country as [country] , `ConcreteData$`.Totalkv as [amt]
FROM `M:\Excel\queryMs.xlsx`.`ConcreteData$` `ConcreteData$`
union all
SELECT `theData$`.Countryname as [country] , `theData$`.finalamount as [amt]
FROM `M:\Excel\queryMs.xlsx`.`theData$` `theData$`)
Group by country;

Path will need changing.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,082
Messages
5,466,535
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top