Please help me on this multiple join Query.....

hi all,
Can someone help me with this?
I have a query that runs perfectly on SQL Server 2000 but when i run the same Query on Access 2000 with same data, it gives a message:


'Syntax Error in Query Expression'


I am attaching the query.Please tell me where the problem lies....I would be much obliged.

select
distinct(p.firstname) as PatientName,
(
select sum(c1.amount)from charge c1 where c1.patientid=p.patientid group by c1.patientid
)-
(
select sum(pa1.amount) from payment pa1 where pa1.patientid=p.patientid group by pa1.patientid
)-
(
select sum(cpd1.amount) from chargepaymentdetails cpd1
inner join
(
charge c2
inner join
patient p2 on p2.patientid=c2.patientid and p2.patientid=p.patientid
)
on cpd1.chargeid=c2.chargeid)as PatientBalance from patient p inner join (
charge c
inner join
(
payment pa inner join chargepaymentdetails cpd on pa.chargeid=cpd.chargeid) on c.chargeid=pa.chargeid) on p.patientid=c.patientid

i've only got access 97 so i'm not sure about access 2000, but you cannot use a subselect in the SELECT list

your best bet is to take the subselects and save each one as a saved query (equivalent to a view)

then you can do a multiple join from all the saved queries


rudy