[RESOLVED] lost on subquery- monthly total vs. commulative total

know this is simple but the query is evading me.
here's the deal:

i want to get the total monthly items: sum(ItemNo),
total monthy amount: sum(ItemAmt), comulative item
from certain date<?>, commulative amount from certain
date<?>. My problem is i dont know how to get the
commulative amount from january to current month.

Here's what i've got so far:

Code:

---

SELECT pc, consum_act, contr_no,sales_code, SUM(cynoitems) As total, sum(dollamt) as dollar
    (select sum(a.cynoitems) as comtotal, sum(cydollamt) as comdoll from msd a  inner join msd aa on a.pc =aa.pc
and a.consum_act = aa.consum_act and a.contr_no =aa.contr_no and a.sales_code = aa.sales_code where a.cydate between cdate("10/01/2008") and cdate ("01/01/2009") )
FROM msd a
WHERE a.cydate = cdate("01/01/2009")
GROUP BY pc,consum_act,contr_no,sales_code

---

the subquery part is the commulative for a given date range <this is where im lost>

First of all, don't use CDate(string value) to specify a Date, as how the value will be interpreted varies depending on things outside of your control. Instead you should explicitly specify a Date value, eg: #2/23/2009# (no matter what settings you have on your computer, this is always interpreted as mm/dd/yyyy format).

Next up, you shouldn't be re-specifying the alias "a" in the subquery, you should just be specifying the aa version and putting the 'join' conditions into the Where clause of the sub-query.

However, as you want two values, you can't use a single sub-query - you need to either use two, or more efficiently put the join into the main query instead.


I think this should do it:

Code:

---

SELECT a.pc, a.consum_act, a.contr_no, a.sales_code,
      SUM(a.cynoitems) As total, sum(a.dollamt) as dollar,
      sum(aa.cynoitems) as comtotal, sum(cydollamt) as comdoll

FROM msd a
INNER JOIN msd aa
  ON  a.pc =aa.pc
  AND a.consum_act = aa.consum_act
  AND a.contr_no =aa.contr_no
  AND a.sales_code = aa.sales_code
  AND aa.cydate between #10/01/2008# AND a.cydate

WHERE a.cydate = #01/01/2009#
GROUP BY a.pc, a.consum_act, a.contr_no, a.sales_code

---

Thanks, i know this is simple, but i'm still struggling with subqueries. i'll try this tomorrow.

when i have questions i'll try to (si_the_geek) see the geek. or should i say ask the geek.

copy paste the code run the query and i got this error msg:

Between operator without And in query expression:

im running this query on ms access 2000

Try this modification.

Code:

---

SELECT a.pc, a.consum_act, a.contr_no, a.sales_code,
      SUM(a.cynoitems) As total, sum(a.dollamt) as dollar,
      sum(aa.cynoitems) as comtotal, sum(cydollamt) as comdoll

FROM msd a
INNER JOIN msd aa
  ON  a.pc =aa.pc
  AND a.consum_act = aa.consum_act
  AND a.contr_no =aa.contr_no
  AND a.sales_code = aa.sales_code
  AND (aa.cydate between #10/01/2008# AND a.cydate)

WHERE a.cydate = #01/01/2009#
GROUP BY a.pc, a.consum_act, a.contr_no, a.sales_code

---