Count records less than each record for each its corresponding year only

[riverwatch]

Hello,

I have a database used to manage volunteer water quality data. For example, I have a table with 20 years of water quality data. An example water quality record has [FieldDate] and [WaterQualityValue]. I would like to run a query that calculates the the number of records that are less than each record for its corresponding year. For example: I have 3,500 records from 1995-2015, and can easily count the number of records less than each record by using

DCount("*","WaterQualityTable","WaterQualityValue<" & [WaterQualityValue])

However, this returns all the records across all years that are less than that specific record's water quality value. So how can I make the query count only records that are less than each [WaterQualityValue] for that [WaterQualityValue]'s [FieldDate] year?

For example, a record with a [FieldDate] of 05/14/2010 and [WaterQualityValue] of 15 would need to count records from 2010 only with a [WaterQualityValue] of less than 15.

Thanks!

Matt

 

[pbaldy]

Try

DCount("*","WaterQualityTable","WaterQualityValue< " & [WaterQualityValue] & " AND Year(FieldDate) = " & Year(FieldDate))

 

[riverwatch]

That works perfectly! I think I get confused with the "&"s and the "And" part of the DCount statement. Thanks so much! Now our program volunteers will be able to easily receive percentile rank's for their stream.

Matt

 

[pbaldy]

Happy to help! Basically you're concatenating fixed text and variable values. One day it will all click into place and seem easy.