Recategorise Date Results

[ChrisBuchanan]

Hi.

I'm trying to sort an existing categorising query to categorise by financial years. I've managed that, but it won't re-categorise once the extra calculation fields are added. Any ideas?

SELECT   [CM Database].[Age Range],
         Count([CM Database].[Age Range]) AS [Count],
         Year([Date Contacted])-IIf([Date Contacted]<DateSerial(Year([Date Contacted]),4,1),1,0) AS FinYr,
         [FinYr] & "/" & Right(([FinYr]+1),2) AS [Financial Year]
FROM     [CM Database]
GROUP BY [CM Database].[Age Range],
         Year([Date Contacted])-IIf([Date Contacted]<DateSerial(Year([Date Contacted]),4,1),1,0),
         [FinYr] & "/" & Right(([FinYr]+1),2)
HAVING   ((([CM Database].[Age Range]) Is Not Null)
   AND    ((Year([Date Contacted])-IIf([Date Contacted]<DateSerial(Year([Date Contacted]),4,1),1,0))=[Year]))
ORDER BY [CM Database].[Age Range];

[NeoPa]

This post has been split from another thread. I couldn't see any relevance to that thread but it is at Connection ACCESS 2007 with SQL Performance in case that's important. I expect, as a new poster, you're simply unaware of some of the dos & don'ts related to posting on our forums. A quick browse through the Help link at the top of each page will help you there I'm sure.

Firstly, I have tidied up your post somewhat to make it easier to read, with code tags etc. Please note and follow suit in future.

The question probably made sense to you when you wrote it down, but from the perspective of someone who doesn't already know what you're thinking, it's a bit confusing. I'll hazard a guess and you can explain further if I'm wide of the mark. Please remember in future that, for much better results, it does help to read your post back after posting to make sure it conveys what you intend.

In this case, I think you're (probably) falling over conversion issues. Where you are dealing with dates or numbers, it is better to leave the data as dates or numbers until all processing has been finished (IE. Don't convert then try to use the converted results for any further categorisations). Converting numbers to formatted strings, or dates to either numbers or formatted strings, inhibits the ability further down the line of processing the items in their native form. You are reduced to treating them in their new form(s) which can be restricting.

I hope this resolves your issue and Welcome to Bytes.