Hope i can make this clear enough,

I have a database which is used to monitor the development of people doing qualifications. Each learner is assigned to an assessor. The learner is also assigned a colour code (red, amber, green) based on their level of dependancy for extra support.

I would like to create a query which gives me a summary of how many red, green and amber learners are assigned to a specific assessor.

I've looked at the count function but im not sure how to implement it so that i can count 3 different instances, in one field, in one query at the same time.

E.g. Joe Smith - Red = 7, Amber = 13, Green = 6.


Thanks for your help

This is probably a situation where you use the sum if approach

ambercount:Sum(IIf(learnercolour="amber",1,0))
ditto for the other colours

Brian

This is probably a situation where you use the sum if approach

ambercount:Sum(IIf(learnercolour="amber",1,0))
ditto for the other colours

Brian


Many thanks for your reply Brian,

I have copied your code and altered it as necessary, but it says

"The expression you entered has an invalid . (dot) or ! operator or invalid parentheses. You may have entered an invalid identifier or typed parentheses following a null constant"

Not quite sure what needs changing .... any ideas?

Thanks again.

Were are you trying to use this expression?

Were are you trying to use this expression?


In the criteria box, which im guessing is probably wrong, but then again its a long time since i've used access in depth, rather than just for data storage

Im using 3 seperate instances of the same field, one for red, one for amber, one for green

In the criteria box, which im guessing is probably wrong, but then again its a long time since i've used access in depth, rather than just for data storage

Im using 3 seperate instances of the same field, one for red, one for amber, one for green


I've now got the expression down to the following:

Sum(IIf("ColourCode"="Red",1,0)) And Sum(IIf("ColourCode"="Amber",1,0)) And Sum(IIf("ColourCode"="Green",1,0))

However it says the expression is too complex to be evaluated.

When i had 3 instances of the colour code field in my query, i had an individual expression for one colour assigned to each, yet it placed the above statement into all 3 instances of the field. I have now got just a single instance of colour code in my field, but as i said, i now get the above error message

any ideas?

ambercount:Sum(IIf(learnercolour="amber",1,0))

These statements are for new fields in the query, they do not go in the criteria, they cause the query to be a TOTALS query so you need to Groupby the assessor, you can leave the Sum off the above and then when you select a Totals query select Sum at that point.
learnercolour was my name for the field with the colourcode.

Brian

ambercount:Sum(IIf(learnercolour="amber",1,0))

These statements are for new fields in the query, they do not go in the criteria, they cause the query to be a TOTALS query so you need to Groupby the assessor, you can leave the Sum off the above and then when you select a Totals query select Sum at that point.
learnercolour was my name for the field with the colourcode.

Brian

Many Thanks Brian, i feel like im getting somewhere now.

I have entered your formulae and selected groupby SUM, and i have also done this for green and amber, although now when i run the query it asks me to enter a colour code to search by. So for example, if i search amber, it puts the number of amber learners in the amber, green and red count fields.

I was hoping it would count all 3 colour fields in 1 query?

I'd post a copy online but its got confidential st helens chamber and learner data on it.

Thanks again

See attached

Brian

See attached

Brian


Thanks for the file Brian

for some reason my query still asks the user to input a colour, i even copied and pasted yours across and substituted your assessor field/table1 for my own and it gives the same response.

Not sure why its doing that?

Thanks for the file Brian

for some reason my query still asks the user to input a colour, i even copied and pasted yours across and substituted your assessor field/table1 for my own and it gives the same response.

Not sure why its doing that?


Never mind.
I have fixed the problem

Thanks very much for your help.

Thanks for the file Brian

for some reason my query still asks the user to input a colour, i even copied and pasted yours across and substituted your assessor field/table1 for my own and it gives the same response.

Not sure why its doing that?


Never mind.
I have fixed the problem

Thanks very much for your help.

I'm pleased that you are sorted, I was beginning to think that it was a2007 problem as I believe, from another thread , that that is what you are on.

Brian