By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,893 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

calculating totals in a query

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
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
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.