modify crosstab query to fiscal qtrs rather than calandar qtrs

[Lisacu]

I need to provide quarterly data based on our fiscal year, not the calendar
year. I am able to create a crosstab query, but don't know how to modify it
for our fiscal year. Thank you

 

[scubadiver]

Create a query with a new field that classifies the months into quarters and
use that as the source for the crosstab.

 

[Lisacu]

Thanks for the reply. Please explain more.

Create a query with a new field that classifies the months into quarters and
use that as the source for the crosstab.

 

[scubadiver]

If you could provide more information about what you have already then my
response might be more constructive.

Presumably you are basing your crosstab on another table or query? Add the
following as an extra field in the query

Quarter: Format([datefield], "q")

It will then classify the dates into 1,2,3 or 4 starting from January. As
said, you can use that new field in the crosstab.

 

[Lisacu]

You're correct. I based the crosstab query on another query. And I have
already used Quarter: Format([datefield], "q"), which produced quarters from
Jan on, but my desire is for Q1 to reflect Jul-Sep, Q2 to reflect Oct-Dec,
and so on, based on our fiscal year Jul-Jun. Any ideas?

If you could provide more information about what you have already then my
response might be more constructive.

Presumably you are basing your crosstab on another table or query? Add the
following as an extra field in the query

Quarter: Format([datefield], "q")

It will then classify the dates into 1,2,3 or 4 starting from January. As
said, you can use that new field in the crosstab.

Thanks for the reply. Please explain more.

 

[scubadiver]

If you haven't already worked it out:

Q: IIf([Quarter]<3,[quarter]+2,[quarter]-2)

You're correct. I based the crosstab query on another query. And I have
already used Quarter: Format([datefield], "q"), which produced quarters from
Jan on, but my desire is for Q1 to reflect Jul-Sep, Q2 to reflect Oct-Dec,
and so on, based on our fiscal year Jul-Jun. Any ideas?

If you could provide more information about what you have already then my
response might be more constructive.

Presumably you are basing your crosstab on another table or query? Add the
following as an extra field in the query

Quarter: Format([datefield], "q")

It will then classify the dates into 1,2,3 or 4 starting from January. As
said, you can use that new field in the crosstab.

Thanks for the reply. Please explain more.

:
Create a query with a new field that classifies the months into quarters and
use that as the source for the crosstab.

:

I need to provide quarterly data based on our fiscal year, not the calendar
year. I am able to create a crosstab query, but don't know how to modify it
for our fiscal year. Thank you