calculating totals in a query

[Paul Mendez]

Performance_Date SumOfBudget_NOI CurrYTD_BudgetNOI_Total
1/1/2004 $4,184,626.00 ?
2/1/2004 $4,484,710.00 ?
3/1/2004 $4,537,424.00 ?
4/1/2004 $4,826,850.00 ?
5/1/2004 $4,966,326.00 ?

Can someone help? What I am trying to do is create a query that will
end up looking like the bottom example. The above query is a
calculated totals query with an aggregate sum function. What it is
doing is summing up all the Budget_NOIs for the month of January and
outputting it as shown (I got that done). And it will do that for
every month as time progresses. What I need help doing is calculating
the portion with the question marks. What I need it to do (below
there is an example on how I want it to look) is sum up all the
SumOfBudget_NOIs from January to the current month. For example, the
February amount would be the sum of both January's and February's
SumOfBudget_NOI. For March, it will be the sum January's, February's
and March's SumOfBudget_NOI. For April, it will be the sum January's,
February's, March's and April's SumofBudget_NOI. And so on for the
other months to come. I really have no idea how to approach this
problem. Any suggestions.
THANKS!

Performance_Date SumOfBudget_NOI CurrYTD_BudgetNOI_Total
1/1/2004 $4,184,626.00 $4,184,626.00
2/1/2004 $4,484,710.00 $8,669,336.00
3/1/2004 $4,537,424.00 $13,206,760.00
4/1/2004 $4,826,850.00 $18,033,610.00
5/1/2004 $4,966,326.00 $22,999,936.00

[Phil]

In the vb window (hit altF11) look up dateserial and associated
functions.

here's an example by grouped by date not month but you will get the
idea.

select pd, sum(b) as monthb,
first(
(select sum(pd) from tbl where month(pd) between 1 and month(pd)+1)
) as ytd
from tbl
group by pd;

Paul Mendez <pm*****@thelyndco.com> posted in
news:99**************************@posting.google.c om

Performance_Date SumOfBudget_NOI CurrYTD_BudgetNOI_Total
1/1/2004 $4,184,626.00 ?
2/1/2004 $4,484,710.00 ?
3/1/2004 $4,537,424.00 ?
4/1/2004 $4,826,850.00 ?
5/1/2004 $4,966,326.00 ?

Can someone help? What I am trying to do is create a query that will
end up looking like the bottom example. The above query is a
calculated totals query with an aggregate sum function. What it is
doing is summing up all the Budget_NOIs for the month of January and
outputting it as shown (I got that done). And it will do that for
every month as time progresses. What I need help doing is calculating
the portion with the question marks. What I need it to do (below
there is an example on how I want it to look) is sum up all the
SumOfBudget_NOIs from January to the current month. For example, the
February amount would be the sum of both January's and February's
SumOfBudget_NOI. For March, it will be the sum January's, February's
and March's SumOfBudget_NOI. For April, it will be the sum January's,
February's, March's and April's SumofBudget_NOI. And so on for the
other months to come. I really have no idea how to approach this
problem. Any suggestions.
THANKS!

Performance_Date SumOfBudget_NOI CurrYTD_BudgetNOI_Total
1/1/2004 $4,184,626.00 $4,184,626.00
2/1/2004 $4,484,710.00 $8,669,336.00
3/1/2004 $4,537,424.00 $13,206,760.00
4/1/2004 $4,826,850.00 $18,033,610.00
5/1/2004 $4,966,326.00 $22,999,936.00

--
Phil

[Paul Mendez]

that seems a little too complex for me, what and where did you want me
to put that. i thought i knew enough to understand, but i was so
wrong. Thanks Phil!
"Phil" <st***@basketball.net> wrote in message news:<AF*******************@newssvr24.news.prodigy .com>...

In the vb window (hit altF11) look up dateserial and associated
functions.

here's an example by grouped by date not month but you will get the
idea.

select pd, sum(b) as monthb,
first(
(select sum(pd) from tbl where month(pd) between 1 and month(pd)+1)
) as ytd
from tbl
group by pd;

Paul Mendez <pm*****@thelyndco.com> posted in
news:99**************************@posting.google.c om

Performance_Date SumOfBudget_NOI CurrYTD_BudgetNOI_Total
1/1/2004 $4,184,626.00 ?
2/1/2004 $4,484,710.00 ?
3/1/2004 $4,537,424.00 ?
4/1/2004 $4,826,850.00 ?
5/1/2004 $4,966,326.00 ?

Can someone help? What I am trying to do is create a query that will
end up looking like the bottom example. The above query is a
calculated totals query with an aggregate sum function. What it is
doing is summing up all the Budget_NOIs for the month of January and
outputting it as shown (I got that done). And it will do that for
every month as time progresses. What I need help doing is calculating
the portion with the question marks. What I need it to do (below
there is an example on how I want it to look) is sum up all the
SumOfBudget_NOIs from January to the current month. For example, the
February amount would be the sum of both January's and February's
SumOfBudget_NOI. For March, it will be the sum January's, February's
and March's SumOfBudget_NOI. For April, it will be the sum January's,
February's, March's and April's SumofBudget_NOI. And so on for the
other months to come. I really have no idea how to approach this
problem. Any suggestions.
THANKS!

Performance_Date SumOfBudget_NOI CurrYTD_BudgetNOI_Total
1/1/2004 $4,184,626.00 $4,184,626.00
2/1/2004 $4,484,710.00 $8,669,336.00
3/1/2004 $4,537,424.00 $13,206,760.00
4/1/2004 $4,826,850.00 $18,033,610.00
5/1/2004 $4,966,326.00 $22,999,936.00

[Phil]

in the query design use the sql view and copy and paste this sql. But
you have to give it your own field and table names, these are just for
example.

in the vba you can open up help and search for month and other date
functions. they allow you to manipulate dates in sql.

this example just shows how to use a subquery for getting the running
total.

post exact field names and I can make it for you. Then when you hit the
visual design window for queries you can see how it is done there.
Paul Mendez <pm*****@thelyndco.com> posted in
news:99*************************@posting.google.co m

that seems a little too complex for me, what and where did you want me
to put that. i thought i knew enough to understand, but i was so
wrong. Thanks Phil!
"Phil" <st***@basketball.net> wrote in message
news:<AF*******************@newssvr24.news.prodigy .com>...

In the vb window (hit altF11) look up dateserial and associated
functions.

here's an example by grouped by date not month but you will get the
idea.

select pd, sum(b) as monthb,
first(
(select sum(pd) from tbl where month(pd) between 1 and
month(pd)+1) ) as ytd
from tbl
group by pd;

Paul Mendez <pm*****@thelyndco.com> posted in
news:99**************************@posting.google.c om

Performance_Date SumOfBudget_NOI CurrYTD_BudgetNOI_Total
1/1/2004 $4,184,626.00 ?
2/1/2004 $4,484,710.00 ?
3/1/2004 $4,537,424.00 ?
4/1/2004 $4,826,850.00 ?
5/1/2004 $4,966,326.00 ?

Can someone help? What I am trying to do is create a query that
will end up looking like the bottom example. The above query is a
calculated totals query with an aggregate sum function. What it is
doing is summing up all the Budget_NOIs for the month of January and
outputting it as shown (I got that done). And it will do that for
every month as time progresses. What I need help doing is
calculating the portion with the question marks. What I need it to
do (below there is an example on how I want it to look) is sum up
all the SumOfBudget_NOIs from January to the current month. For
example, the February amount would be the sum of both January's and
February's SumOfBudget_NOI. For March, it will be the sum
January's, February's and March's SumOfBudget_NOI. For April, it
will be the sum January's, February's, March's and April's
SumofBudget_NOI. And so on for the other months to come. I really
have no idea how to approach this problem. Any suggestions.
THANKS!

Performance_Date SumOfBudget_NOI CurrYTD_BudgetNOI_Total
1/1/2004 $4,184,626.00 $4,184,626.00
2/1/2004 $4,484,710.00 $8,669,336.00
3/1/2004 $4,537,424.00 $13,206,760.00
4/1/2004 $4,826,850.00 $18,033,610.00
5/1/2004 $4,966,326.00 $22,999,936.00

--
Phil