Solved

Crosstab Union query giving weird Error About Name of Query

Posted on 2013-01-29
13
354 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

740 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