Current Month Total and Commulative Total

[NuBie via AccessMonster.com]

I 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:

SELECT PC, ACTTIVITY, SUM(ITEMNO) as "MONTHLYTOTALITEM" ,SUM(ITEMAMT) AS
"MONTHLYTOTALAMOUNT", <totalitemfromjantocurrent>,
<totalamountromjantocurrent>
FROM MSD
WHERE FORMAT(CYDATE,"MMM") = "FEB"
GROUP BY PC, ACTIVITY



Table Design
PC
Activity
ItemNo
ItemAmt
CyDate


Report Layout

PC Activity
MonthItemTotal MonthDollarTotal Jan-CurrenMonthTotal Jan-
CurrenMonthDollar


TIA

 

[Allen Browne]

Use a subquery.

Details in:
Subquery basics: Year-to-date
at:
http://allenbrowne.com/subquery-01.html#YTD

 

[NuBie via AccessMonster.com]

of course..thanks i'll follow the link

Use a subquery.

Details in:
Subquery basics: Year-to-date
at:
http://allenbrowne.com/subquery-01.html#YTD

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

[quoted text clipped - 28 lines]

TIA

 

[NuBie via AccessMonster.com]

im still not familiar with subquerries, have patience with me.

still couldn't get the running total from certain date to current date (the
subquery). im using only one table

SELECT pc, consum_act, contr_no,sales_code, SUM(cynoitems) As total,
(select sum(a.cynoitems) 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") ) as
running
FROM msd a
WHERE a.cydate = cdate("01/01/2009")
GROUP BY pc,consum_act,contr_no,sales_code

of course..thanks i'll follow the link

Use a subquery.

[quoted text clipped - 8 lines]

 

[Allen Browne]

You are using the same alias in the subquery as in the main query?
I'm not sure what the JOIN in the subquery is for.

Perhaps you want something like this:

SELECT pc,
consum_act,
contr_no,sales_code,
SUM(cynoitems) As total,
(select sum(a.cynoitems) AS howmuch
from msd AS a
where a.cydate between #10/01/2008# and #01/01/2009#
AND a.pc = msd.pc
AND a.consum_act = msd.consum_act
AND a.sales_code = msd.sales_code) as running
FROM msd
WHERE msd.cydate = #01/01/2009#
GROUP BY pc, consum_act, contr_no, sales_code;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

im still not familiar with subquerries, have patience with me.

still couldn't get the running total from certain date to current date
(the
subquery). im using only one table

SELECT pc, consum_act, contr_no,sales_code, SUM(cynoitems) As total,
(select sum(a.cynoitems) 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") ) as
running
FROM msd a
WHERE a.cydate = cdate("01/01/2009")
GROUP BY pc,consum_act,contr_no,sales_code

of course..thanks i'll follow the link

Use a subquery.

[quoted text clipped - 8 lines]

 

[NuBie via AccessMonster.com]

Thanks Allen...this is what im looking for

You are using the same alias in the subquery as in the main query?
I'm not sure what the JOIN in the subquery is for.

Perhaps you want something like this:

SELECT pc,
consum_act,
contr_no,sales_code,
SUM(cynoitems) As total,
(select sum(a.cynoitems) AS howmuch
from msd AS a
where a.cydate between #10/01/2008# and #01/01/2009#
AND a.pc = msd.pc
AND a.consum_act = msd.consum_act
AND a.sales_code = msd.sales_code) as running
FROM msd
WHERE msd.cydate = #01/01/2009#
GROUP BY pc, consum_act, contr_no, sales_code;

im still not familiar with subquerries, have patience with me.

[quoted text clipped - 18 lines]

[quoted text clipped - 8 lines]