IIf Statement to for lowest number by month

[bluke829]

Hi,

I have a table with Name, date, test score. I've been asked to determine the lowest score for each person, each each. I'm stuck trying to create the IIF statement in query based on this table. Below are some record examples

Employee1, 5/1/17, 90
Employee1, 5/10/17, 85
Employee1, 5/14/17, 82
Employee1, 5/24/17 73
Employee1, 6/1/17, 91
Employee1, 6/12/17, 83
Employee1, 6/18/17, 79
Employee1, 6/29/17 74

I basically need to create a expression that shows if that record is the lowest score for the person for that month like this:

Employee1, 5/1/17, 90, no
Employee1, 5/10/17, 85, no
Employee1, 5/14/17, 82, no
Employee1, 5/24/17 73, yes
Employee1, 6/1/17, 91, no
Employee1, 6/12/17, 83, no
Employee1, 6/18/17, 79, no
Employee1, 6/29/17 74, yes

Can someone please help? I'm at a complete loss

Thanks

 

[isladogs]

Don't use IIF

Instead do an aggregate query where you group by Name and Month([Date]) and then get Min value for Test Score field.

Strongly recommend you rename the Date field as its a reserved word in Access and will cause you problems
Suggest you use TestDate instead

 

[bluke829]

Thanks, but I've tried that, however, my next step is to average, by person, by month all of the scores except the lowest each month, so a query with just the lowest won't help get to the final result. Any other suggestions?

 

[plog]

Technically, here's another suggestion: I suggest you follow ridders suggestion.

You need a subquery to determine the lowest score for a month. Once you have that you can achieve the data you said you wanted in your first post. From there you can build yet another query to determine the average excluding the lowest score.

Work on the method ridders gave.

 

[isladogs]

Thanks, but I've tried that, however, my next step is to average, by person, by month all of the scores except the lowest each month, so a query with just the lowest won't help get to the final result. Any other suggestions?

Perhaps you should have mentioned that in your first post.
Even so, as Plog has already said, you can't exclude the lowest value unless you have already identified it.

Getting the lowest value will be the easy bit
Doing the subqueries that Plog listed will be that bit harder.

So one step at a time... and build it up.

If you can guarantee that each employee does the same number of tests each month e.g 4 then you can run a query that starts
SELECT TOP 3.... where you group by month and sort by TestScore Descending
This will give you all but the lowest value

However I expect that's not likely and probably harder for you to write.

Which means you need to do the query I suggested originally

If you can reply with the table structure i.e. table name, field names & datatypes I'm happy to write that query for you. As you have less than 10 posts, you will need to zip your file

Better still upload the table itself with realistic data. Change names by all means.

Otherwise I suggest you check out Allen Browne's website as he covers all types of query including TOP 3, averages, subqueries etc

http://allenbrowne.com/tips.html