Calculating YTD totals on a Fiscal Year

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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")));
 
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"))


KARL DEWEY said:
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")));

--
KARL DEWEY
Build a little - Test a little


Rob said:
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
Click to expand...
Click to expand...
 
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"))

--
KARL DEWEY
Build a little - Test a little


Rob said:
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"))


KARL DEWEY said:
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")));

--
KARL DEWEY
Build a little - Test a little


Rob said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

KARL DEWEY said:
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"))

--
KARL DEWEY
Build a little - Test a little


Rob said:
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"))


KARL DEWEY said:
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")));

--
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
("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
Click to expand...
Click to expand...
Click to expand...
 
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.
--
KARL DEWEY
Build a little - Test a little


Rob said:
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.

KARL DEWEY said:
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"))

--
KARL DEWEY
Build a little - Test a little


Rob said:
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.

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")));

--
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
("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
Click to expand...
Click to expand...
Click to expand...
 
I made sure that the code is exactly what is written here. Still receive a
syntax error.

Rob

KARL DEWEY said:
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.
--
KARL DEWEY
Build a little - Test a little


Rob said:
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.

KARL DEWEY said:
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"))

--
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
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.

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")));

--
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
("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
Click to expand...
Click to expand...
Click to expand...
 
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.
 
Back
Top