Using PIVOT in Access - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Using PIVOT in Access

Author

Topic

harsha123_8
Starting Member

2 Posts

Posted - 2004-07-07 : 22:59:35

I am using a crosstab query and using following statement as part
of the query to get sales results.

PIVOT Format(salesdate, 'yyyy')

Result will be

Product 2001 2002 2003 2004
A
B
C

Now i want to get it for financial year. That is from 1st April previous year to 31st March current year. (Eg. 1st April 2001-31st March 2002, 1st April 2002 - 31st March 2003 etc..)

like this

Product 2001/2002 2002/2003 2003/2004
A
B
C

Can any genius help please.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-07 : 23:37:14

Instead of using Format(salesdate, 'yyyy'), you need to write a function that returns the FY instead.

e.g.
IIF(Month(salesDate)<7, Year(salesdate)-1 & "-" & Year(salesdate), Year(salesdate) & "-" & Year(salesdate) + 1)

In other words, if the month is before the FY cutoff, then the FY will be the previous year to the current one. Otherwise, it's the other way around. I haven't tested this, but it would give you the basic idea.

NB: The above is based on FY running from 1-Jul to 30-Jun. If it's the UK where the FY starts in the middle of a month the function would be a bit more complicated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources