?
Solved

query with crosstab subquery

Posted on 2005-03-01
3
Medium Priority
?
620 Views
Last Modified: 2008-03-17
I want to create a single query of 2 query.

note : this is an simple example of what i want to do not the real example.

this is my subquery named SubQuery1

TRANSFORM Count(dbo_Call.Call_ID) AS CompteDeCall_ID
  SELECT dbo_Call.Call_EnteredBy
  FROM (
   SELECT dbo_Lang.Lang_Id, dbo_Lang.Lang_Text
   FROM dbo_Lang
   WHERE (((dbo_Lang.Lang_Tag)="LST_CALLUDF3") AND ((dbo_Lang.Lang_Flags)=0))
   ORDER BY dbo_Lang.Lang_Id) [reqPlage] INNER JOIN dbo_Call ON reqPlage.Lang_Id = dbo_Call.Call_LST_CALLUDF3
   GROUP BY dbo_Call.Call_EnteredBy PIVOT reqPlage.Lang_Text

if i do SELECT * FROM SubQuery1 all work fine.

but now i try to do a single query out of this without the subquery1
so I do this

SELECT * FROM (
TRANSFORM Count(dbo_Call.Call_ID) AS CompteDeCall_ID
  SELECT dbo_Call.Call_EnteredBy
  FROM (
   SELECT dbo_Lang.Lang_Id, dbo_Lang.Lang_Text
   FROM dbo_Lang
   WHERE (((dbo_Lang.Lang_Tag)="LST_CALLUDF3") AND ((dbo_Lang.Lang_Flags)=0))
   ORDER BY dbo_Lang.Lang_Id) [reqPlage] INNER JOIN dbo_Call ON reqPlage.Lang_Id = dbo_Call.Call_LST_CALLUDF3
   GROUP BY dbo_Call.Call_EnteredBy PIVOT reqPlage.Lang_Text
) SubQuery1

this code dont work... it return syntax error form clause and select the TRANSFORM word, so how i could write it to make it work in a single query?
0
Comment
Question by:numeriss
[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
  • 2
3 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 2000 total points
ID: 13431571
Hmm, is indeed a problem, but why you you need to select from a crosstable query in another query ?
In general limiting the basic data will give the needed column headers...

Nic;o)
0
 

Author Comment

by:numeriss
ID: 13431655
because these query are dynamic and i want to do

rs.open myQuery

and i would like to do it without having to add a temp subquery...
0
 
LVL 54

Expert Comment

by:nico5038
ID: 13431937
I proposed to use a WHERE in your an embedded query to extract just those rows needed by limiting the "column value".
What's the select you need on this crosstable query ?

Nic;o)
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

777 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