Counting number of data instances in a query

[Rik_StHelens]

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

 

[Brianwarnock]

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

 

[Rik_StHelens]

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.

 

[John Big Booty]

Were are you trying to use this expression?

 

[Rik_StHelens]

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

 

[Rik_StHelens]

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?

 

[Brianwarnock]

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

 

[Rik_StHelens]

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

 

[Brianwarnock]

See attached

Brian

 

[Rik_StHelens]

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?

 

[Rik_StHelens]

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.

 

[Rik_StHelens]

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.

 

[Brianwarnock]

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