Beginning Balance Query

Hello all,
I would like a total (beginning balance) field in a query be the total for
the year up to the month based on another field within the query, so that
when the fiscal_date = '5/1/2006' that I would have a field contain totals
from '1/1/2006' thru '4/30/2006'. I would like to do this in one query, I am
trying to populate a table with 6 years of data that would have a beginning
balance field and then todays current amount based off of a Fiscal_Date fiel
d.
My problem is I cannot figure out how to do a sum of prior data based off of
another fields current date. Am I trying to do too much in one query to
populate the table?Lyners wrote:
> Hello all,
> I would like a total (beginning balance) field in a query be the total for
> the year up to the month based on another field within the query, so that
> when the fiscal_date = '5/1/2006' that I would have a field contain totals
> from '1/1/2006' thru '4/30/2006'. I would like to do this in one query, I
am
> trying to populate a table with 6 years of data that would have a beginnin
g
> balance field and then todays current amount based off of a Fiscal_Date fi
eld.
> My problem is I cannot figure out how to do a sum of prior data based off
of
> another fields current date. Am I trying to do too much in one query to
> populate the table?
>
Without DDL, I don't know your actual table/field names, but something
like this should be close:
SELECT
Table2.FiscalDate,
(SELECT SUM(Amount) FROM Table1 WHERE DATEPART(year, DateField) =
DATEPART(year, Table2.FiscalDate) AND DATEPART(month, DateField) <
DATEPART(month, Table2.FiscalDate)) AS FiscalAmount
FROM Table2|||Thank you Tracy,
Yes, I did something close to this. What I ended up doing was create 2
tablkes that were identical, and then used a cross join where one of my
criteria is that a.date < b.date, then in the select statement I added a
sum(a.amount). This way I sum all a.amount that is less than b.date.
This way I have all of the b table fields and the total of the amount from
the a table all together.
Thank you,
Lyners
"Tracy McKibben" wrote:

> Lyners wrote:
> Without DDL, I don't know your actual table/field names, but something
> like this should be close:
> SELECT
> Table2.FiscalDate,
> (SELECT SUM(Amount) FROM Table1 WHERE DATEPART(year, DateField) =
> DATEPART(year, Table2.FiscalDate) AND DATEPART(month, DateField) <
> DATEPART(month, Table2.FiscalDate)) AS FiscalAmount
> FROM Table2
>|||Why would you create a second identical table when a VIEW would most likely
have worked -without the issues of keeping 2 tables syncronized?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Lyners" <Lyners@.discussions.microsoft.com> wrote in message
news:7FA08953-C425-43CD-A198-3CBC08EA229E@.microsoft.com...
> Thank you Tracy,
> Yes, I did something close to this. What I ended up doing was create 2
> tablkes that were identical, and then used a cross join where one of my
> criteria is that a.date < b.date, then in the select statement I added a
> sum(a.amount). This way I sum all a.amount that is less than b.date.
> This way I have all of the b table fields and the total of the amount from
> the a table all together.
> Thank you,
> Lyners
> "Tracy McKibben" wrote:
>|||Good point Arnie. I actually just created a large query that has 2 identical
select statements. The second select statement I total the amount field base
d
on the cross join where clause. I am in the design phase of this report, so
in the future, I will most likely create a view (faster) of the select query
and just reference that instead of requerying everytime.
Thanks for the performance pointer.
"Arnie Rowland" wrote:

> Why would you create a second identical table when a VIEW would most likel
y
> have worked -without the issues of keeping 2 tables syncronized?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Lyners" <Lyners@.discussions.microsoft.com> wrote in message
> news:7FA08953-C425-43CD-A198-3CBC08EA229E@.microsoft.com...
>
>