Solved

Crosstab Union query giving weird Error About Name of Query

Posted on 2013-01-29
13
350 Views
Last Modified: 2013-01-29
So I have gotten the question titled "Crosstab query constraint of only 1 Value" answered and the query is running just fine.

I have another query where I need to be able to pull in 2 different values in a Crosstab format from the same table, but it keeps giving me this weird error about the Name of the Query (as in what I change the name to in the Navigation Pane)

This is the syntax I am using:

TRANSFORM Right(String(10," ") & Format(Sum(['Auth Count per1k & Avg Auth Cost'].[MyValue]),IIf([source]="count","#,###","$#,###.00")),10) AS MyValue
SELECT ['Auth Count per1k & Avg Auth Cost'].Source
FROM (SELECT date, "Count" AS Source, Count(region_plan_comparison.count) AS MyValue
FROM region_plan_comparison
GROUP BY date, "Count"
UNION
SELECT date, "Value" AS Source, Avg(region_plan_comparison.count) AS MyValue
FROM region_plan_comparison
GROUP BY date, "Value")  AS ['Auth Count per1k & Avg Auth Cost']
WHERE ((([region_plan_comparison].[domain])="auth_req_spec_amt1k"))
GROUP BY ['Auth Count per1k & Avg Auth Cost'].Source
PIVOT Format([region_plan_comparison.].[date],"yyyy" & "-" & "mm");


And this is the error I am getting:

wierd crosstab union query error
0
Comment
Question by:IEHP1
  • 7
  • 6
13 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38832686
try removing the single quote on this

['Auth Count per1k & Avg Auth Cost']

to be

[Auth Count per1k & Avg Auth Cost]

also

avoid using special characters in the name of your objects, see this link

http://support.microsoft.com/kb/826763
0
 

Author Comment

by:IEHP1
ID: 38833032
Yeah, I did that and got the following error message:

new weird error
I am not sure why? If you notice in the screenshot, the other query that is using the same SQL syntax (xtab a union query) has an & ampersand symbol as well, but that one works??

Is it my Right function in the TRANSFORM line??
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38833091
this also not correct

Right(String(10," ")

what are you trying to do?
0
 

Author Comment

by:IEHP1
ID: 38833106
That's what I mean, too.....both of my data types for this one are of the Number data type (the field called count)

So I played with removing it along with some of the closing parentheses, but then it gives me an even scarier error??
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38833113
also, i would rename this query
Auth Count per1k & Avg Auth Cost

with

Auth_Count_per1k_and_Avg_Auth_Cost
0
 

Author Comment

by:IEHP1
ID: 38833139
nope.....when I renamed it to that, it just went back to the original error ??

I tried to like manually debug the syntax by taking out the " quotes from top to bottom to see which part is causing the error, but couldn't pinpoint it??
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38833163
that is not the only problem that you have, but avoiding special characters in naming your objects will help preventing errors..
0
 

Author Comment

by:IEHP1
ID: 38833176
I don't understand? I have another query that has the same syntax (same format of the syntax) and it works just fine??

FYI, just to let you know and in no way am I trying to be disrespectful towards you, fyed helped me with the syntax for the one that works......
0
 

Author Comment

by:IEHP1
ID: 38833268
So here is my SQL that gives the 2nd error(pct and count are Number data types):

TRANSFORM Right(String(10," ") & Format(Sum(['AuthCountper1kandAvgAuthCost'].[MyValue]),IIf([source]="count","#,###","$#,###.00")),10) AS MyValue
SELECT ['AuthCountper1kandAvgAuthCost'].Source
FROM (SELECT date, "Count" AS Source, Sum(region_plan_comparison.pct) AS MyValue
FROM region_plan_comparison
WHERE ((([region_plan_comparison].[domain])="auth_req_spec_1k"))
GROUP BY date, "Count"
UNION
SELECT date, "Value" AS Source, Avg(region_plan_comparison.count) AS MyValue
FROM region_plan_comparison
GROUP BY date, "Value")  AS ['AuthCountper1kandAvgAuthCost']
WHERE ((([region_plan_comparison].[domain])="auth_req_spec_amt1k"))
GROUP BY ['AuthCountper1kandAvgAuthCost'].Source
PIVOT Format([region_plan_comparison.].[date],"yyyy" & "-" & "mm");
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 38833335
try this


TRANSFORM Right(String(10," ") & Format(Sum(A.[MyValue]),IIf([source]="count","#,###","$#,###.00")),10) AS MyValue
SELECT A.Source
FROM (
SELECT date, "Count" AS Source, Sum(region_plan_comparison.pct) AS MyValue
FROM region_plan_comparison
WHERE ((([region_plan_comparison].[domain])="auth_req_spec_1k"))
GROUP BY date, "Count"
UNION
SELECT date, "Value" AS Source, Avg(region_plan_comparison.count) AS MyValue
FROM region_plan_comparison
WHERE ((([region_plan_comparison].[domain])="auth_req_spec_amt1k"))
GROUP BY date, "Value"
) AS A
GROUP BY A.Source
PIVOT Format(A.[date],"yyyy" & "-" & "mm");
0
 

Author Comment

by:IEHP1
ID: 38833478
Right on capricorn1,

I have to be careful about aliasing the table name and also about that the WHERE comes before the GROUP BY.......

I so much appreciate your help!!! Thank you!!!

I have a related question as well if I can....

Since this is Crosstabing a UNION query, would it be possible for me to put in another query and UNION that one, too (and even another one). So, for example:

TRANSFORM ~~~~~~~~~~~~~
SELECT A.Source
FROM (
SELECT
FROM
WHERE
GROUP BY
UNION
SELECT
FROM
WHERE
GROUP BY
UNION
SELECT
FROM
WHERE
GROUP BY
UNION
SELECT
FROM
WHERE
GROUP BY
) AS A
GROUP BY A.Source
PIVOT ~~~~~~~~


???
0
 

Author Closing Comment

by:IEHP1
ID: 38833486
Perfect!!! Aliasing and order of operations very important!!!!
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38833518
yes, you can, but there is a limit of ~64000 characters in a single SQL statement.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now