Microsoft Query

Question

Microsoft Query

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

david.john.gilligan Default port Points: 1471 More actions · Answer 1

Oh...and thanks for your help....forgot to say it...not in a good mood right now about this MS Query. I will let you know either the fix or when I have given up :doze:

Don Urquhart SSCrazy Points: 2423 More actions · Answer 2

the "as Country" in the inner query aliases the field (which could have diff. names) as Country, so you need to use that in the outer query. Try this (Country in first select, not CountryName) :

select a.Country, 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

If you don't stand for something, you'll fall for anything!,

Don Urquhart

david.john.gilligan Default port Points: 1471 More actions · Answer 3

I was just going to write that I changed it when I saw it....it is all a blur now....but I get the same error thing about the '('....maybe UNION ALL is not allowed in Tables but OK in named ranges....now I really want to know what is up with this....

Thanks again. Once I decide on this I can get back to being productive....

Don Urquhart SSCrazy Points: 2423 More actions · Answer 4

the only other possibility that I see (haven't set up a test) is that you have aliased the inner table names without the $ so you might have to use that name in all references.

Try either this (Remove $ from field ref.s in inner qry):

select a.Country, 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

OR this (no ref.s)

select a.Country, Sum(a.amt) as Total

from (

SELECT [CountryName] as Country, [FinalAmount] as amt

FROM [TheData$] TheData

union all

SELECT [CountryName] as Country, [TotalKV] as amt

FROM [ConcreteData$] ConcreteData

) a

If you don't stand for something, you'll fall for anything!,

Don Urquhart

david.john.gilligan Default port Points: 1471 More actions · Answer 5

Thanks...but same as before.....I am going to leave work now...bring my PC from work and probably look at it with a clearer head.

I'll keep you posted.

Cheers,

Dave

david.john.gilligan Default port Points: 1471 More actions · Answer 6

Got it. Well you did....I took your code from above and added back the GROUP BY a.Country that we mistakenly omitted in the form. This is obvious from the error message "Cannot add table '('" :hehe:

So the following code will produce correct results....it is however much slower than my "kludge" but requires no interaction (read: mistakes) by the user and the report is run infrequently.....

So, the code that works is:

select a.Country, 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

GROUP BY a.Country

Maybe some of the other ones did but without the GROUP BY at the end, the all failed with the (only?) error message MS Query threw (throws?).

I hope I never have to work on SQL with Microsoft Query again. Ever. :w00t:

As we say here, tusen takk - 1000 thanks!

Don Urquhart SSCrazy Points: 2423 More actions · Answer 7

Good catch! I can't believe I missed that one. Well actually, I can - it was real early here & the coffee hadn't hit the min. cognizance level.

Your welcome! Hope you have no more MS query nightmares.

If you don't stand for something, you'll fall for anything!,

Don Urquhart

david.john.gilligan Default port Points: 1471 More actions · Answer 8

No, my bad. I think I saw you are in the UK and I am in Oslo so I was more cafinated than you were at the time, in one hour one can consume much coffee. Thanks again. And the slow performance is just because there are about a million "blankish", (unused but there) so I am sure it will be a much more elegant solution. The sad thing, this could have been solved much more quickly if MS Query gave SOME kind of feedback rather than the rubbish they gave as error messages, like I used to with MS SQL Server...what line did I screw up on? What is REALLY wrong. Tusen takk!!!

Don Urquhart SSCrazy Points: 2423 More actions · Answer 9

You were prob. much more caffeinated! I'm actually west coast of Canada (Vancouver area).

If you don't stand for something, you'll fall for anything!,

Don Urquhart