count value x where value y = z 2

[stormcloudgathering]

hello
Firstly - I need to be able to do this in the expression builder as my VBA skills are non existent (doing course in January!)
I have a report with several columns of data
I need to be able to total the values in the column [number of codes requested] only if the the value in the column [Late?] = "Late"
I could manage this in SQL by writing using a where clause, but I'm struggling in Access!
All suggestions very gratefully received
many thanks
lynne

 

[RobPotts]

off the top of my head try,

=sum(iif([number of codes requested] is not null and [Late?]="Late", 1,0))

Hope this works
Rob! [Bigcheeks]

 

[CosmoKramer]

Lynne,

Try the DSum function. Something like:

=DSum("[number of codes requested]", "YourTableName", "[Late?]  = 'Late'")

Let me know if this works......

 

[stormcloudgathering]

Rob, hi
thanks, this kind of works!
I don't think I asked for the right thing though
In my field "number of codes requested" there can be a value of anything from 1 to 20
Is there a way to total up what those values are when [late?] = "Late"

For instance, in May a total of 2 requests were late, but in one of those requests 3 codes were requested, and just 1 in the other, so the total number of codes requested that were late was 4.
Currently, using your syntax the result I get is 2: the total number of requests made

Thanks so much for your help
lynne

 

[RobPotts]

Yes I think you should be able to do it like

=sum(iif([number of codes requested] is not null and [Late?]="Late",[number of codes requested],0))

Rob! [Bigcheeks]

 

[stormcloudgathering]

brilliant!
what a star you are!
many thanks

OK, now for the really hard one, can you explain to me in words of one syllable, how I get an average calculation ignoring fields with a null value in them, please?
This seems to fox an awful lot of people on here, surely it can't be as difficult as it appears? can it?!
I have a column [How many days] and I would like to average this only where there is a value in the field, please can you help?
thanks again
lynne

 

[CosmoKramer]

Lynne,

Check out Access help for the DAvg function. It says there that "Records containing Null values aren't included in the calculation of the average."

=DAvg("[How many days]","TableName")

 

[stormcloudgathering]

thank you both for your help, I think I've got it all sorted now!