MS Query Help

[gilligan73]

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

 

[sunnyland]

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.