Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

query with crosstab subquery

Posted on 2005-03-01
3
Medium Priority
?
621 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
  • 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

577 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