Solved

Crosstab Union query giving weird Error About Name of Query

Posted on 2013-01-29
13
352 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38833091
this also not correct

Right(String(10," ")

what are you trying to do?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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 120

Expert Comment

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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

778 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