Access Dsum in Query - Microsoft Community

i'd 1 line of data each month in year, i've entered following query design. instead of rendering one  line per month, renders 7 or 8 lines per month revenue amounts not add proper amount month. i've tried other variations dartpart, or changing dlookup, have not come answer yet. suggestions?

year: datepart("yyyy",[apptdate])

month: datepart("m",[apptdate])

revenue: dsum("[revenue]","tblappointment","[apptdate]=#" & ([apptdate]) & "#")

tricky in 1 query.  think simplest way use 2 instances of query gave in second query.  revenue previous month can returned in subquery using dateserial function return date of first day of each month , restricting result difference 1 month:

let's assume query gave before:

select year(apptdate) apptyear,
month(apptdate) apptmonth,
sum(revenue) monthlyrevenue
tblappointment
group year(apptdate), month(apptdate);

has been saved qrymonthlyrevenues, second query this:

select apptyear,apptmonth, monthlyrevenue,
    (select monthlyrevenue
      qrymonthlyrevenues mr2
      datediff("m",dateserial(mr2.apptyear,mr2.apptmonth,1),
      dateserial(mr1.apptyear,mr1.apptmonth,1))=1)
previousmonthlyrevenue
qrymonthlyrevenues mr1;

2 instances of original query differentiated giving them aliases of mr1 , mr2.  allows subquery correlated outer query.  you'll find that, due correlation, won't fast original query.

Office / Access / Windows 8 / Office 2013