ms access - Combine 2 queries with sum involved -

i have 2 similar queries both table being switched treatment patient in second query:

select treatment.phys_id phys_id, physician.fname, physician.lname, sum(treatment.charge) totcharge  physician  inner join treatment on physician.phys_id = treatment.phys_id group treatment.phys_id, physician.fname, physician.lname; 

the output of both is:

phys_id___fname___lname____totcharge 

when combined, need add 2 queries' columns of totcharge actual totcharge. however, when union these 2 queries tables stack on top of each other , union rearanges both tables identical phys_ids next each other. how can make 2 queries' totcharges add up?

you can use subqueries add charges @ physician level:

select physician.phys_id phys_id, physician.fname, physician.lname,     (select nz(sum(treatment.charge))     treatment treatment.phys_id = physician.phys_id) +    (select nz(sum(patient.charge))    patient patient.phys_id = physician.phys_id) total charge  physician; 

alternatively, can use dsum (strictly ms access function , not ansi sql).

select physician.phys_id phys_id, physician.fname, physician.lname,      nz(dsum("charge", "treatment", "phys_id =" &  physician.phys_id)) +     nz(dsum("charge", "patient", "phys_id =" & physician.phys_id)) total charge  physician;