Fiscal Year Crosstab Query

[Terpsfan]

How does one set up a fiscal year crosstab query? The default behavior is by the calendar year. Is there any setting in Access I can set to change this, or do I have to do it in code? Any help would be appreciated.

 

[Blorf]

Well, first what is your transoform value? Is it a date, or just a month name?

I assume you want to change from Jan - Dec and go from your Fiscal start to end.

You end up having to do some small conversions, but it's not that hard. Can you post your cross tab query and your fiscal start / end, and he format of your pivot value?

Thanks,
ChaZ

 

[dhookom]

You can add a number of months to a date field to "shift" the year of the date field. As ChaZ suggests, you haven't provide more than a vague specification.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Terpsfan]

it's basically a review date that I am using as the column header. It's in a short date format. I want the date in quarter format...Datepart("q",ReviewDate)


Yes, I was not smart enough to figure out the answer to this, so this is why I am asking people who are much smarter than me.

 

[dhookom]

I'm even more confused. Where did "quarters" come from? Can you provide some sample data and how you would like it to display in the report?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Blorf]

Agreed, confused.

Duane, how's your grandson?

Sorry, saw it and had to ask. Congradulations by the way.

ChaZ

 

[Terpsfan]

Ok, so you've called me a moron twice. I'll figure it out myself. I'll post the result when I come up with the solution.

 

[dhookom]

<OT>
Please excuse the bragging...
Just took his first steps tonight

http://www.jackson.hookom.net/images/firststeps.wmv

his 1st birthday is Thursday
</OT>

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Blorf]

Omega, we want to help, but need more specific info.

Dhookom, your grandon is adorable. My wife and I watched the video several times. We are looking forward to our first one. Hopefully soon. Working on it.

Any way, congradulations, and give your grandson a great big teady bear for me.

ChaZ

 

[JonFer]

Omega - here is a similar question:

thread701-892514

I would avoid the IIf options and use the DateAdd functions.

John

http://www.interautomateconsulting.com

 

[Terpsfan]

PARAMETERS [@Year] Long;
TRANSFORM Avg(qryResults.Result) AS AvgOfResult
SELECT qryResults.QNum, Avg(qryResults.Result) AS [Total Of Result]
FROM qryResults
WHERE qryResults.ReviewDate Between DateSerial([@Year]-1,10,1) And DateSerial([@Year],9,30)
GROUP BY qryResults.QNum
PIVOT "Qtr " & Format(DateAdd("m",3,[ReviewDate]),"q") In ("Qtr 1","Qtr 2","Qtr 3","Qtr 4");

 

[dhookom]

Omega36,
I don't see any issues with the SQL. What problem are you experiencing?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[CosmoKramer]

Duane,

I don't think there is a problem. In an earlier post Omega36 said:

I'll post the result when I come up with the solution.

I'm CosmoKramer, and I approve this message.