Aggregate query help

K

ken

below is my aggregate query, I am trying to pull financial information based
on 8 key fields listed in the group by below. I am pulling project to date
(all), month to date (this month), and year to date (months 1-4) using an
iif() function inside a sum. I am appending the results of this query into a
table using another query and when I check the results in the table they are
totally wrong.

I have written many aggregate queries before in other databases, I do not
know what I am doing wrong here. Help/suggestions greatly appreciated.

SELECT mid_local.org_lc AS orig_par, mid_local.Ferc, mid_local.Pnt AS Point,
mid_local.rsrc AS resource_cd, mid_local.bnft_lc AS bnft_par,
iif(mid_local.work_order_id = 0,NULL,mid_local.work_order_id) AS
work_order_id, iif(mid_local.task_id = 0,NULL,mid_local.task_id) AS task_id,
iif(mid_local.vnd_cd = '',NULL,left(mid_local.vnd_cd,6)) AS vendor_cd,
Sum(iif(mid_local.gl_yr = CInt(getYear()) AND mid_local.gl_mth =
CInt(getMonth()),nz(trns_amt_f,0),0)) AS MTDActuals, Sum(iif(mid_local.gl_yr
= CInt(getYear()) AND mid_local.gl_mth <=
CInt(getMonth()),nz(trns_amt_f,0),0)) AS YTDActuals, Sum(nz(trns_amt_f,0)) AS
PTDActuals
FROM mid_local
GROUP BY mid_local.org_lc, mid_local.Ferc, mid_local.Pnt, mid_local.rsrc,
mid_local.bnft_lc, mid_local.work_order_id, mid_local.task_id,
mid_local.vnd_cd
ORDER BY mid_local.org_lc, mid_local.Ferc, mid_local.Pnt, mid_local.rsrc,
mid_local.bnft_lc, mid_local.work_order_id, mid_local.task_id,
mid_local.vnd_cd;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top