Solved

Crosstab Union query giving weird Error About Name of Query

Posted on 2013-01-29
13
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

734 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