Solved

Crosstab Union query giving weird Error About Name of Query

Posted on 2013-01-29
13
349 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
Comment Utility
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
Comment Utility
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
Comment Utility
this also not correct

Right(String(10," ")

what are you trying to do?
0
 

Author Comment

by:IEHP1
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Perfect!!! Aliasing and order of operations very important!!!!
0
 
LVL 119

Expert Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

12 Experts available now in Live!

Get 1:1 Help Now