Year Total from a date and total

[Brian62]

I have a graph that is pulling data from a query. What I am trying to do is add a total for the year. I have two columns, one is, Dateofaudit and Total which shows the total number of findings for a particular finding that I enter myself. I tried using the sum and Total functions but can't get it to work.

Here is what I am trying to do:
Total for dateofaudit that equals the year 2011 etc.

dateofaudit Total
01/10/11 2
02/12/11 7

Total for 2011 would be 9.

I'm not sure if I should do this in the query or report.

 

[Brianwarnock]

I'm a little puzzled by what's what here , but given that data then sum of Total where Year(dateofAudit)= 2011 should give you what you want.

Brian

 

[Brian62]

The name of the query colums are Total and dateofaudit that I am using for this problem. I enter the number of findings I find in the total column and enter the date of the audit. I want to show how many findings I have for the entire year, either being 2010, 2011 etc.

I hope this makes it more clearer for you. It's hard to describe what you want where someone would understand it.

 

[Brian62]

This is the code in a text box I am trying in the report but keeps giving me an error:
=Count(IIf([Total] And [dateofaudit]="2011",0))
It is in the report footer while the Total and dateofaudit is in the detal section.

 

[Brianwarnock]

That should be

=Sum(IIF(Year([dateofaudit])=2011,[Total],0))

personally I would not use Total as a field name, I would qualify it to avoid conflicts with Access's use of it.

Brian

 

[Brian62]

Thanks! That worked perfectly!!!

 

[vbaInet]

Am I allowed to change this slightly Brian (i.e. to both of you )

=Sum(IIF(Year([dateofaudit])=2011,[Total], Null))

 

[Brian62]

Thanks for your input! Both worked as advertised.

 

[Brianwarnock]

Am I allowed to change this slightly Brian (i.e. to both of you )

=Sum(IIF(Year([dateofaudit])=2011,[Total], Null))

Why would you want to sum a Null rather than a 0 ?

Brian

 

[vbaInet]

Why would you want to sum a Null rather than a 0 ?

Brian

As you know aggregate functions will ignore Nulls, so using 0 will cause a recalculation instead of it being ignored. I guess, one less step for the Engine. I mentioned this once to someone on here and Allan Bunch ran a test on a huge dataset to confirm that Null computed faster than 0.

 

[Brianwarnock]

If performance became an issue , fine, but it is not as logical and leaves the possibility of just summing Nulls, ok as remote as the performance issue probably. I accept your right to raise it and leave it to the poster to choose.

Brian