Fiscal Months Query

[Guest]

I have Fiscal Months that are very strange. i.e. November 2006 is from
October 23 through November 19, 2006.

I need to sort dates by Fiscal Month and sum the associated dollar amounts.
The sorting dates and dollar amounts come from a query result.

I'd like to stay away from VB, although I am thinking of a very long Case
Statement that might be able to handle it.

Any ideas?

 

[Douglas J. Steele]

What about a table containing the Month/Day Start and Month/Day End, with an
appropriate SortOrder field in it, and then link to that table?

 

[Guest]

Build a reference table containing a FiscalMonth, StartDate, and an EndDate
field.
In your totals query add both tables but do not join.
Add the FiscalMonth field in the grid as a Group By field.
Use this as criteria on you date field ---
Between [StartDate] And [EndDate]

I am not sure how you treat your fiscal year.

 

[Guest]

Hi Karl and Douglas,

I like the idea and will work it.

I'm planning on generating a report that will show all the Fiscal Months for
the entire project with the totals for each. I'm not planning on providing
the option of using the Between function. Wil this matter?

Build a reference table containing a FiscalMonth, StartDate, and an EndDate
field.
In your totals query add both tables but do not join.
Add the FiscalMonth field in the grid as a Group By field.
Use this as criteria on you date field ---
Between [StartDate] And [EndDate]

I am not sure how you treat your fiscal year.

I have Fiscal Months that are very strange. i.e. November 2006 is from
October 23 through November 19, 2006.

I need to sort dates by Fiscal Month and sum the associated dollar amounts.
The sorting dates and dollar amounts come from a query result.

I'd like to stay away from VB, although I am thinking of a very long Case
Statement that might be able to handle it.

Any ideas?

 

[Guest]

I do not understand how you will make it work without using the Between
function except to use >= Start AND <= End which is the same thing.

Post your SQL so your method can be looked and learned from.

Hi Karl and Douglas,

I like the idea and will work it.

I'm planning on generating a report that will show all the Fiscal Months for
the entire project with the totals for each. I'm not planning on providing
the option of using the Between function. Wil this matter?

Build a reference table containing a FiscalMonth, StartDate, and an EndDate
field.
In your totals query add both tables but do not join.
Add the FiscalMonth field in the grid as a Group By field.
Use this as criteria on you date field ---
Between [StartDate] And [EndDate]

I am not sure how you treat your fiscal year.

I have Fiscal Months that are very strange. i.e. November 2006 is from
October 23 through November 19, 2006.

I need to sort dates by Fiscal Month and sum the associated dollar amounts.
The sorting dates and dollar amounts come from a query result.

I'd like to stay away from VB, although I am thinking of a very long Case
Statement that might be able to handle it.

Any ideas?

 

[Guest]

I was thinking of not using the Parameter Box for the Between options to
choose only one month at a time. Didn't mean to confuse you.

I tried what you said. I created the FiscalMonth Table, inputed some dates,
then put the table into the total query. I turned on the Group By function.
Once I added the Fiscal Month field to the query, I got 1668 records instead
of 138. When I input the
Between [StartDate] And [EndDate] into the Criteria in the date field, I
got a wierd message "you tried to execute a query that does not include the
specific expression "table name". [Most Recent Date] between [StartDate] And
[EndDate] as part of an aggregate function."

What am I doing wrong?

I do not understand how you will make it work without using the Between
function except to use >= Start AND <= End which is the same thing.

Post your SQL so your method can be looked and learned from.

Hi Karl and Douglas,

I like the idea and will work it.

I'm planning on generating a report that will show all the Fiscal Months for
the entire project with the totals for each. I'm not planning on providing
the option of using the Between function. Wil this matter?

Build a reference table containing a FiscalMonth, StartDate, and an EndDate
field.
In your totals query add both tables but do not join.
Add the FiscalMonth field in the grid as a Group By field.
Use this as criteria on you date field ---
Between [StartDate] And [EndDate]

I am not sure how you treat your fiscal year.

:

I have Fiscal Months that are very strange. i.e. November 2006 is from
October 23 through November 19, 2006.

I need to sort dates by Fiscal Month and sum the associated dollar amounts.
The sorting dates and dollar amounts come from a query result.

I'd like to stay away from VB, although I am thinking of a very long Case
Statement that might be able to handle it.

Any ideas?

 

[Guest]

Post your query SQL.

I was thinking of not using the Parameter Box for the Between options to
choose only one month at a time. Didn't mean to confuse you.

I tried what you said. I created the FiscalMonth Table, inputed some dates,
then put the table into the total query. I turned on the Group By function.
Once I added the Fiscal Month field to the query, I got 1668 records instead
of 138. When I input the
Between [StartDate] And [EndDate] into the Criteria in the date field, I
got a wierd message "you tried to execute a query that does not include the
specific expression "table name". [Most Recent Date] between [StartDate] And
[EndDate] as part of an aggregate function."

What am I doing wrong?

I do not understand how you will make it work without using the Between
function except to use >= Start AND <= End which is the same thing.

Post your SQL so your method can be looked and learned from.

Hi Karl and Douglas,

I like the idea and will work it.

I'm planning on generating a report that will show all the Fiscal Months for
the entire project with the totals for each. I'm not planning on providing
the option of using the Between function. Wil this matter?

:

Build a reference table containing a FiscalMonth, StartDate, and an EndDate
field.
In your totals query add both tables but do not join.
Add the FiscalMonth field in the grid as a Group By field.
Use this as criteria on you date field ---
Between [StartDate] And [EndDate]

I am not sure how you treat your fiscal year.

:

I have Fiscal Months that are very strange. i.e. November 2006 is from
October 23 through November 19, 2006.

I need to sort dates by Fiscal Month and sum the associated dollar amounts.
The sorting dates and dollar amounts come from a query result.

I'd like to stay away from VB, although I am thinking of a very long Case
Statement that might be able to handle it.

Any ideas?

 

[Guest]

This works for me.
SELECT FiscalMonth.FiscalMonth, Sum([Change Requests].y) AS SumOfy
FROM [Change Requests], FiscalMonth
WHERE ((([Change Requests].z) Between [StartDate] And [enddate]))
GROUP BY FiscalMonth.FiscalMonth;

Post your query SQL.

I was thinking of not using the Parameter Box for the Between options to
choose only one month at a time. Didn't mean to confuse you.

I tried what you said. I created the FiscalMonth Table, inputed some dates,
then put the table into the total query. I turned on the Group By function.
Once I added the Fiscal Month field to the query, I got 1668 records instead
of 138. When I input the
Between [StartDate] And [EndDate] into the Criteria in the date field, I
got a wierd message "you tried to execute a query that does not include the
specific expression "table name". [Most Recent Date] between [StartDate] And
[EndDate] as part of an aggregate function."

What am I doing wrong?

I do not understand how you will make it work without using the Between
function except to use >= Start AND <= End which is the same thing.

Post your SQL so your method can be looked and learned from.

:

Hi Karl and Douglas,

I like the idea and will work it.

I'm planning on generating a report that will show all the Fiscal Months for
the entire project with the totals for each. I'm not planning on providing
the option of using the Between function. Wil this matter?

:

Build a reference table containing a FiscalMonth, StartDate, and an EndDate
field.
In your totals query add both tables but do not join.
Add the FiscalMonth field in the grid as a Group By field.
Use this as criteria on you date field ---
Between [StartDate] And [EndDate]

I am not sure how you treat your fiscal year.

:

I have Fiscal Months that are very strange. i.e. November 2006 is from
October 23 through November 19, 2006.

I need to sort dates by Fiscal Month and sum the associated dollar amounts.
The sorting dates and dollar amounts come from a query result.

I'd like to stay away from VB, although I am thinking of a very long Case
Statement that might be able to handle it.

Any ideas?

 

[Guest]

Hi Karl,

I haven't had a chance to get back to this but I'll work on it, hopefully
tomorrow. Thanks again for the instruction. I'll let you know how it goes.

This works for me.
SELECT FiscalMonth.FiscalMonth, Sum([Change Requests].y) AS SumOfy
FROM [Change Requests], FiscalMonth
WHERE ((([Change Requests].z) Between [StartDate] And [enddate]))
GROUP BY FiscalMonth.FiscalMonth;

Post your query SQL.

I was thinking of not using the Parameter Box for the Between options to
choose only one month at a time. Didn't mean to confuse you.

I tried what you said. I created the FiscalMonth Table, inputed some dates,
then put the table into the total query. I turned on the Group By function.
Once I added the Fiscal Month field to the query, I got 1668 records instead
of 138. When I input the
Between [StartDate] And [EndDate] into the Criteria in the date field, I
got a wierd message "you tried to execute a query that does not include the
specific expression "table name". [Most Recent Date] between [StartDate] And
[EndDate] as part of an aggregate function."

What am I doing wrong?



:

I do not understand how you will make it work without using the Between
function except to use >= Start AND <= End which is the same thing.

Post your SQL so your method can be looked and learned from.

:

Hi Karl and Douglas,

I like the idea and will work it.

I'm planning on generating a report that will show all the Fiscal Months for
the entire project with the totals for each. I'm not planning on providing
the option of using the Between function. Wil this matter?

:

Build a reference table containing a FiscalMonth, StartDate, and an EndDate
field.
In your totals query add both tables but do not join.
Add the FiscalMonth field in the grid as a Group By field.
Use this as criteria on you date field ---
Between [StartDate] And [EndDate]

I am not sure how you treat your fiscal year.

:

I have Fiscal Months that are very strange. i.e. November 2006 is from
October 23 through November 19, 2006.

I need to sort dates by Fiscal Month and sum the associated dollar amounts.
The sorting dates and dollar amounts come from a query result.

I'd like to stay away from VB, although I am thinking of a very long Case
Statement that might be able to handle it.

Any ideas?