Count of records in MV Field

[brastedhouse]

On November 13, 2017 Jtorres13 posted a question about counting the entries in a MV Field. I too am saddled with this task. I have studied the answer for quite a while and I am not having much luck figuring out how to do this.

The answer he received was:
SELECT
SomeID, First([Users]) as SelectedUsers, Count(Users.Value) AS NumberUsers,
FROM Users
GROUP BY SomeID

So, my table is tblCall and my Primary Key is callID. The MV field is callMember. This from a lookup field using another table.

So I worked out:
SELECT
tblCall.callID, First([callMember]) as SelectedtblCall.callMember, Count(tblCall.callMember.Value) AS NumbercallMember,
FROM tblCall
GROUP BY tblCall.callID;

First question is does this become the control source for a control? I would like to put the count in a text box on the same form as the combobox containing the member's names.

Second question, is there a trick to this? I used the code as the control source of the text box and it gives me a subquery error.

I am at a loss as to what is wrong.

Thanks, Scott

 

[Darrylles]

brastedhouse

What's an 'MV' field?


ATB,

Darrylle

 

[Andrzejek]

Multi-Value field


---- Andy

There is a great need for a sarcasm font.