multiple results - need to display on one line

[Guest]

Hi - I think this may have already been answered, but I'm having a hard time
with applying the solution to my problem.

I'm running a query from a linked database where I'm getting the count of
exams done by doctors. The list is appearing as follows:

Exam Desc Dr1 Dr2 Dr3 Dr4
exam1 3
exam1 6
exam1 12
exam2 3
exam2 3

But I would like it to appear as follows:

Exam desc Dr1 Dr2 Dr3 Dr4
exam1 3 6 12
exam2 3 3

What do I need to do?
Thankyou in advance.

 

[Guest]

Use a crosstab query.

 

[Guest]

You could also use a totals query on the current results like this --
Exam Desc Dr1 Dr2 Dr3 Dr4
Group By Sum Sum Sum Sum

 

[Guest]

It is a crosstab query.

Use a crosstab query.

 

[Guest]

So I would make another query off the existing crosstab query?

 

[Duane Hookom]

If it is a crosstab, why would you not suggest this in your first posting
and also provide the SQL view?

 

[Guest]

Please pardon me for not giving all the information needed. Since this is my
first posting, maybe you could cut me some slack?
Here is the SQL:

TRANSFORM Count(dbo_item.proc_desc_long) AS Expr1
SELECT dbo_item.proc_desc_long
FROM ((((dbo_activity INNER JOIN dbo_activity_info ON dbo_activity.acc_itn =
dbo_activity_info.acc_itn) INNER JOIN dbo_activity_result ON
dbo_activity.acc_itn = dbo_activity_result.acc_itn) INNER JOIN
dbo_document_xref ON dbo_activity_result.document_itn =
dbo_document_xref.document_itn) INNER JOIN dbo_item ON
(dbo_activity_info.dept = dbo_item.dept) AND (dbo_activity_info.proc_no =
dbo_item.proc_no)) INNER JOIN dbo_doctor ON dbo_document_xref.reading_dr =
dbo_doctor.dr_no
WHERE (((dbo_activity.proc_dtime) Between #1/1/2006# And #4/1/2006#) AND
((dbo_activity_info.cncl_dtime) Is Null) AND
((dbo_doctor.dr_user_fld_3)<>"RAD") AND ((dbo_doctor.dr_member_rad)="R"))
GROUP BY dbo_item.proc_desc_long, dbo_document_xref.reading_dr,
dbo_doctor.dr_first
PIVOT dbo_doctor.dr_last;

 

[Duane Hookom]

Change the GROUP BY to only:
GROUP BY dbo_item.proc_desc_long

 

[Guest]

Perfect - thank you for your assistance.

Change the GROUP BY to only:
GROUP BY dbo_item.proc_desc_long