Summing Dividends by Finacial Year

[barkly]

I have a small private MS Access database to record details of a share portfolio. Dividends received are recorded and these are generally received twice per year. I wish to develop a query that will aggregate on a yearly basis the dividends received from each stock, however it is not a calendar year aggregation but a financial year aggregation that I am after. My financial year runs from 1^st July to the 30^th June the following year.

This if dividends were received such as

StockA 1 Sept 2011 $100
StockA 1 Mar 2012 $120
StockA 1 Sep 2012 $130
StockA 1 Mar 2013 $135
StockA 1 Sep 2013 $140
StockA 1 Mar 2014 $145
StockA 1 Sep 2014 $150

The query output would look like this for the Financial year ending



StockA 2012 $220
StockA 2013 $265
StockA 2014 $285
StockA 2015 $150

The query I have tried gets a listing but no aggregation. I would appreciate if someone could point me in the right direction please

many thanks

SELECT tbl_Div_DRP.ASXCode, Sum(tbl_Div_DRP.DivAmt) AS SumOfDivAmt, tbl_Div_DRP.DivDate
FROM tbl_Div_DRP
GROUP BY tbl_Div_DRP.ASXCode, tbl_Div_DRP.DivDate
HAVING (([DivDate]>=("07/01/" & Year([DivDate])) And [DivDate]<=("06/30/" & (Year([DivDate])+1))))
ORDER BY tbl_Div_DRP.ASXCode;

[ranman256]

I have a report form (fiscal year OR cal yr.) that lets me select a month and it determines the date range.
The reports run off the range boxes.

[June7]

Instead of DivDate use Year(DivDate).

Better might be to build a report using Grouping & Sorting features with aggregate calcs in footers. This will allow display of detail records and summary calcs.

Is the DivDate field actually a date/time type and is formatted to show only mon/year?

[barkly]

Thanks ranman256 and June7 ,

I was keen to just open a form (with this query behind it) and not have the need to enter any details.

I saw on a video how to develop a form which had the Grouping Sorting and Aggregating features you have referred to above and is probably the simplest way to achieve what I am after and no doubt is what I should be using. However I was keen to try to find a way to do it within a query if possible as I am a novice at MS Access and doing this the hard(er) way just enhances your skills and usually gives you another technique to perhaps use somewhere else.
The field Divdate is a full date of day month and year.
What is not happening in the simple query is that there is no SUMing going on in respect of a fiscal year. If I use Dsum I end up aggregating the entire field list for DivAmt so that is not correct. I suspect there might be some complex logic required to do this.

regards and thanks

barkly

[June7]

Can bind a form to an aggregate but would not be able to edit the data.

The query is not summing by year because you have the full date value in the SELECT and GROUP BY clauses. Therefore the aggregation is by date and effectively no aggregation.

Did you try my suggestion to use Year(DivDate) instead?

[barkly]

June7 Thankyou for coming back again.

I have tried Year(DivDate) in every place I can see but I am not getting the result. But perhaps my method is not good - might you refer to my initial code and indicate exactly where you mean please.

My thanks

barkly

[June7]

Also, try without the HAVING clause - it really makes no sense. The ORDER BY might not even be necessary - try without and see what happens.

SELECT tbl_Div_DRP.ASXCode, Sum(tbl_Div_DRP.DivAmt) AS SumOfDivAmt, Year(DivDate)
FROM tbl_Div_DRP
GROUP BY tbl_Div_DRP.ASXCode, Year(DivDate)
ORDER BY tbl_Div_DRP.ASXCode;