Group by fiscal months

[Guest]

The company has an odd way of summarizing data - their fiscal months start
before or after the actual first day of the month (but always on a Mon). The
number of weeks in a fiscal month vary from 4 to 5. The dates on which a
fiscal month change from one year to another, and every 6th year there will
be an extra week added into December to catch up.

I have a text report that is grouped on months. I created a query that
calculates the start date and end date for each fiscal month. How can I
summarize on those fiscal months in this report? I also have a line graph
which shows tons of product per month. How can I change it from regular
months to fiscal months?

 

[Guest]

We don't know much about your table structure or your "query that calculates
the start date and end date for each fiscal month". I would expect you could
add the query to your report's record source and set the criteria under the
significant date field to:
Between [StartDate] And [EndDate]

 

[Guest]

The results of the fiscal month query contain the month "Jan", "Feb" etc, and
then the calculated start date and end date for each month, "12/26/05" for
Jan start, "1/29/06" for Jan end, "1/30/06 for Feb start etc. The query
returns the fiscal months for a specific year, so only has 12 records in it.

The query that is the record source for the report contains the Machine#, a
shift date & time, product number (which is counted), and product tons (which
is summed). Right now I have the report grouped by month([shiftdate]). I
need to determine in which "fiscal month" each row of data belongs, then
group by the fiscal month and display "Jan", "Feb" etc. Once I know how to
add the fiscal month to the recourdsource query, I can group by that field,
but I'm not sure how to add the fiscal month without creating a really
complex Iif statement that compares the "shiftdate" with every fiscal month's
start and end date. Is there a better way of doing it?

I appreciate your help!

We don't know much about your table structure or your "query that calculates
the start date and end date for each fiscal month". I would expect you could
add the query to your report's record source and set the criteria under the
significant date field to:
Between [StartDate] And [EndDate]

--
Duane Hookom
Microsoft Access MVP

The company has an odd way of summarizing data - their fiscal months start
before or after the actual first day of the month (but always on a Mon). The
number of weeks in a fiscal month vary from 4 to 5. The dates on which a
fiscal month change from one year to another, and every 6th year there will
be an extra week added into December to catch up.

I have a text report that is grouped on months. I created a query that
calculates the start date and end date for each fiscal month. How can I
summarize on those fiscal months in this report? I also have a line graph
which shows tons of product per month. How can I change it from regular
months to fiscal months?

 

[John Spencer]

Two ways that I can think of to solve this problem. One method while
tedious to set up is really flexible.

First Way
Create a table of dates for the entire period - All dates from Jan 1
1995 to Dec 31 2020

Have a field (or fields) that specify which fiscal month the date
belongs to. Then all you need to do is to link any date field to the
date field in the table to get the fiscal month.


Second way, is to use your present query as a source in the from cluase
and use a non-equi join to get the fiscal month. YOu cannot build a
non-equi join in the design (query grid) view. The SQL of the query
would look something like the following.

SELECT *
FROM yourTable INNER JOIN YourFiscalMonthQuery
ON YourTable.ShiftDate >= YourFiscalMonthQuery.StartDate
AND YourTable.ShiftDate <=YourFiscalMonthQuery.EndDate


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The results of the fiscal month query contain the month "Jan", "Feb" etc, and
then the calculated start date and end date for each month, "12/26/05" for
Jan start, "1/29/06" for Jan end, "1/30/06 for Feb start etc. The query
returns the fiscal months for a specific year, so only has 12 records in it.

The query that is the record source for the report contains the Machine#, a
shift date & time, product number (which is counted), and product tons (which
is summed). Right now I have the report grouped by month([shiftdate]). I
need to determine in which "fiscal month" each row of data belongs, then
group by the fiscal month and display "Jan", "Feb" etc. Once I know how to
add the fiscal month to the recourdsource query, I can group by that field,
but I'm not sure how to add the fiscal month without creating a really
complex Iif statement that compares the "shiftdate" with every fiscal month's
start and end date. Is there a better way of doing it?

I appreciate your help!

We don't know much about your table structure or your "query that calculates
the start date and end date for each fiscal month". I would expect you could
add the query to your report's record source and set the criteria under the
significant date field to:
Between [StartDate] And [EndDate]

--
Duane Hookom
Microsoft Access MVP

The company has an odd way of summarizing data - their fiscal months start
before or after the actual first day of the month (but always on a Mon). The
number of weeks in a fiscal month vary from 4 to 5. The dates on which a
fiscal month change from one year to another, and every 6th year there will
be an extra week added into December to catch up.

I have a text report that is grouped on months. I created a query that
calculates the start date and end date for each fiscal month. How can I
summarize on those fiscal months in this report? I also have a line graph
which shows tons of product per month. How can I change it from regular
months to fiscal months?

 

[Guest]

In addition to John's quality remarks, I would not use text months in the
fiscal month query. If you attempt to sort on Jan, Feb, Mar, Apr,... "Apr"
will be followed by "Aug" etc. I would use a month number like 1, 2, 3, ...
--
Duane Hookom
Microsoft Access MVP

Two ways that I can think of to solve this problem. One method while
tedious to set up is really flexible.

First Way
Create a table of dates for the entire period - All dates from Jan 1
1995 to Dec 31 2020

Have a field (or fields) that specify which fiscal month the date
belongs to. Then all you need to do is to link any date field to the
date field in the table to get the fiscal month.


Second way, is to use your present query as a source in the from cluase
and use a non-equi join to get the fiscal month. YOu cannot build a
non-equi join in the design (query grid) view. The SQL of the query
would look something like the following.

SELECT *
FROM yourTable INNER JOIN YourFiscalMonthQuery
ON YourTable.ShiftDate >= YourFiscalMonthQuery.StartDate
AND YourTable.ShiftDate <=YourFiscalMonthQuery.EndDate


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The results of the fiscal month query contain the month "Jan", "Feb" etc, and
then the calculated start date and end date for each month, "12/26/05" for
Jan start, "1/29/06" for Jan end, "1/30/06 for Feb start etc. The query
returns the fiscal months for a specific year, so only has 12 records in it.

The query that is the record source for the report contains the Machine#, a
shift date & time, product number (which is counted), and product tons (which
is summed). Right now I have the report grouped by month([shiftdate]). I
need to determine in which "fiscal month" each row of data belongs, then
group by the fiscal month and display "Jan", "Feb" etc. Once I know how to
add the fiscal month to the recourdsource query, I can group by that field,
but I'm not sure how to add the fiscal month without creating a really
complex Iif statement that compares the "shiftdate" with every fiscal month's
start and end date. Is there a better way of doing it?

I appreciate your help!

We don't know much about your table structure or your "query that calculates
the start date and end date for each fiscal month". I would expect you could
add the query to your report's record source and set the criteria under the
significant date field to:
Between [StartDate] And [EndDate]

--
Duane Hookom
Microsoft Access MVP


:

The company has an odd way of summarizing data - their fiscal months start
before or after the actual first day of the month (but always on a Mon). The
number of weeks in a fiscal month vary from 4 to 5. The dates on which a
fiscal month change from one year to another, and every 6th year there will
be an extra week added into December to catch up.

I have a text report that is grouped on months. I created a query that
calculates the start date and end date for each fiscal month. How can I
summarize on those fiscal months in this report? I also have a line graph
which shows tons of product per month. How can I change it from regular
months to fiscal months?

 

[Guest]

Thank you both. I appreciate your quick response!

In addition to John's quality remarks, I would not use text months in the
fiscal month query. If you attempt to sort on Jan, Feb, Mar, Apr,... "Apr"
will be followed by "Aug" etc. I would use a month number like 1, 2, 3, ...
--
Duane Hookom
Microsoft Access MVP

Two ways that I can think of to solve this problem. One method while
tedious to set up is really flexible.

First Way
Create a table of dates for the entire period - All dates from Jan 1
1995 to Dec 31 2020

Have a field (or fields) that specify which fiscal month the date
belongs to. Then all you need to do is to link any date field to the
date field in the table to get the fiscal month.


Second way, is to use your present query as a source in the from cluase
and use a non-equi join to get the fiscal month. YOu cannot build a
non-equi join in the design (query grid) view. The SQL of the query
would look something like the following.

SELECT *
FROM yourTable INNER JOIN YourFiscalMonthQuery
ON YourTable.ShiftDate >= YourFiscalMonthQuery.StartDate
AND YourTable.ShiftDate <=YourFiscalMonthQuery.EndDate


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

The results of the fiscal month query contain the month "Jan", "Feb" etc, and
then the calculated start date and end date for each month, "12/26/05" for
Jan start, "1/29/06" for Jan end, "1/30/06 for Feb start etc. The query
returns the fiscal months for a specific year, so only has 12 records in it.

The query that is the record source for the report contains the Machine#, a
shift date & time, product number (which is counted), and product tons (which
is summed). Right now I have the report grouped by month([shiftdate]). I
need to determine in which "fiscal month" each row of data belongs, then
group by the fiscal month and display "Jan", "Feb" etc. Once I know how to
add the fiscal month to the recourdsource query, I can group by that field,
but I'm not sure how to add the fiscal month without creating a really
complex Iif statement that compares the "shiftdate" with every fiscal month's
start and end date. Is there a better way of doing it?

I appreciate your help!

:

We don't know much about your table structure or your "query that calculates
the start date and end date for each fiscal month". I would expect you could
add the query to your report's record source and set the criteria under the
significant date field to:
Between [StartDate] And [EndDate]

--
Duane Hookom
Microsoft Access MVP


:

The company has an odd way of summarizing data - their fiscal months start
before or after the actual first day of the month (but always on a Mon). The
number of weeks in a fiscal month vary from 4 to 5. The dates on which a
fiscal month change from one year to another, and every 6th year there will
be an extra week added into December to catch up.

I have a text report that is grouped on months. I created a query that
calculates the start date and end date for each fiscal month. How can I
summarize on those fiscal months in this report? I also have a line graph
which shows tons of product per month. How can I change it from regular
months to fiscal months?