cross tab query from Access

From: Aleks (axiaxi2003_at_hotmail.com)
Date: 07/29/04 

Date: Thu, 29 Jul 2004 02:27:55 -0400

Hi,

I am still working on this cross tab query, I started by using Access,
created a query and called it "jen", this is the query:

SELECT Atts.UserId, Users.MailStr, Users.MaidenNm, Cases.Id, Cases.CaseId
FROM (Atts INNER JOIN Cases ON Atts.CaseId=Cases.Id) INNER JOIN Users ON
Atts.UserId=Users.UserId;

Then created a cross-tab query using the wizard, this is the result:

TRANSFORM Max(jen.UserId) AS MaxOfMailStr
SELECT jen.Id
FROM jen
GROUP BY jen.Id
PIVOT jen.UserId;

This will give me the desired result, by grouping by cases.id and it
displays the user.id for each one of the records. Problem is that when I try
to run the second query on sql query analyzer it doesnt work because there
is no "jen" object.

How can I combine both queries into one to run it in query analizer and get
the same result ?

THANKS IN ADVANCE !

Z

Relevant Pages

  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: Return just the first record that fits the parameter
    ... Be warned that coordinate subqueries are slow since they run the query one time for each record in the main query. ... FROM tblProcess INNER JOIN ... This is the only record for Plan 06-22. ... It would be convenient to be able to use "ABC 10" as the criteria for ProcName, and return all Plans that include that Process. ...
    (microsoft.public.access.queries)
  • Re: Matching records for an update query
    ... then the update query would look something like ... UPDATE RegisteredMembers INNER JOIN BusinessChanges ...
    (microsoft.public.access.queries)