Calculating YTD totals on a Fiscal Year

[Rob]

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

 

[KARL DEWEY]

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

 

[Rob]

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

 

[KARL DEWEY]

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

 

[Rob]

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.

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

 

[KARL DEWEY]

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

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.

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

 

[Rob]

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.
Vary the size of the window to check the text wrap verus hard return.
--
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
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.

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

 

[KARL DEWEY]

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.