June 26, 2014 at 3:06 am
I know this is the wrong forum to post but I have no idea where to post and this is just a shot in the dark as it MUST use only MS Query in Excel 2010 (bosses orders, NO MS SQL database), so I am trying to use MS Query to use Excel as my Database. All is running smoothly until I came to this. I need to add the totals by country from two different tables. For example:
SELECT DISTINCT theBigData.`Country Name` AS 'Country', Sum(theBigData.`Final Amount`) AS 'Total'
FROM theBigData theBigData
GROUP BY theBigData.`Country Name`
Will give me a nice summary for one table and
SELECT DISTINCT theLittleData.`Country` AS 'Country2', Sum(theLittleData.`Total`) AS 'Total2'
FROM theLittleData theLittleData
GROUP BY theLittleData.`Country`
Will give me the second group. What I want to do is add the two country totals. The # of the countries will not always be the same but they will be spelled the same way in both. I have tried 100 times and got every error they could throw at me. I know how to do this in MS SQL but MS Query for Excel has me baffled. Any ideas are much appreciated. BTW. the "tables" are named ranges in Excel.
Thanks in advance,
Dave
June 27, 2014 at 10:24 am
You could do something like the following:
select a.Country, Sum(a.Amt) as Total
from (
SELECT TheBigData.Country as Country, TheBigData.amt as amt
FROM `C:\DataTestSource.xlsx`.TheBigData TheBigData
union all
SELECT TheLittleData.Country as Country, TheLittleData.amt as amt
FROM `C:\DataTestSource.xlsx`.TheLittleData TheLittleData
) a
group by a.Country
This simply combines both data sources then groups and sums them.
Don Urquhart
June 30, 2014 at 8:18 am
You have given me some good ideas but I hate this MS Query...but I am not allowed to use SQL Server for this...it chokes with a "Could not add the table '('
I think it just the pepeculiarities for MS Query....if I can't get it I could always pivot or VBA code it in if I have to...
Thanks for your suggestion!
Best,
Dave
June 30, 2014 at 8:30 am
I had that same problem and it seemed to give me that when I had syntax errors elsewhere in the query. I would double check everything.
Don Urquhart
June 30, 2014 at 8:33 am
Actually getting somewhere.....by removing the
`C:\DataTestSource.xlsx`.
I know the numbers that do not have matching pairs are right....now to test the ones that do....but, hell, it runs now!
Cheers.
Dave
June 30, 2014 at 8:44 am
Well "Shazam!" and "Gawwww-leeeee"....that did the trick.....
but I still do not like the MS Query....
One thousand thanks!
Dave
June 30, 2014 at 8:48 am
Your Welcome! Glad I could help.
FYI - The C:\TestSource ref. was a separate workbook for data; wasn't sure of your setup and wouldn't be required for a single workbook as you found out.
I whole heartedly agree that MSQuery is ugly at best. Too bad you can't show your boss and convince him to actually use SQL Server.
Don Urquhart
June 30, 2014 at 9:20 am
Yes, I knew that and tried both ways. I wish it was for my boss but it was to help another department. And MS Access was off limits too (only one person will ever use it at a time). My boss would have gone straight SQL server and not this ms query mixed with macros and pivots I have to use.
And it took be a good hour to figure out that on ms query you need to use the slanted apostrophe and not the straight one or else the ever so helpful "syntax error"
Thanks again,
Dave
July 17, 2014 at 1:37 am
Hate to bring up MS Query again but the "boss" has a rule that I cannot even use access for a quarterly report that only one user will ever use...but...I am stuck having to use MS Query....
While the information above worked, I have since changed to a dynamic range or using spreadsheet as the datasource (either one is fine, but the data is changing in size)....
I have tried to modify based on two sheets, as dynamic ranges do not appear in MS Query for Excel...so I will use the entire worksheet....
Here is what I have:
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
but I get again stopped by "Could not add the table 'SELECT'"...
my god I never realized how much I hate MS Query
Thanks in advance,
Dave
July 17, 2014 at 6:38 am
As far as I know MSQuery only works with named ranges. That is all I've ever used.
Don Urquhart
July 17, 2014 at 7:14 am
Don Urquhart (7/17/2014)
As far as I know MSQuery only works with named ranges. That is all I've ever used.
It will definately work on sheets when written with brackets and a trailing $....this ALMOST works....but for some crazy reason the numbers don't add correctly if the country had amounts in both the sheet "TheData" and "ConcreteData":
SELECT CountryName, (SUM(FinalAmount)) as Total
FROM [TheData$] TheData
GROUP BY CountryName
will return the correct data for the tab "TheData" and this:
SELECT Country, (SUM(TotalKV)) as Total
FROM [ConcreteData$] ConcreteData
GROUP BY CountryName
will work for the other table....
but using your logic, I tried this
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
Gives me a meaningless error about the '(' which I have learned to know that is some kind of other error somewhere else....So the sheet can definately be used...just this bloody syntax and useless errors are killing me. This is exactly why MS Query is NOT on my CV!!!!!:w00t::hehe::crazy:
July 17, 2014 at 7:45 am
Your initial sum is wrong. It should be referencing the field names from the inner Union query (amt) not FinalAmount, which doesn't exist.
It should be:
select a.CountryName, Sum(a.amt) 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
Don Urquhart
July 17, 2014 at 7:57 am
Got it (what you mean) but still getting the "Could not add table '('".....which means there is something maybe slightly off in syntax. I have worked a very ugly work around using macros and pivots which I might resort to using this "database" project in Excel is starting to get to me.....last time I got the error was because I for got a [ or $ or something totally unrelated to the error message. I now, officially, really hate MS Query......
July 17, 2014 at 8:01 am
You have CountryName in the outer query and have renamed them to Country in the inner union queries.
Really dumb that they don't give something even remotely helpful for messages.
Don Urquhart
July 17, 2014 at 8:11 am
One was called CountryName on one sheet and Country on the other (they come from two different systems) so I renamed the column to CountryName on both.
Tried:
select a.CountryName, Sum(a.amt) as Total
from (
SELECT [TheData$].[CountryName] as Country, [TheData$].[FinalAmount] as amt
FROM [TheData$] TheData
union all
SELECT [ConcreteData$].[CountryName] as Country, [ConcreteData$].[TotalKV] as amt
FROM [ConcreteData$] ConcreteData
) a
Again same error......Might have to let Moby Dick go soon and use the "kludgy" ugly macros and pivots....
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply