Self join problem

[ponderdj]

Hello All,

This started out as a report question, but has become a query question...

I have created a self join so that I may compare a field to itself. I want to list a count of [PDC](which will give me the # of transactions), and I want a sum of [MRC] per buyer, (which will give me total money spent per buyer)
Where [selling program]="TRNB" There are like 15 different buyers, and I don't want to write a seperate query for each buyer.

The query works fine, the problem occurs when I put it into a report(or in a bigger report as a subreport) and attempt to sum the count of [PDC] and sum [MRC]
I receive the error, "Multi-Level group clause is not allowed in subquery."

I came up with a work-around that works only if you run the query first, then close it, then run the report. (Don't ask me why) You can look at the semi-solution here:

http://www.tek-tips.com/viewthread.cfm?qid=989566&page=1

I think that I need to re-write the query, is there a better way to do this without writing a seperate query from the seller to each buyer?

The SQL is below with some redundancy omitted:

SELECT BaseLine1.[Buying Program], BaseLine1.[Selling Program], (SELECT Count([CSA]) FROM tblISCCSAsbaseline WHERE [Buying Program]=BaseLine1.[Buying Program] And [Selling Program]="TRNB") AS CountOfPDC, (SELECT SUM([Total MRC (Baseline)]) FROM tblISCCSAsbaseline WHERE  [Selling Program]="TRNB" AND BaseLine1.[Buying Program]=[Buying Program]) AS MRC, (SELECT SUM([Total MRC (Baseline)]) FROM tblISCCSAsbaseline WHERE  [Selling Program]="TRNB") AS Total, (SELECT SUM([October MRC]) FROM tblISCCSAsbaseline WHERE Baseline1.[Buying Program] =tblISCCSAsbaseline.[Buying Program] AND [Selling Program]="TRNB") AS [October 04] 
FROM tblISCCSAsbaseline AS BaseLine1
GROUP BY BaseLine1.[Buying Program], BaseLine1.[Selling Program]
HAVING ((BaseLine1.[Selling Program])="TRNB");

Hope that made sense,
Thanks