Calculating YTD totals on a Fiscal Year

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rob - 16 Jul 2007 15:54 GMT

I have created a query that sums data by month in Categories. I want to
create from this query or within this query a ytd total of the summed data
("Sentin000's") where the fiscal year begins in October. I also want to
create a percentage of these categories compared to a dollar amount that is
not a total of the summed data mentioned here but a different base. The
categories are LB, SB, SDB, WOSB, VOSB, SDVOSB, AND HUBZ. I want the base to
be the total of the LB and SB Spentin000's.

I believe that I can use a sub report to calculate the base but I don't know
how to get the ytd amount based on an entry in a popup specifying the month
that the year to date is calculated on. I am at my wit's end and I have been
reading some of the questions and answers in the forum but I don't understand
where to put the formula's that are mentioned and if they would suffice for
my query. Please Help!!

Rob

Reply to this Message

KARL DEWEY - 16 Jul 2007 20:38 GMT

This will give you the sum for the present fiscal year.

SELECT Table2.Sentin000, Sum(Table2.Field1) AS SumOfField1,
Format(DateAdd("m",+3,[YOURdate]),"yyyy") AS [Fiscal Year]
FROM Table2
GROUP BY Table2.Sentin000, Format(DateAdd("m",+3,[YOURdate]),"yyyy")
HAVING (((Format(DateAdd("m",+3,[YOURdate]),"yyyy"))=Format(Date(),"yyyy")));

Signature

KARL DEWEY
Build a little - Test a little

> I have created a query that sums data by month in Categories. I want to
> create from this query or within this query a ytd total of the summed data
[quoted text clipped - 12 lines]
>
> Rob

Reply to this Message

Rob - 16 Jul 2007 22:04 GMT

Thanks so much! One more thing, can you think of a way that I can get the
base to calculate within this same query. I have tried this and I get the
error message "At most, only one record can be found".

Here is the code:

Base: Sum((SELECT [Small Business Activity Detail]![Spent] FROM [Small
Business Activity Detail]
WHERE [Minority Indicator Categories] = "sb"
or [Minority Indicator Categories] = "lb"))

> This will give you the sum for the present fiscal year.
>
[quoted text clipped - 20 lines]
> >
> > Rob

Reply to this Message

KARL DEWEY - 16 Jul 2007 23:22 GMT

Try this after you change "YOURdate" to your field name.

Base: Sum(SELECT [Small Business Activity Detail]![Spent] FROM [Small
Business Activity Detail]
WHERE [Minority Indicator Categories] = "sb" or [Minority Indicator
Categories] = "lb"
HAVING Format(DateAdd("m",+3,[YOURdate]),"yyyy")=Format(Date(),"yyyy"))

Signature

KARL DEWEY
Build a little - Test a little

> Thanks so much! One more thing, can you think of a way that I can get the
> base to calculate within this same query. I have tried this and I get the
[quoted text clipped - 31 lines]
> > >
> > > Rob

Reply to this Message

Rob - 17 Jul 2007 15:38 GMT

Thanks Karl but when I enter this code I automatically get a syntax error
message. I really would appreciate it if you would look over this code and
tell me where the error is. I used exactly what you wrote except for the
YourDate which is ActualDate2.

> Try this after you change "YOURdate" to your field name.
>
[quoted text clipped - 39 lines]
> > > >
> > > > Rob

Reply to this Message

KARL DEWEY - 17 Jul 2007 18:52 GMT

Did you type or paste it? Copying a post and pasting can add returns in the
SQL.
Vary the size of the window to check the text wrap verus hard return.

Signature

KARL DEWEY
Build a little - Test a little

> Thanks Karl but when I enter this code I automatically get a syntax error
> message. I really would appreciate it if you would look over this code and
[quoted text clipped - 44 lines]
> > > > >
> > > > > Rob

Reply to this Message

Rob - 17 Jul 2007 18:58 GMT

I made sure that the code is exactly what is written here. Still receive a
syntax error.

Rob

> Did you type or paste it? Copying a post and pasting can add returns in the
> SQL.
[quoted text clipped - 48 lines]
> > > > > >
> > > > > > Rob

Reply to this Message

KARL DEWEY - 17 Jul 2007 20:36 GMT

This is a subquery and I have not took the time to learn what I need to about
them.
Try this --
Base: Sum(SELECT [Small Business Activity Detail]![Spent] FROM [Small
Business Activity Detail]
WHERE [Minority Indicator Categories] = "sb" or [Minority Indicator
Categories] = "lb" AND
Format(DateAdd("m",+3,[YOURdate]),"yyyy")=Format(Date(),"yyyy"))

If this does not work then repost so others can help.

Signature

KARL DEWEY
Build a little - Test a little

> I have created a query that sums data by month in Categories. I want to
> create from this query or within this query a ytd total of the summed data
[quoted text clipped - 12 lines]
>
> Rob

Reply to this Message